3 Replies - 2032 Views - Last Post: 11 April 2013 - 07:05 PM Rate Topic: -----

#1 tbzserge  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 30
  • Joined: 26-January 11

Loop in Finding last used Row in Excel using vb6

Posted 07 April 2013 - 08:31 PM

Hi, i am testing a sample VB6 application which is enable to insert the text in textbox to an excel.
I would like to have a loop to find the last used cell in row and insert at the next row.
The range is from C10 to C49.
After last row is inserted, it will prompt a msg open a new excel file.

The problem is i am unable to do the loop part.So, any help or advice would be greatly appreciated.
Thanks and sorry for the silly question.

Private Sub cmdUpdate_Click()

Dim objConn As New ADODB.Connection
Dim szConnect As String
Dim szSQL As String
Dim WS As Worksheet

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Excel\Format.xls;" & _
            "Extended Properties='Excel 8.0;HDR=NO';"

objConn.Open szConnect
'objConn.Execute "UPDATE[Sheet1$C15:D15] SET F1=11235;"

Dim xrow As Integer
Dim lastRow As Integer
lastRow = 10
Do
objConn.Execute "UPDATE [Sheet1$C" & lastRow & ":D/>" & lastRow & "]SET F1 =" & txt1.Text & ";"
lastRow = lastRow + 1
Loop Until lastRow
End Sub




Is This A Good Question/Topic? 0
  • +

Replies To: Loop in Finding last used Row in Excel using vb6

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Loop in Finding last used Row in Excel using vb6

Posted 08 April 2013 - 07:40 PM

If you say "Loop Until LastRow" what you are actually saying is "Loop Until LastRow = True". So Visual Basic helpfully converts the actual value of LastRow to a boolean value, because it thinks that's what you want. 0 converts to false, any other number converts to true. So, armed with that knowledge, how many times will you go through your loop? :)

And, what do you have to do to fix it?
Was This Post Helpful? 0
  • +
  • -

#3 tbzserge  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 30
  • Joined: 26-January 11

Re: Loop in Finding last used Row in Excel using vb6

Posted 09 April 2013 - 03:19 AM

Hi Bob,

Thanks for the reply.
Actually, my concern is how to check on last row used and append the next data on the next row (same column).

Note that i am using ADO to connect Excel.
I know that getting last used cell in a column would be a breeze using Excel Object Model. Something like the lines of

Range("C1").End(xldown).Select

I using ADO because i would like to have my excel opened when it's doing the update.
Using Excel Object Model to update while opening the excel would cause it prompt a message saying it can't open read only file.

Any advice would be apprecciated. Thanks
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Loop in Finding last used Row in Excel using vb6

Posted 11 April 2013 - 07:05 PM

Yes, I understand very well what you are trying to do. Fix your loop.

This post has been edited by BobRodes: 11 April 2013 - 07:06 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1