From: RB Smissaert on
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" <GeoffK(a)discussions.microsoft.com> wrote in message
news:49E85068-AB85-4D20-BADD-37FD75744AC0(a)microsoft.com...
> 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:
>

From: RB Smissaert on
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" <GeoffK(a)discussions.microsoft.com> wrote in message
news:49E85068-AB85-4D20-BADD-37FD75744AC0(a)microsoft.com...
> 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:
>

From: Geoff K on
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

From: RB Smissaert on
Did you try the fixed code that works with ADO?

RBS


"Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message
news:864B726E-CCFE-4B1F-8CBF-D1C453F040E8(a)microsoft.com...
> 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
>

From: Geoff K on
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: