count populated row in spreadsheet for import to table

[pb21]

Hi everyone

BAT17 was kind enough to provide a function to count the number of populated rows in a column so as only to import populated rows into a table using transferspreadsheet method.

I have added the function to a module and called it but it always returns 65536 despite only two rows one field header and second row data. It refers to an excel macro function counta but i cannot see why it doesnt work. any ideas?

Public Function GetXlRows(filepath, Filename, sheetname As String)
Dim xlApp As excel.Application
Set xlApp = New excel.Application
GetXlRows = xlApp.ExecuteExcel4Macro("counta('" & filepath & "\[" & Filename & "]" & sheetname & "'!r1c1:r65536c1)")
Set xlApp = Nothing
End Function

I pass:

Dim filepath As String
Dim Filename As String
Dim sheetname As String
Dim lngRows As Long

filepath = "d:\Datastore\"
Filename = "Eif2.XLS"
sheetname = "Student Details"
lngRows = GetXlRows(filepath, Filename, sheetname)
Debug.Print lngRows

output always 65536

column ain student details sheet A1 [first name]
row 2 peter

****
regards in advance
peter

 

[Bat17]

you don't need the final '\' as it is embeded in the function. Suprised it gave 65536 rather than 0 or an error of some sort though

filepath = "d:\Datastore"

Peter