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

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

Geoff,

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

=COUNTA(A:A)

This should give you the total number of rows

You can then use following procedure to copy all the data from required
sheet / range in closed workbook using formula. If you use a hidden sheet to
store this data your lookup formula can then reference the active workbook.

May need some work but hope gives you some ideas.


Sub GetData()
Dim mydata As String
Dim rcount As String
Dim lr As Long

'helper cell
rcount = "='C:\[MyTestBook.xls]Sheet1'!$C$1"

'link to worksheet
With ThisWorkbook.Worksheets(1)

With .Range("C1")

.Formula = rcount

'convert formula to text

.Value = .Value

lr = .Value

End With

'data location & range to copy
mydata = "='C:\[MyTestBook.xls]Sheet1'!$A$1:$A$" & lr

With .Range("A1:A" & lr)
.Formula = mydata

'convert formula to text

.Value = .Value

End With

End With

End Sub

--
jb


"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

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

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)

sorry if first suggestion along wrong lines.

not tested but does doing this solve text / numeric problem?

=COUNTA('C:\Path\[File.xls]Sheet1'!A:A)
--
jb


"Geoff K" wrote:

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)

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)

Hi John

That was interesting but still not there. Yes it overcomes the data type
issue but does not count the nulls and I do need the last used row which
includes nulls rather than a count.

It was also interesting because I continued testing MATCH to see how data
type affected results in a number of other wbooks. I found that using MATCH
(99^99 etc worked correctly on numeric fields and returned N/A on text and
vice versa when using MATCH("ZZZ" etc. - not unexpectedly I might add now.
In the case where a number (not N/A) was returned it proved to be the last
used row in that column which is what I'm after. If I can get MATCH to read
both types all I have to do is loop through all fields of the wbook to get
the maximun row number.

What is confusing the whole investigation is the wbook with the huge bloated
UsedRange coincidently has a text first field. I thought it was the misuse
of the data to MATCH, 99^99 or "ZZZ", that was creating the infinite loop.
However COUNTA also causes the same problem in this same wbook.

Thinking it might be the UsedRange I then tried MATCh on another misaligned
UsedRange which also had a first field as text. It worked correctly on that.
COUNTA didn't bother it either.

The puzzle therefore is why does this one wbook (up to now) have this
affect. If I do open it, it processes normally. I must resolve this.

And my original question still stands also - how can I create a MATCH
function which reads both text and numeric fields.

Geoff

john wrote:

sorry if first suggestion along wrong lines.

not tested but does doing this solve text / numeric problem?

=COUNTA('C:\Path\[File.xls]Sheet1'!A:A)
--
jb

This will cut some cycles out, but not fully tested.
This is probably done neatest with a recursive procedure, but I think this
will do.
Note that lCycles will tell you the efficiency of the code.


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, _
Optional lCycles As Long) As Long

Dim lOldMinRow As Long
Dim lOldMaxRow As Long
Dim strArg As String
Dim bPreviousFound As Boolean

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 & ")"

'for testing
'-----------
'Cells(lCycles + 1, 1) = lMinRow
'Cells(lCycles + 1, 2) = lMaxRow
'Cells(lCycles + 1, 3) = lOldMinRow
'Cells(lCycles + 1, 4) = lOldMaxRow
'Cells(lCycles + 1, 6) = lCycles

If ExecuteExcel4Macro(strArg) 0 Then
'Cells(lCycles + 1, 5) = "found" 'for testing
If bPreviousFound Or lCycles Mod 2 = 0 Then
lOldMinRow = lMinRow
lMinRow = (lMaxRow + lMinRow) \ 2
If lMinRow = lOldMinRow Then
GetXLRows = lMinRow
Exit Function
End If
End If
bPreviousFound = True
Else 'If ExecuteExcel4Macro(strArg) 0
'Cells(lCycles + 1, 5) = "nil found" 'for testing
If lCycles = 0 Then
'nil found in whole range, so return zero
'----------------------------------------
Exit Function
Else
If bPreviousFound = False Then
lOldMinRow = lMinRow
lMinRow = lMaxRow
lMaxRow = lOldMaxRow
Else
lOldMaxRow = lMaxRow
lMaxRow = lMinRow
lMinRow = lOldMinRow
End If
End If
bPreviousFound = False
End If 'If ExecuteExcel4Macro(strArg) 0
lCycles = lCycles + 1
Loop

GetXLRows = lMinRow

Exit Function
ERROROUT:

GetXLRows = -2

End Function



RBS


"RB Smissaert" wrote in message
...
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)

Hi

Thank you. The method is interesting but very slow when operating on closed
wbooks.

First I tested it on the bloated UsedRange wbook (AF50918 v S98) - closed.
Out of curiosity I waited more than 10 minutes and gave up.
I then ran it with the wbook open - it took 0.04 seconds to return the
correct last row of 98.

Next, I ran it on another misaligned UsedRange wbook, Q1532 against real
last cell of P153.
Closed, this took 86 seconds. Opened, it took 0.01 seconds

In execution the longest step was in the line If ExecuteExcel4Macro(strArg)
0 Then within the Do While Loop.
Stepping through with the bloated wbook closed, the code never moved past
the line.

So the original question remains, how can I get MATCH to return a row number
from both numeric and text fields?
And now this supplementary one - why does MATCH, COUNTA and this method fail
on the bloated wbook but then processes correctly if I open it.

Ah, I see you have sent another post. Many thanks but it is 02:01 here and
I will test in the morning.

Geoff

"RB Smissaert" wrote:

Try this code:


Sub test()

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

End Sub

