title: Append data to Excel column using VB6 ADO
body: <p>I am testing a sample VB6 application which inserts text from TextBox to Excel.
  I would like to find the last used row in the column, and append text from <code>txt1</code> TextBox at the next row whenever user clicks a button.
  The range is from <code>C10</code> to <code>C49</code>.
  After the last row is filled, I will prompt user to open new Excel file.</p>
  
  <p>I am unable to do the appending part. Below is the code I tried:   </p>
  
  <pre><code>Private Sub cmdUpdate_Click()
    Dim objConn As New ADODB.Connection
    Dim szConnect As String
  
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
          "Data Source=C:\Excel\Format.xls;" &amp; _
          "Extended Properties='Excel 8.0;HDR=NO';"
  
    objConn.Open szConnect
  
    Dim xrow As Integer
    Dim lastRow As Integer
    lastRow = 10
    xrow = 49
    Do while lastRow &lt;= xrow
      objConn.Execute "UPDATE [Sheet1$C" &amp; lastRow &amp; ":C" &amp; lastRow &amp; "] SET F1 =" &amp;      txt1.Text &amp; ";"
      lastRow = lastRow + 1
    Loop 
  End Sub
  </code></pre>
  
  <p>The code fills the whole range on each update. I know where my mistake is, but can't figure out proper way. How to make it insert only once until row <code>49</code>?</p>
  
  <p>Using Excel Object model is not an option as I want to be able to make updates when workbook is open in Excel.</p>