Hi all, I have witten a small macro that will connect to an access database, use SQL to connect to a table and download the contents of said table into a spreadsheet. As the tables might be greater than 65536 rows (yes still using xl2003 here!!!!) i have used the getrows function to split the recordset and paste the data into separate sheets as necessary. The macro works fine, unless the table in question has more than 1,000,000 records. Then i get a runtime error 1004. I do not know the exact figure, but it seems to be 1 million. So is there a limit to using the adodb recordset and getrows function? Is it a memory issue?
Here is the code...
TransposeDim is just a function used to transpose the array, as application.transpose has limits to transposing lots of data...
Many Thanks
Bolo.
Here is the code...
Code:
Set tmpApp = New Excel.Application
tmpApp.DisplayAlerts = False
nSheets = WorksheetFunction.RoundUp(rs.RecordCount / nRec, 0)
tmpApp.SheetsInNewWorkbook = nSheets
Set tmpWb = tmpApp.Workbooks.Add
ReDim tmpTit(rs.FIELDS.Count) As String
For i = 1 To rs.FIELDS.Count
tmpTit(i - 1) = rs.FIELDS(i - 1).Name
Next i
For i = 1 To nSheets
Application.StatusBar = "Downloading page " & i & " of " & nSheets & "..."
tmpWb.Sheets(i).[a1].Resize(1, rs.FIELDS.Count) = tmpTit
tmpArray = rs.GetRows(nRec)
tmpWb.Sheets(i).[a65536].End(xlUp).Offset(1, 0).Resize(UBound(tmpArray, 2) + 1, rs.FIELDS.Count) = TransposeDim(tmpArray)
Next i
TransposeDim is just a function used to transpose the array, as application.transpose has limits to transposing lots of data...
Many Thanks
Bolo.