This is some further optimized code plus added a timer and logging for
testing.
It works quite fast with me, but this is Excel 2003 and you might be on
2007.
Also bear in mind that you can make it a lot faster if you limit the last
column and you
may know that or you may find that with a procedure with the same principle
or you
could even combine a search for the last row with a search for the last
column.
A really fast way to do this possibly is to work directly on the BIFF Excel
file data and another option
is to capture all the data with ADO into an array and then do a binary
search (similar as in my code)
on that array.


Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub test()

Dim LR As Long
Dim lCycles As Long
Dim bLog As Boolean

'bLog = True

If bLog Then
Cells.Clear
End If

StartSW
LR = GetLastDataRow("C:\ExcelFiles\", "Lottery.xls", "Sheet1", _
, 23, , , lCycles, bLog)
StopSW , "last data row: " & LR & ", " & "found with " & lCycles & "
cycles"

End Sub

Function GetLastDataRow(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, _
Optional lCycles As Long, _
Optional bLogToSheet As Boolean) As Long

Dim lOldMinRow As Long
Dim lOldMaxRow As Long
Dim strArgStart As String
Dim strArg As String
Dim bPreviousFound As Boolean

On Error GoTo ERROROUT

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

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

'first check if very last row has data to do an early exit
'---------------------------------------------------------
strArgStart = "COUNTA('" & strPath & "[" & strFile & "]" & strSheet & "'!"
strArg = strArgStart & _
"R" & lMaxRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

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

lMaxRow = lMaxRow - 1 'as this was checked above
lOldMinRow = lMinRow
lOldMaxRow = lMaxRow

Do While lMaxRow lMinRow

strArg = strArgStart & _
"R" & lMinRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If bLogToSheet Then
'for testing
'-----------
Cells(lCycles + 1, 1) = lMinRow
Cells(lCycles + 1, 2) = lMaxRow
Cells(lCycles + 1, 3) = lOldMinRow
Cells(lCycles + 1, 4) = lOldMaxRow
Cells(lCycles + 1, 6) = lCycles
End If

If ExecuteExcel4Macro(strArg) 0 Then
If bLogToSheet Then
Cells(lCycles + 1, 5) = "found" 'for testing
End If
lOldMinRow = lMinRow
lMinRow = (lMaxRow + lMinRow) \ 2
If lMinRow = lOldMinRow Then
GetLastDataRow = lMinRow
Exit Function
End If
bPreviousFound = True
Else 'If ExecuteExcel4Macro(strArg) 0
If bLogToSheet Then
Cells(lCycles + 1, 5) = "nil found" 'for testing
End If
If lCycles = 0 Then
'nil found in whole range, so return zero
'----------------------------------------
Exit Function
Else
If bPreviousFound = False Then
lOldMinRow = lMinRow
lMinRow = lMaxRow
lMaxRow = lOldMaxRow
Else
lOldMaxRow = lMaxRow
lMaxRow = lMinRow
lMinRow = lOldMinRow
End If
End If
bPreviousFound = False
End If 'If ExecuteExcel4Macro(strArg) 0
lCycles = lCycles + 1
Loop

GetLastDataRow = lMinRow

Exit Function
ERROROUT:

GetLastDataRow = -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

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function


RBS



"Geoff K" wrote in message
...
Hi

Thank you. The method is interesting but very slow when operating on
closed
wbooks.

First I tested it on the bloated UsedRange wbook (AF50918 v S98) - closed.
Out of curiosity I waited more than 10 minutes and gave up.
I then ran it with the wbook open - it took 0.04 seconds to return the
correct last row of 98.

Next, I ran it on another misaligned UsedRange wbook, Q1532 against real
last cell of P153.
Closed, this took 86 seconds. Opened, it took 0.01 seconds

In execution the longest step was in the line If
ExecuteExcel4Macro(strArg)
0 Then within the Do While Loop.
Stepping through with the bloated wbook closed, the code never moved past
the line.

So the original question remains, how can I get MATCH to return a row
number
from both numeric and text fields?
And now this supplementary one - why does MATCH, COUNTA and this method
fail
on the bloated wbook but then processes correctly if I open it.

Ah, I see you have sent another post. Many thanks but it is 02:01 here
and
I will test in the morning.

Geoff

"RB Smissaert" wrote:

Try this code:


Sub test()

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

End Sub

Also bear in mind that often you know that the only column to consider is
column 1, so in that case you can do, as in my example:

LR = GetLastDataRow("C:\ExcelFiles\", "Lottery.xls", "Sheet1", _
, 1, , , lCycles, bLog)

Making it a lot faster.


RBS


"RB Smissaert" wrote in message
...
This is some further optimized code plus added a timer and logging for
testing.
It works quite fast with me, but this is Excel 2003 and you might be on
2007.
Also bear in mind that you can make it a lot faster if you limit the last
column and you
may know that or you may find that with a procedure with the same
principle or you
could even combine a search for the last row with a search for the last
column.
A really fast way to do this possibly is to work directly on the BIFF
Excel file data and another option
is to capture all the data with ADO into an array and then do a binary
search (similar as in my code)
on that array.


Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub test()

Dim LR As Long
Dim lCycles As Long
Dim bLog As Boolean

'bLog = True

If bLog Then
Cells.Clear
End If

StartSW
LR = GetLastDataRow("C:\ExcelFiles\", "Lottery.xls", "Sheet1", _
, 23, , , lCycles, bLog)
StopSW , "last data row: " & LR & ", " & "found with " & lCycles & "
cycles"

End Sub

Function GetLastDataRow(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, _
Optional lCycles As Long, _
Optional bLogToSheet As Boolean) As Long

Dim lOldMinRow As Long
Dim lOldMaxRow As Long
Dim strArgStart As String
Dim strArg As String
Dim bPreviousFound As Boolean

On Error GoTo ERROROUT

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

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

