Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read of excel spreadsheet with ADO recordset
Anyone know a work-around?
Can you post your actual code ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel PivotCache Recordset property is it Read/Write? | Excel Programming | |||
Read Excel Spreadsheet Via VB.Net | Excel Programming | |||
ADO recordset closed, cannot read | Excel Programming | |||
how to read excel spreadsheet into database (DB2) | Excel Programming | |||
How to populate a multi-column activeX listbox on a spreadsheet with an ADO recordset | Excel Programming |