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 4 Oct 2009 10:09 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 4 Oct 2009 10:58 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 4 Oct 2009 11:43 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 4 Oct 2009 11:55 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 4 Oct 2009 12:05
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: |