'first check if very last row has data to do an early exit
'---------------------------------------------------------
strArgStart = "COUNTA('" & strPath & "[" & strFile & "]" & strSheet &
"'!"
strArg = strArgStart & _
"R" & lMaxRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

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

lMaxRow = lMaxRow - 1 'as this was checked above
lOldMinRow = lMinRow
lOldMaxRow = lMaxRow

Do While lMaxRow lMinRow

strArg = strArgStart & _
"R" & lMinRow & "C" & lMinColumn & _
":R" & lMaxRow & "C" & lMaxColumn & ")"

If bLogToSheet Then
'for testing
'-----------
Cells(lCycles + 1, 1) = lMinRow
Cells(lCycles + 1, 2) = lMaxRow
Cells(lCycles + 1, 3) = lOldMinRow
Cells(lCycles + 1, 4) = lOldMaxRow
Cells(lCycles + 1, 6) = lCycles
End If

If ExecuteExcel4Macro(strArg) 0 Then
If bLogToSheet Then
Cells(lCycles + 1, 5) = "found" 'for testing
End If
lOldMinRow = lMinRow
lMinRow = (lMaxRow + lMinRow) \ 2
If lMinRow = lOldMinRow Then
GetLastDataRow = lMinRow
Exit Function
End If
bPreviousFound = True
Else 'If ExecuteExcel4Macro(strArg) 0
If bLogToSheet Then
Cells(lCycles + 1, 5) = "nil found" 'for testing
End If
If lCycles = 0 Then
'nil found in whole range, so return zero
'----------------------------------------
Exit Function
Else
If bPreviousFound = False Then
lOldMinRow = lMinRow
lMinRow = lMaxRow
lMaxRow = lOldMaxRow
Else
lOldMaxRow = lMaxRow
lMaxRow = lMinRow
lMinRow = lOldMinRow
End If
End If
bPreviousFound = False
End If 'If ExecuteExcel4Macro(strArg) 0
lCycles = lCycles + 1
Loop

GetLastDataRow = lMinRow

Exit Function
ERROROUT:

GetLastDataRow = -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

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function


RBS



"Geoff K" wrote in message
...
Hi

Thank you. The method is interesting but very slow when operating on
closed
wbooks.

First I tested it on the bloated UsedRange wbook (AF50918 v S98) -
closed.
Out of curiosity I waited more than 10 minutes and gave up.
I then ran it with the wbook open - it took 0.04 seconds to return the
correct last row of 98.

Next, I ran it on another misaligned UsedRange wbook, Q1532 against real
last cell of P153.
Closed, this took 86 seconds. Opened, it took 0.01 seconds

In execution the longest step was in the line If
ExecuteExcel4Macro(strArg)
0 Then within the Do While Loop.
Stepping through with the bloated wbook closed, the code never moved past
the line.

So the original question remains, how can I get MATCH to return a row
number
from both numeric and text fields?
And now this supplementary one - why does MATCH, COUNTA and this method
fail
on the bloated wbook but then processes correctly if I open it.

Ah, I see you have sent another post. Many thanks but it is 02:01 here
and
I will test in the morning.

Geoff

"RB Smissaert" wrote:

Try this code:


Sub test()

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

End Sub

Hi RBS
Thank you for the continued suggestions. I am not familiar with BIFF so
clearly further research is necessary on that and as you can see from the
prelim results the suggested method works ideally on open wbooks but is still
very slow on closed - and that is the essential element in my project.

These prelim results were obtained using the last method and leaving maxcol
at 23. I'm using 2003 SP3.

Wbk 1 - 29 cycles - real last used cell = BV97 UsedRange last cell = same
Closed = 851 ms Open 10ms

Wbk 2 - 27 cycles - real last used cell = W625 UsedRange last cell = same
Closed = 2523ms Open = 10ms

Wbk 3 - 28 cycles - real last used cell = P153 UsedRange last cell = Q1532
Closed = 7020ms Open = 10ms

Wbk 4 - 29 cycles - real last used cell = S98 UsedRange last cell = AF50918
Closed = did not finish Open = 10 ms

As you can see there is a vast difference in results between closed and open
wbooks.

Of significance is the bloated UsedRange wbook (Wbk4) - it did not finish
when closed. The code never moves beyond "If ExecuteExcel4Macro(strArg) 0
Then" in the Do While Loop.

I wonder why because all is perfectly ok with it open.

Geoff


"RB Smissaert" wrote:

This is some further optimized code plus added a timer and logging for
testing.
It works quite fast with me, but this is Excel 2003 and you might be on
2007.
Also bear in mind that you can make it a lot faster if you limit the last
column and you
may know that or you may find that with a procedure with the same principle
or you
could even combine a search for the last row with a search for the last
column.
A really fast way to do this possibly is to work directly on the BIFF Excel
file data and another option
is to capture all the data with ADO into an array and then do a binary
search (similar as in my code)
on that array.

From phone:
I take it you are on XL 2003?
I can't reproduce these very slow results.
If you log, does the logic look OK,
so does its steadily and in logic manner
make progress? Did you try the same
on an ADO recordset? Should be a lot
faster.

RBS

On 3 Oct, 11:31, Geoff K wrote:
Hi RBS
Thank you for the continued suggestions. *I am not familiar with BIFF so
clearly further research is necessary on that and as you can see from the
prelim results the suggested method works ideally on open wbooks but is still
very slow on closed - and that is the essential element in my project.

These prelim results were obtained using the last method and leaving maxcol
at 23. *I'm using 2003 SP3.

Wbk 1 - 29 cycles - real last used cell = BV97 * UsedRange last cell = same
Closed = 851 ms * * Open 10ms

Wbk 2 - 27 cycles - real last used cell = W625 * UsedRange last cell = same
Closed = 2523ms * * Open = 10ms

