Getting data from a closed wbook
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)
|