Nicholas Clarke Nicholas Clarke - 1 year ago 371
SQL Question

Excel as database - query more than 65536 rows?

I am trying to query a spreadsheet using VBA and am running up against a seeming hard limit of 65536 rows (though I am running Excel 2013).
Basically, when trying to select all rows where the number of rows is greater than 65536 I get the following error message:

runtime error '-2147217865 (80040e37)':

The Microsoft Access database engine could not find the object 'Sheet1$A1:A65537'.....

My code is below:

Option Explicit

Sub ExcelQuery()

Dim conXLS As ADODB.Connection
Dim rsXLS As ADODB.Recordset
Dim strPath As String
Dim strSQL As String
Dim i As Integer

'Get the full directory + file name location of the current workbook (so it can query itself)'
strPath = Application.ActiveWorkbook.FullName

'create the ADO connection to the excel file'
Set conXLS = New ADODB.Connection
With conXLS
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPath & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=False"""
End With

strSQL = "" & _
"SELECT " & _
"* " & _
"FROM " & _
"[Sheet1$A1:A65537] "

'create ADO recordset to hold contents of target sheet.'
Set rsXLS = New ADODB.Recordset
With rsXLS
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With

'using SQL return contents of the target sheet'
rsXLS.Open strSQL, conXLS

'disconnect the active connection'
Set rsXLS.ActiveConnection = Nothing

'Return results to excel'
Sheets("Sheet2").Cells(1, 1).CopyFromRecordset rsXLS

Set rsXLS = Nothing

'destroy the connection object'
Set conXLS = Nothing

End Sub

I have also tried the connection string:

With conXLS
.Provider = "Microsoft.Jet.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPath & ";" & _
"Extended Properties=Excel 12.0;"

I have set references to "Microsoft ActiveX Data Objects 6.0 Library" and "OLE Automation".

Interestingly, there seems to be no problem when using MSQuery.

Has anyone come across this problem?

Answer Source

Older Excel versions (prior to 2007) indeed have a limit of some 65k+ rows per worksheet. Run your code and reference any object Lib starting w/Excel 2007 and up (max 1,048,576 rows per worksheet, Lib version correspondingly 12.x and up). Pertinent to your case, try to use a notation [Sheet1$A:A] instead of [Sheet1$A1:A65537] Rgds,