Wbk 3 - 28 cycles - real last used cell = P153 * UsedRange last cell = Q1532
Closed = 7020ms * * Open = 10ms

Wbk 4 - 29 cycles - real last used cell = S98 * UsedRange last cell = AF50918
Closed = did not finish * * Open = 10 ms

As you can see there is a vast difference in results between closed and open
wbooks.

Of significance is the bloated UsedRange wbook (Wbk4) - it did not finish
when closed. *The code never moves beyond "If ExecuteExcel4Macro(strArg) 0
Then" in the Do While Loop.

I wonder why because all is perfectly ok with it open.

Geoff



"RB Smissaert" wrote:
This is some further optimized code plus added a timer and logging for
testing.
It works quite fast with me, but this is Excel 2003 and you might be on
2007.
Also bear in mind that you can make it a lot faster if you limit the last
column and you
may know that or you may find that with a procedure with the same principle
or you
could even combine a search for the last row with a search for the last
column.
A really fast way to do this possibly is to work directly on the BIFF Excel
file data and another option
is to capture all the data with ADO into an array and then do a binary
search (similar as in my code)
on that array.

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:


Sub TEST3()

Dim LR As Long

StartSW
LR = GetSheetLastDataRow("C:\ExcelFiles\TestLastRow2003 .xls",
"Sheet1")
StopSW , "last data row: " & LR & ", done with ADO"

End Sub

Function GetSheetLastDataRow(strWB As String, _
strSheet As String, _
Optional lColumn As Long = -1) As Long

Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim arr
Dim LR As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWB & ";" & _
"Extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [" & strSheet & "$]"

Set rs = New ADODB.Recordset

rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
arr = rs.GetRows
GetSheetLastDataRow = GetArrayLastDataRow(arr, lColumn)

End Function

Function GetArrayLastDataRow(arr As Variant, Optional lColumn As Long
= -1) As Long

Dim r As Long
Dim c As Long
Dim LR As Long
Dim UB As Long
Dim UB2 As Long
Dim LB As Long
Dim LB2 As Long

UB = UBound(arr)
UB2 = UBound(arr, 2)
LB = LBound(arr)
LB2 = LBound(arr, 2)
GetArrayLastDataRow = LB

If lColumn = -1 Then
For c = LB2 To UB2
For r = UB To GetArrayLastDataRow Step -1
If Not IsEmpty(arr(r, c)) Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
Next c
Else
For r = UB To GetArrayLastDataRow Step -1
If Not IsEmpty(arr(r, lColumn)) Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
End If

End Function


RBS



On 3 Oct, 16:45, Bart Smissaert wrote:
From phone:
I take it you are on XL 2003?
I can't reproduce these very slow results.
If you log, does the logic look OK,
so does its steadily and in logic manner
make progress? Did you try the same
on an ADO recordset? Should be a lot
faster.

RBS

On 3 Oct, 11:31, Geoff K wrote:



Hi RBS
Thank you for the continued suggestions. *I am not familiar with BIFF so
clearly further research is necessary on that and as you can see from the
prelim results the suggested method works ideally on open wbooks but is still
very slow on closed - and that is the essential element in my project.

These prelim results were obtained using the last method and leaving maxcol
at 23. *I'm using 2003 SP3.

Wbk 1 - 29 cycles - real last used cell = BV97 * UsedRange last cell = same
Closed = 851 ms * * Open 10ms

Wbk 2 - 27 cycles - real last used cell = W625 * UsedRange last cell = same
Closed = 2523ms * * Open = 10ms

Wbk 3 - 28 cycles - real last used cell = P153 * UsedRange last cell = Q1532
Closed = 7020ms * * Open = 10ms

Wbk 4 - 29 cycles - real last used cell = S98 * UsedRange last cell = AF50918
Closed = did not finish * * Open = 10 ms

As you can see there is a vast difference in results between closed and open
wbooks.

Of significance is the bloated UsedRange wbook (Wbk4) - it did not finish
when closed. *The code never moves beyond "If ExecuteExcel4Macro(strArg) 0
Then" in the Do While Loop.

I wonder why because all is perfectly ok with it open.

Geoff

"RB Smissaert" wrote:
This is some further optimized code plus added a timer and logging for
testing.
It works quite fast with me, but this is Excel 2003 and you might be on
2007.
Also bear in mind that you can make it a lot faster if you limit the last
column and you
may know that or you may find that with a procedure with the same principle
or you
could even combine a search for the last row with a search for the last
column.
A really fast way to do this possibly is to work directly on the BIFF Excel
file data and another option
is to capture all the data with ADO into an array and then do a binary
search (similar as in my code)
on that array.

Hi Bart

Unfortunately this is not working correctly. It returns a row number much
less than the actual last row.

However the bloated UsedRange wbk does NOT go into an infinite loop. I am
greatly encouraged by this development.

I will test further tomorrow. Many thanks for your continued interest.

Geoff

"Bart Smissaert" wrote:

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

Sub test3()

Dim LR As Long

StartSW
LR = GetSheetLastDataRow("C:\ExcelFiles\TestLastRow2003 .xls", "Sheet1")
StopSW , "last data row: " & LR & ", done with ADO"

End Sub

Function GetSheetLastDataRow(strWB As String, _
strSheet As String, _
Optional lColumn As Long = -1) As Long

Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim arr
Dim LR As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWB & ";" & _
"Extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [" & strSheet & "$]"

Set rs = New ADODB.Recordset

rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
arr = rs.GetRows
GetSheetLastDataRow = GetArrayLastDataRow(arr, lColumn) + 1 'add one as
0-based array

End Function

Function GetArrayLastDataRow(arr As Variant, Optional lColumn As Long = -1)
As Long

