Using ADODB in VBScript to find the number of rows

Question

I'm trying to use ADODB in VBScript to access an Excel file to find the number of rows in a given sheet that have data entered into them. My code so far displays everything on the sheet, but I'm not sure how I could count the rows or directly find the number of rows using a query. I want to use ADODB as it doesn't open the Excel file directly, but if this isn't the best way then how could I do it otherwise? Thanks.

Set adodb = CreateObject("ADODB.Connection")
adodb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
              "test.xls" & ";Extended Properties=""Excel 8.0;IMEX=1;" & _
              "HDR=NO;" & """"

Set result = adodb.Execute("Select * from [Sheet1$]")

MsgBox result.GetString 

result.Close
adodb.Close
Set adodb = Nothing
Set result = Nothing

Answer 1

I got this to work ok:

Sub testit()

Dim ad As New adodb.Connection
Dim result As New adodb.Recordset

    ad.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=test.xls ;" & _
    "Extended Properties=Excel 8.0;"

    result.Open "Select count(*) FROM [Sheet1$]", _
        ad, adOpenStatic, adLockOptimistic, adCmdText

    Debug.Print "rows:" & result.GetString

    result.Close
    ad.Close

End Sub

(I changed your variable name adodb, as it seems to conflict).

Answer 2

Add a CursorLocation property for your Connection object.

Updated:

'result.CursorLocation = 3 'adUseClient
adodb.CursorLocation = 3 'adUseClient

Then you can get number of rows.

MsgBox result.RecordCount