Try this code:


Sub test()

MsgBox GetXLRows("C:\ExcelFiles\", "Test.xls", "Sheet1")

End Sub

Function GetXLRows(strPath As String, _
strFile As String, _
strSheet As String, _
Optional lMinColumn As Long = 1, _
Optional lMaxColumn As Long = 256, _
Optional lMinRow As Long = 1, _
Optional lMaxRow As Long = 65536) As Long

Dim i As Long
Dim lOldMinRow As Long
Dim lOldMaxRow As Long
Dim strArg As String

On Error GoTo ERROROUT

If Right$(strPath, 1) < "\" Then
strPath = strPath & "\"
End If

If bFileExists(strPath & strFile) = False Then
GetXLRows = -1
Exit Function
End If

strArg = "COUNTA('" & strPath & "[" & strFile & "]" & strSheet & "'!" & _
"R" & lMaxRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If ExecuteExcel4Macro(strArg) 0 Then
GetXLRows = lMaxRow
Exit Function
End If

Do While lMaxRow lMinRow
strArg = "COUNTA('" & strPath & "[" & strFile & "]" & strSheet & "'!" &
_
"R" & lMinRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If ExecuteExcel4Macro(strArg) 0 Then
If i Mod 2 = 0 Then
lOldMinRow = lMinRow
lMinRow = (lMaxRow + lMinRow) \ 2
If lMinRow = lOldMinRow Then
GetXLRows = lMinRow
Exit Function
End If
End If
Else
If i = 0 Then
'nil found in whole range, so return zero
'----------------------------------------
Exit Function
Else
If i Mod 2 = 0 Then
lMinRow = lMaxRow
lMaxRow = lOldMaxRow
Else
lMaxRow = lMinRow
lMinRow = lOldMinRow
End If
End If
End If
i = i + 1
Loop

GetXLRows = lMinRow

Exit Function
ERROROUT:

GetXLRows = -2

End Function

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS


"Geoff K" wrote in message
...
Hi John
I do not want to ever open source wbooks if I can possibly avoid it.

They are used once only to extract data and are not used again unless
there
are anomalies in the final analysis. Opening and closing wbooks wastes
time
if you only need their data and there are so many of them.

Somehow I have to get the real last row BEFORE I begin to extract data
because I need to establish the original record count.

I use ADO for extraction and it works fine. But when I use a SELECT COUNT
(*) to get a record count it gets messed up sometimes because a wbook may
have been saved with an out of line UsedRange.

One wbook I came across showed the last UsedRange call as AF50918 instead
of
S98. That produced an original record count of 50917 instead of 97.

I've been through a number of alternatives then came across the method
which
I posted. But it doesn't work consistently. It seems ok if the first
field
in a closed wbook is numeric - and it reurns N/A if it encounters a text
field - but if the first field is a text field then it throws a wobbler.

If I can get the thing to work correctly I can install formulae on the
hidden wsheet of my Add-in and loop through all the wbooks in the folder
and
calculate the number of original records in each.

Geoff

"john" wrote:

Geoff,

Use a helper cell in the closed workbook and add formula like this:

=COUNTA(A:A)