'note that the passed array is transposed as it is produced by rs.GetRows
'------------------------------------------------------------------------
Dim r As Long
Dim c As Long
Dim LR As Long
Dim UB As Long
Dim UB2 As Long
Dim LB As Long
Dim LB2 As Long

'note the bounds are reversed due to the supplied array being transposed
'-----------------------------------------------------------------------
UB = UBound(arr, 2)
UB2 = UBound(arr)
LB = LBound(arr, 2)
LB2 = LBound(arr)
GetArrayLastDataRow = LB

'as sheet columns are 1-based, but this array is 0-based
'-------------------------------------------------------
If lColumn 0 Then
lColumn = lColumn - 1
End If

If lColumn = -1 Then
For c = LB2 To UB2
For r = UB To GetArrayLastDataRow Step -1
If IsNull(arr(c, r)) = False Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
Next c
Else
For r = UB To GetArrayLastDataRow Step -1
If IsNull(arr(lColumn, r)) = False Then
If r GetArrayLastDataRow Then
GetArrayLastDataRow = r
End If
Exit For
End If
Next r
End If

End Function


Note here that the final row result is the table row, so the field row is
zero and the first row is row 1.
This means that it is not the same as the sheet row. It works fine with me
and is reasonably quick.


RBS


"Geoff K" wrote in message
...
Hi Bart

Unfortunately this is not working correctly. It returns a row number much
less than the actual last row.

However the bloated UsedRange wbk does NOT go into an infinite loop. I am
greatly encouraged by this development.

I will test further tomorrow. Many thanks for your continued interest.

Geoff

"Bart Smissaert" wrote:

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:

As mentioned before, this is also an interesting option to consider:
http://www.rondebruin.nl/copy7.htm
One problem seems to be that it doesn't differentiate between empty cells
and
cells holding the value 0.

RBS



"Geoff K" wrote in message
...
Hi Bart

Unfortunately this is not working correctly. It returns a row number much
less than the actual last row.

However the bloated UsedRange wbk does NOT go into an infinite loop. I am
greatly encouraged by this development.

I will test further tomorrow. Many thanks for your continued interest.

Geoff

"Bart Smissaert" wrote:

With ADO it would work along the code
below and will normally be a lot faster
than with Excel4:

I've been there some time back. AFAIK all it does is download a recordset.

Seems as though I am going to be cursed with this flaw. It's a pity because
my project runs quickly on normal wbks.

Geoff

"RB Smissaert" wrote:

As mentioned before, this is also an interesting option to consider:
http://www.rondebruin.nl/copy7.htm
One problem seems to be that it doesn't differentiate between empty cells
and
cells holding the value 0.

RBS

Did you try the fixed code that works with ADO?

RBS


"Geoff K" wrote in message
...
I've been there some time back. AFAIK all it does is download a
recordset.

Seems as though I am going to be cursed with this flaw. It's a pity
because
my project runs quickly on normal wbks.

Geoff

"RB Smissaert" wrote:

As mentioned before, this is also an interesting option to consider:
http://www.rondebruin.nl/copy7.htm
One problem seems to be that it doesn't differentiate between empty cells
and
cells holding the value 0.

RBS

Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and run
the 2 recordset method on that wbk only. On the rest of the wbks I could use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it is
so blisteringly quick. I think instead it probably uses the UsedRange last
row or something like it. Unfortunately a null is a record to SQL so if the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

It works fine with me.
Could you mail me that workbook that gives you the wrong answer?

RBS


"Geoff K" wrote in message
...
Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as
50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid
the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only
about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and
run
the 2 recordset method on that wbk only. On the rest of the wbks I could
use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it
is
so blisteringly quick. I think instead it probably uses the UsedRange
last
row or something like it. Unfortunately a null is a record to SQL so if
the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

I agree, SELECT COUNT(*), RecordCount, GetRows all work fine when the
UsedRange reflects the real data range. Excel4Macros don't work properly
because the code just hangs.

All the above fail to return correct results whenever a wbk has been saved
with a UsedRange flaw. The only method which does work is the 2 recordset I
mentioned earlier but that is very slow.

Unfortunately I am not able to supply the 2 wbks with known UsedRange flaws
because of Data Protection. If they did not contain details of names, jobs,
addresses and telephone numbers you would be very welcome to have a look.
And of course I cannot delete the data as that would reset the UsedRange.

If you can think of a way to create a wbk with an incorrect UsedRange and
employ any of the above methods then you would make the same observations, I
am certain.

Unfortunately I have no control over theses wbks which are supplied from
outside sources. The standard of presentation is appalling - hidden rows,
hidden columns, autofilters, merged cells, wordwraps, end of line characters
- some even without any field names - and of course some with a flawed
UsedRange.

Geoff

"RB Smissaert" wrote:

It works fine with me.
Could you mail me that workbook that gives you the wrong answer?

RBS


"Geoff K" wrote in message
...
Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as
50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid
the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only
about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and
run
the 2 recordset method on that wbk only. On the rest of the wbks I could
use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it
is
so blisteringly quick. I think instead it probably uses the UsedRange
last
row or something like it. Unfortunately a null is a record to SQL so if
the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of
line characters
- some even without any field names

OK, I hadn't tested for all that.
Did you try the latest ADO code I posted?
Can't you produce a demo wb that has (all of) the above problems and make it
fail with ADO code?

RBS


"Geoff K" wrote in message
...
I agree, SELECT COUNT(*), RecordCount, GetRows all work fine when the
UsedRange reflects the real data range. Excel4Macros don't work properly
because the code just hangs.

All the above fail to return correct results whenever a wbk has been saved
with a UsedRange flaw. The only method which does work is the 2 recordset
I
mentioned earlier but that is very slow.

