How to calculate Number of rows in Excel ?

[anu]

Hi ,
I have an excel sheet. i want to calculate the number of rows in the
excel sheet using Vb6.
This is the code that i have:

Dim S As String
Dim rlmt As Integer
Open CommonDialog1.FileName For Input As #1
rlmt = 0
' To get the number of rows
While Not EOF(1)
Line Input #1, S
rlmt = rlmt + 1
Wend
Close #1
MsgBox (rlmt)

this code seems to give me more no of rows than what is actually
there. Example, i had 7327 rows but my output using this code was
7330. Any help????

Thanks,
anu

 

[dq]

Anu,

The function you are using is only suited for textfiles. If you want
the number of rows in an excel sheet, use
Sheet1.UsedRange.Rows.Count() but remember that for excel every cell
with something in it or with (conditional) formatting is considered
used, so you might be counting some 'empty' rows.

DQ

 

[NickHK]

Opening a binary Excel using these text file based tools will at best return
rubbish. The fact that the numbers are nearly equal is complete luck.
You can use automation from VB6, as DQ indicates:
http://support.microsoft.com/kb/219151

Or possibly ADO depending on the structure of the worksheet(s).
http://support.microsoft.com/kb/257819

NickHK