Read of excel spreadsheet with ADO recordset
With VBA, am reading an external Excel spreadsheet using ADODB. This has
been working great, but now the external spreadsheet has more than 3000 rows and I am getting error: [Microsoft][ODBC Excel Driver] This table contains cells that are outside the range of cells defined in this spreadsheet. If I cut back the #rows, it works fine. Even though I know Excel isn't the best choice for a database here, it's what I'm dealing with. And I did not anticipate hitting a limit this early. Anyone know a work-around? |
Read of excel spreadsheet with ADO recordset
Anyone know a work-around?
Can you post your actual code ? |
Read of excel spreadsheet with ADO recordset
Sure. Here is a snipet from the calling routine:
sqltext = "Select * from [Sheet 1$A3:AC65000] " strFilter = "" SelectData db, sqltext, "Details", "B6", strFilter And here is the routine that does the actual select. The error occurs on the rs.open statement and only happens when I have more than around 3000 rows of data. Sub SelectData (Database, sqltext, SheetName, StartCell, strFilter, Optional Headers As Boolean) Dim rs As New ADODB.Recordset Dim recArray As Variant Dim recCount As Long On Error GoTo NoData ' ' open the record set ' rs.Open sqltext, Database, adOpenForwardOnly, adLockReadOnly ' ' assign the record set to an array ' If strFilter < "" Then rs.Filter = strFilter End If On Error GoTo NoData recArray = rs.GetRows On Error Resume Next recCount = UBound(recArray, 2) + 1 ' this is a 0 based-array fldCount = rs.Fields.Count ' ' assign the array to the worksheet ' Sheets(SheetName).Range(StartCell).Resize(recCount , fldCount).Value = _ TransposeDim(recArray) If Headers Then Sheets("Input Data").Range(StartCell).Offset(-1) = rs.Fields(0).Name Sheets("Input Data").Range(StartCell).Offset(-1, 1) = rs.Fields(1).Name End If rs.Close Set rs = Nothing Exit Sub NoData: 'MsgBox "Problem selecting data. " & sqltext On Error Resume Next rs.Close Set rs = Nothing End Sub |
All times are GMT +1. The time now is 12:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com