Unfortunately I am not able to supply the 2 wbks with known UsedRange
flaws
because of Data Protection. If they did not contain details of names,
jobs,
addresses and telephone numbers you would be very welcome to have a look.
And of course I cannot delete the data as that would reset the UsedRange.

If you can think of a way to create a wbk with an incorrect UsedRange and
employ any of the above methods then you would make the same observations,
I
am certain.

Unfortunately I have no control over theses wbks which are supplied from
outside sources. The standard of presentation is appalling - hidden rows,
hidden columns, autofilters, merged cells, wordwraps, end of line
characters
- some even without any field names - and of course some with a flawed
UsedRange.

Geoff

"RB Smissaert" wrote:

It works fine with me.
Could you mail me that workbook that gives you the wrong answer?

RBS


"Geoff K" wrote in message
...
Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as
50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid
the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only
about
2 wbks in 500. But because of the risk, I have to use the slow method
on
every wbook. It would be great if I could detect a flawed UsedRange
and
run
the 2 recordset method on that wbk only. On the rest of the wbks I
could
use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because
it
is
so blisteringly quick. I think instead it probably uses the UsedRange
last
row or something like it. Unfortunately a null is a record to SQL so
if
the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed
UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly
because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

I mentioned in my first post here that I was looking at using a formula to
include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value for
every field, then get the maximum which will give me the last used row and
original record count.

It coincidently looks similar to Ron de Bruin's code in his Main Program at
the bottom of the page. What is interesting is how he turns formulae into
values. I insert my formula on my hidden Add-in wsheet. But last night I
was getting stuck on how to convert the results into a value - so that
snippet will be useful.

But - even this method fails with the largest UsedRange flaw. The wbk justs
hangs. And even with normal wbks it can be very slow. I have to check all
fields for end of row because required fields are not always in the same
order and I need th get the original count prior to processing.

Geoff

"RB Smissaert" wrote:

Did you try the fixed code that works with ADO?

RBS

I agree, SELECT COUNT(*), RecordCount, GetRows all work as expected when the
UsedRange matches the real data.

But all fail with a flawed UsedRange. Excel4Macros failed to run on the
largest of the 2 wbks but not on the smallest.

Unfortunately I have no quality control over these received wbks. Sometimes
the quality is appalling, hidden rows, hidden columns, end of line
characters, merged cells, cell errors, autofilters, some even without field
names and of course some with flawed UsedRanges.

I would be willing to supply the 2 wbks with known flaws were it not for
data protection. They contain names, job titles, telephone numbers etc and
it would be wrong of me to share those details. And of course if I deleted
or overwrote the data the ensuing save would reset the UsedRange.

But if you know of a way to create a UsedRange which is out of line then I
am certain you would make the same observations.

Geoff

"RB Smissaert" wrote:

It works fine with me.
Could you mail me that workbook that gives you the wrong answer?

RBS


"Geoff K" wrote in message
...
Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as
50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid
the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only
about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and
run
the 2 recordset method on that wbk only. On the rest of the wbks I could
use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it
is
so blisteringly quick. I think instead it probably uses the UsedRange
last
row or something like it. Unfortunately a null is a record to SQL so if
the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead:

The current project, which uses open wbks, solves all those issues
satisfactorily - even dealing with flawed UsedRanges. What I am trying to do
is extend the current use of ADO and also work from closed wbks. It saves a
great deal of time.

And the beauty of using ADO (from a closed wbk) is that I can ignore having
to undo hidden rows, autofilters etc etc. In one experiment I hid all data
rows and columns, ran the process and it still produced the same final
results as if the wbk had been open BUT as I said a great deal quicker. On
average using ADO on a folder of closed wbks reduces processing time by half.
It is a prize worth pursuing as this application is only part of a wider
process.

Can you produce a flawed UsedRange wbk? I can't.
My only understanding of the phenomenon is they can be caused by "a frequent
change of data area, cutting and pasting" but who knows?

Geoff

"RB Smissaert" wrote:

hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of
line characters
- some even without any field names

OK, I hadn't tested for all that.
Did you try the latest ADO code I posted?
Can't you produce a demo wb that has (all of) the above problems and make it
fail with ADO code?

I have tried, but not managed to make the ADO method I posted last fail.
If there are no fields at all then it will give one row number less, but
that makes sense, as it
will consider the first row with data the field row. Hiding rows and
columns, merging cells, autofilter and
linebreaks in cells didn't cause any problem. So, not sure what causes the
problem in your wb.

RBS



"Geoff K" wrote in message
...
I mentioned in my first post here that I was looking at using a formula to
include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value for
every field, then get the maximum which will give me the last used row and
original record count.

It coincidently looks similar to Ron de Bruin's code in his Main Program
at
the bottom of the page. What is interesting is how he turns formulae into
values. I insert my formula on my hidden Add-in wsheet. But last night I
was getting stuck on how to convert the results into a value - so that
snippet will be useful.

But - even this method fails with the largest UsedRange flaw. The wbk
justs
hangs. And even with normal wbks it can be very slow. I have to check
all
fields for end of row because required fields are not always in the same
order and I need th get the original count prior to processing.

Geoff

"RB Smissaert" wrote:

Did you try the fixed code that works with ADO?

RBS

I have been able to create a flawed UsedRange wbk! Not sure I can remember
exactly how. < g

I have run the recent ADO on it and the method does not produce the expected
result.

How can I send the wbk to you? Or, I can try and retrace my steps to
replicate the wbk and pass those on.

Geoff

"RB Smissaert" wrote:

I have tried, but not managed to make the ADO method I posted last fail.
If there are no fields at all then it will give one row number less, but
that makes sense, as it
will consider the first row with data the field row. Hiding rows and
columns, merging cells, autofilter and
linebreaks in cells didn't cause any problem. So, not sure what causes the
problem in your wb.

RBS



"Geoff K" wrote in message
...
I mentioned in my first post here that I was looking at using a formula to
include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value for
every field, then get the maximum which will give me the last used row and
original record count.

It coincidently looks similar to Ron de Bruin's code in his Main Program
at
the bottom of the page. What is interesting is how he turns formulae into
values. I insert my formula on my hidden Add-in wsheet. But last night I
was getting stuck on how to convert the results into a value - so that
snippet will be useful.

But - even this method fails with the largest UsedRange flaw. The wbk
justs
hangs. And even with normal wbks it can be very slow. I have to check
all
fields for end of row because required fields are not always in the same
order and I need th get the original count prior to processing.

Geoff

"RB Smissaert" wrote:

Did you try the fixed code that works with ADO?

RBS

How can I send the wbk to you?
Just zip it and mail it to me.

RBS

"Geoff K" wrote in message
...
I have been able to create a flawed UsedRange wbk! Not sure I can remember
exactly how. < g

I have run the recent ADO on it and the method does not produce the
expected
result.

How can I send the wbk to you? Or, I can try and retrace my steps to
replicate the wbk and pass those on.

Geoff

"RB Smissaert" wrote:

I have tried, but not managed to make the ADO method I posted last fail.
If there are no fields at all then it will give one row number less, but
that makes sense, as it
will consider the first row with data the field row. Hiding rows and
columns, merging cells, autofilter and
linebreaks in cells didn't cause any problem. So, not sure what causes
the
problem in your wb.

RBS



"Geoff K" wrote in message
...
I mentioned in my first post here that I was looking at using a formula
to
include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value
for
every field, then get the maximum which will give me the last used row
and
original record count.

It coincidently looks similar to Ron de Bruin's code in his Main
Program
at
the bottom of the page. What is interesting is how he turns formulae
into
values. I insert my formula on my hidden Add-in wsheet. But last
night I
was getting stuck on how to convert the results into a value - so that
snippet will be useful.

But - even this method fails with the largest UsedRange flaw. The wbk
justs
hangs. And even with normal wbks it can be very slow. I have to check
all
fields for end of row because required fields are not always in the
same
order and I need th get the original count prior to processing.

Geoff

"RB Smissaert" wrote:

Did you try the fixed code that works with ADO?

RBS

On the way.

Geoff

"RB Smissaert" wrote:

How can I send the wbk to you?
Just zip it and mail it to me.

RBS

"Geoff K" wrote in message
...
I have been able to create a flawed UsedRange wbk! Not sure I can remember
exactly how. < g

I have run the recent ADO on it and the method does not produce the
expected
result.

How can I send the wbk to you? Or, I can try and retrace my steps to
replicate the wbk and pass those on.

Geoff

"RB Smissaert" wrote:

I have tried, but not managed to make the ADO method I posted last fail.
If there are no fields at all then it will give one row number less, but
that makes sense, as it
will consider the first row with data the field row. Hiding rows and
columns, merging cells, autofilter and
linebreaks in cells didn't cause any problem. So, not sure what causes
the
problem in your wb.

RBS



"Geoff K" wrote in message
...
I mentioned in my first post here that I was looking at using a formula
to
include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value
for
every field, then get the maximum which will give me the last used row
and
original record count.

It coincidently looks similar to Ron de Bruin's code in his Main
Program
at
the bottom of the page. What is interesting is how he turns formulae
into
values. I insert my formula on my hidden Add-in wsheet. But last
night I
was getting stuck on how to convert the results into a value - so that
snippet will be useful.

But - even this method fails with the largest UsedRange flaw. The wbk
justs
hangs. And even with normal wbks it can be very slow. I have to check
all
fields for end of row because required fields are not always in the
same
order and I need th get the original count prior to processing.

Geoff

"RB Smissaert" wrote:

Did you try the fixed code that works with ADO?

RBS

Do in Excel: Tools, Options, View and tick Zero values and you will see
why it gave you the right answer.

RBS


"Geoff K" wrote in message
...
On the way.

Geoff

"RB Smissaert" wrote:

How can I send the wbk to you?
Just zip it and mail it to me.

RBS

"Geoff K" wrote in message
...
I have been able to create a flawed UsedRange wbk! Not sure I can
remember
exactly how. < g

I have run the recent ADO on it and the method does not produce the
expected
result.

How can I send the wbk to you? Or, I can try and retrace my steps to
replicate the wbk and pass those on.

Geoff

"RB Smissaert" wrote:

I have tried, but not managed to make the ADO method I posted last
fail.
If there are no fields at all then it will give one row number less,
but
that makes sense, as it
will consider the first row with data the field row. Hiding rows and
columns, merging cells, autofilter and
linebreaks in cells didn't cause any problem. So, not sure what causes
the
problem in your wb.

RBS



"Geoff K" wrote in message
...
I mentioned in my first post here that I was looking at using a
formula
to
include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row
value
for
every field, then get the maximum which will give me the last used
row
and
original record count.

It coincidently looks similar to Ron de Bruin's code in his Main
Program
at
the bottom of the page. What is interesting is how he turns
formulae
into
values. I insert my formula on my hidden Add-in wsheet. But last
night I
was getting stuck on how to convert the results into a value - so
that
snippet will be useful.

But - even this method fails with the largest UsedRange flaw. The
wbk
justs
hangs. And even with normal wbks it can be very slow. I have to
check
all
fields for end of row because required fields are not always in the
same
order and I need th get the original count prior to processing.

Geoff

"RB Smissaert" wrote:

Did you try the fixed code that works with ADO?

RBS

