Ron de Bruin covers how to do that.

http://www.rondebruin.nl/copy7.htm

"Geoff K" wrote:

Hi
Getting data from a closed wbook.
These wsheet formulae work fine on numerical fields but not on text.

Gets from a closed wbook the value in the last used cell of a column.
=LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

Gets from a closed wbook the last used row number of a column.
=MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

But if either meet with a text field or even if 99^99 is substituted with
"ZZZ" then Excel goes into an infinite loop.

How can the formulae be made universal to look for either numeric or text
fields? Or if that is not possible then how might it made to work in a text
field?

T.I.A

Geoff

Hi Jeff

Please read the text of my question.

My aim is to find the last used row of the source wbook before I begin to
extract data.

Geoff

"Jeff" wrote:

Ron de Bruin covers how to do that.

http://www.rondebruin.nl/copy7.htm

"Geoff K" wrote:

Hi
Getting data from a closed wbook.
These wsheet formulae work fine on numerical fields but not on text.

Gets from a closed wbook the value in the last used cell of a column.
=LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

Gets from a closed wbook the last used row number of a column.
=MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

But if either meet with a text field or even if 99^99 is substituted with
"ZZZ" then Excel goes into an infinite loop.

How can the formulae be made universal to look for either numeric or text
fields? Or if that is not possible then how might it made to work in a text
field?

T.I.A

Geoff

Returning the last used row is pretty simple, Here are a Function and Sub
procedure examples

Public Sub Geoff_K()
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Public Function GetLastRow() As Long
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

GetLastRow = lRow
End Function


"Geoff K" wrote:

Hi Jeff

Please read the text of my question.

My aim is to find the last used row of the source wbook before I begin to
extract data.

Geoff

"Jeff" wrote:

Ron de Bruin covers how to do that.

http://www.rondebruin.nl/copy7.htm

"Geoff K" wrote:

Hi
Getting data from a closed wbook.
These wsheet formulae work fine on numerical fields but not on text.

Gets from a closed wbook the value in the last used cell of a column.
=LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

Gets from a closed wbook the last used row number of a column.
=MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

But if either meet with a text field or even if 99^99 is substituted with
"ZZZ" then Excel goes into an infinite loop.

How can the formulae be made universal to look for either numeric or text
fields? Or if that is not possible then how might it made to work in a text
field?

T.I.A

Geoff

Thank you. But again, please read my question.

Yes it is simple but that is for an open wbook. I want to get the last row
from a CLOSED wbook.

Geoff

"Jeff" wrote:

Returning the last used row is pretty simple, Here are a Function and Sub
procedure examples

Public Sub Geoff_K()
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Public Function GetLastRow() As Long
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

GetLastRow = lRow
End Function

Try an ADO solution...
'Requires reference to microsoft Active X Data Objects Lib 2.7

Public Sub QueryWorksheet()

Dim Recordset As ADODB.Recordset
Dim ConnectionString As String

ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= H:\Test3.xls;" & _
"Extended Properties=Excel 8.0;"

Dim SQL As String

' Query based on the worksheet name.
SQL = "SELECT * FROM [Sheet1$]"

' Query based on a sheet level range name.
' SQL = "SELECT * FROM [Sales$MyRange]"
' Query based on a specific range address.
' SQL = "SELECT * FROM [Sales$A1:E14]"
' Query based on a book level range name.
' SQL = "SELECT * FROM BookLevelName"

Set Recordset = New ADODB.Recordset

On Error GoTo Cleanup

Call Recordset.Open(SQL, ConnectionString, _
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
CommandTypeEnum.adCmdText)

Call Sheet1.Range("A1").CopyFromRecordset(Recordset)

Cleanup:
If (Err.Number < 0) Then
Debug.Print Err.Description
End If

If (Recordset.State = ObjectStateEnum.adStateOpen) Then
Recordset.Close
End If

Set Recordset = Nothing

End Sub

"Geoff K" wrote:

Thank you. But again, please read my question.

Yes it is simple but that is for an open wbook. I want to get the last row
from a CLOSED wbook.

Geoff

"Jeff" wrote:

Returning the last used row is pretty simple, Here are a Function and Sub
procedure examples

Public Sub Geoff_K()
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Public Function GetLastRow() As Long
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

GetLastRow = lRow
End Function

I appreciate you are trying to help. But again please read my question - it
is very specific.

I am already using ADO but I need the last used row before I begin to
extract data.

Using SQL to get a count with SELECT COUNT(*) does not work if a wbook has
been saved with an out of line UsedRange. One of the wbooks I have come
across had a UsedRange last cell of AF50918 whereas the real last cell was
S98.

When I did a record count on that wbook it returned 50917 instead of 97.

Using 2 associated recordsets and looping through all the fields provided
the correct last row / record count but it was painfully slow because it had
to work its way through 50,918 rows on X number of fields.

I had already been through the usual alternative methods until I came across
the method detailed in my post. I thought this might be worth a shot.

It works if the first field is numeric and doesn't throw its toys out of the
cot if it encounters a text field subsequently it just returns N/A. But if
seems if the first field of a wbook is text then it goes into an infinite
loop.

If I can get it right I can install formulae on the hidden wsheet in my
Add-in and pull in the last used row number and then simply calculate the
number of original records before I extract data from the closed wbook.

Hope that clarifies.

Geoff

"Jeff" wrote:

Try an ADO solution...
'Requires reference to microsoft Active X Data Objects Lib 2.7