Read of excel spreadsheet with ADO recordset

[ER]

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?

 

[ytayta555]

Anyone know a work-around?

Can you post your actual code ?

 

[ER]

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