I know. If only it were that easy. The wb I sent was not a true flawed
UsedRange.

I thought it was too easy to produce one. I was messing around after the
last post to try and create one and deliberately turned zeros off. In my
enthusiasm I forgot that.

The ADO method doesn't return the expected answer with a true flawed
UsedRange - and there are no hidden zeros either.

I will see if I can do something with the 2 genuine flawed wbks.

Geoff

"RB Smissaert" wrote:

Do in Excel: Tools, Options, View and tick Zero values and you will see
why it gave you the right answer.

RBS

Ok I have substituted real data with gobbledy gook in the smaller of the 2
flawed wbks. But it should enable you to see the issue.

I will mail it to you now.

Geoff

"Geoff K" wrote:

I know. If only it were that easy. The wb I sent was not a true flawed
UsedRange.

I thought it was too easy to produce one. I was messing around after the
last post to try and create one and deliberately turned zeros off. In my
enthusiasm I forgot that.

The ADO method doesn't return the expected answer with a true flawed
UsedRange - and there are no hidden zeros either.

I will see if I can do something with the 2 genuine flawed wbks.

Geoff

"RB Smissaert" wrote:

Do in Excel: Tools, Options, View and tick Zero values and you will see
why it gave you the right answer.

RBS

I am now mailing the largest flawed UsedRange wbk.

All real data has been replaced with similar data type.

The UsedRange last cell is AF50918 and the real last cell is S98.

This wbk will not run Excel4 - it just hangs. Execution is considerably
slowed using other methods.

Please let me know how you get on.

Geoff



"Geoff K" wrote:

Ok I have substituted real data with gobbledy gook in the smaller of the 2
flawed wbks. But it should enable you to see the issue.

I will mail it to you now.

Geoff

"Geoff K" wrote:

I know. If only it were that easy. The wb I sent was not a true flawed
UsedRange.

I thought it was too easy to produce one. I was messing around after the
last post to try and create one and deliberately turned zeros off. In my
enthusiasm I forgot that.

The ADO method doesn't return the expected answer with a true flawed
UsedRange - and there are no hidden zeros either.

I will see if I can do something with the 2 genuine flawed wbks.

Geoff

"RB Smissaert" wrote:

Do in Excel: Tools, Options, View and tick Zero values and you will see
why it gave you the right answer.

RBS

I don't know whether this one would work or not on your data. But it
seems to be able to detect a flawed UsedRange in my case, ignoring the
time of process. According data, it might be very slow. I assumed a
flawed UsedRange to be data file that returns a wrong number when using
Select count(*) in ADO.


Sub CheckFlawedtest()
Dim SsourceData As String
Dim Table1 As String

SsourceData = "c:\adodata.xls"
Table1 = "[Sheet1$]"

If CkFlawedURange(SsourceData, Table1) Then
MsgBox "Flawed UsedRange"
MsgBox "Corect LastRow Is " & _
GetLastRow(SsourceData, Table1)
Else
MsgBox "Not Flawed"
End If

End Sub

Function CkFlawedURange(ByVal Fname As String, _
ByVal TableName As String) As Boolean
'Fname is a name of a file with a full path
'TableName is a name of Worksheet
Dim oConn As ADODB.Connection
Dim i As Long

Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Fname & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset

oRS.CursorLocation = adUseClient
oRS.Open TableName, oConn, adOpenStatic
oRS.MoveLast

CkFlawedURange = True
For i = 0 To oRS.Fields.Count - 1
If Not IsNull(oRS.Fields(i).Value) Then
CkFlawedURange = False
Exit For
End If
Next

oRS.Close
oConn.Close
Set oConn = Nothing
Set oRS = Nothing

End Function

Function GetLastRow(ByVal Fname As String, _
ByVal TableName As String) As Long
'Fname is a name of a file with a full path
'TableName is a name of Worksheet
Dim Flawed As Boolean
Dim oConn As ADODB.Connection
Dim i As Long

Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Fname & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset

oRS.CursorLocation = adUseClient
oRS.Open TableName, oConn, adOpenStatic
oRS.MoveLast

Flawed = True

Do While (Flawed)

For i = 0 To oRS.Fields.Count - 1
If Not IsNull(oRS.Fields(i).Value) Then
Flawed = False
Exit Do
End If
Next
oRS.MovePrevious
Loop

GetLastRow = oRS.AbsolutePosition + 1

oRS.Close
oConn.Close
Set oConn = Nothing
Set oRS = Nothing

End Function

Keiji

Geoff K wrote:
Hi

I was just about to post the same thing when I spotted your reply.

It was easy enough to transpose and add 1 for the zero base.

However the ADO function returns me once more to the start position of
mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918
and not the real 98.

I have been here before.

MichDenis in another post some way back now supplied a link
http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the
pitfalls of flawed UsedRanges but is slow.

This is frustrating because the incidence of flawed UsedRanges is only about
2 wbks in 500. But because of the risk, I have to use the slow method on
every wbook. It would be great if I could detect a flawed UsedRange and run
the 2 recordset method on that wbk only. On the rest of the wbks I could use
SELECT COUNT(*) etc.

FWIW I don't believe SELECT COUNT(*) does any counting at all because it is
so blisteringly quick. I think instead it probably uses the UsedRange last
row or something like it. Unfortunately a null is a record to SQL so if the
wbk has been saved with a flawed UsedRange that is what it uses.

So I am right back to square 1. If only I could detect a flawed UsedRange
in a closed wbk€¦€¦€¦

Geoff


"RB Smissaert" wrote:

That code wasn't tested and indeed it is no good at all, mainly because I
didn't consider the fact
that an array produced by rs.GetArray is transposed.
Shortly after I posted better code (via a phone), but it didn't come
through.
Try this code instead: