Getting data from a closed wbook
in [Programming]
|
Prev: Counting records with ADO
Next: Microsoft.Office.Interop.Excel myexcel = new EXCEL.Application()
From: RB Smissaert on 3 Oct 2009 04:15 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" <bartsmissaert(a)blueyonder.co.uk> wrote in message news:OTKn1U$QKHA.352(a)TK2MSFTNGP02.phx.gbl... > 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" <GeoffK(a)discussions.microsoft.com> wrote in message > news:04209BE7-291C-4BB6-8928-113CE2A824F2(a)microsoft.com... >> 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 >> >
From: Geoff K on 3 Oct 2009 06:31 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: Bart Smissaert on 3 Oct 2009 11:45 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 <Geo...(a)discussions.microsoft.com> 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.
From: Bart Smissaert on 3 Oct 2009 17:41 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 <bart.smissa...(a)gmail.com> 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 <Geo...(a)discussions.microsoft.com> 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.
From: Geoff K on 3 Oct 2009 21:31
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: |