4

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).

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:

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
conXLS.Open

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'
conXLS.Close
Set conXLS = Nothing

End Sub

I also tried the connection string:

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

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.

Community
  • 1
  • 1
Nicholas Clarke
  • 116
  • 2
  • 5

2 Answers2

7

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,

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
  • Thanks very much, would you have an example of such a lib? – Nicholas Clarke Jun 29 '14 at 00:59
  • Open Excel 2013 app (or any version starting w/2007), hit ALT+F11 and you will get VBA editor w/proper Lib. Paste your stuff and recompile the project (Menu option Debug->Compile). Rgds, – Alexander Bell Jun 29 '14 at 01:07
  • Hey Alex, just a little stuck on the setting reference part - there is no Microsoft ActiveX Data Objects 12.0 Library or similar, I think the most recent version would be ado.net (which I of course cannot use). Have Microsoft even come out with an updated ado for VBA? Thanks for your help again. – Nicholas Clarke Jun 29 '14 at 09:19
  • @Nicholas Clarke: you are welcome. It seems that your code is used just to copy column A of the Sheet1 into Sheet2. Why do you need ADO object? The operation can be completed using a single line of VBA, like: Sheets("Sheet2").Range("A:A").Value = Sheets("Sheet1").Range("A:A").Value. Rgds, – Alexander Bell Jun 29 '14 at 12:51
  • Please note that the version 6.0 is delivered as part of Windows Vista and does not correspond to VB 6. See this link for more information http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=171:microsoft-access-activex-data-objects-library-ado&catid=79&Itemid=475 The latest version seems to be 6.1 for Win7. – MP24 Jun 29 '14 at 20:44
  • 4
    Alex, while your code runs smoothly (I mean [Sheet1$A:A] approach), it again returns only 65536 rows, so the result of the whole query will be incorrect. There's interesting workaround [here](http://forum.chandoo.org/threads/excel-recordset-only-returns-65536-rows-if-you-try-to-pull-data-from-a-range.12492/). – JohnyL May 16 '16 at 19:54
  • 1
    Forget giving the columns - this doesn't work - not sure why this is an accepted answer. Just give it the sheet as a whole `[Sheet1$]` - if your data isn't on row one in a simple table, throw it on a separate sheet. @Đức Thanh Nguyễn addresses this below. – user1274820 Sep 13 '18 at 22:11
5

I encountered this problem a long time ago. What I did was writing my query like this:

select Data from [Temp$]

Where Temp was my sheet name, the content of cell A1 was "Data" and the content of A2:A80000 were ids

It worked.

Đức Thanh Nguyễn
  • 9,127
  • 3
  • 21
  • 27
  • 1
    This saved my ass so thanks very much It's worth noting that named ranges fail also (likely because they're converted to the sheet range in the background). I had a named range called `DataSet` so my query looked like this (paraphrasing) `SELECT SomeData FROM DataSet` - the error was "Cannot find object DataSet blah blah", but going to the Names section of the Data tab, it was clearly there. Changing to `SELECT SomeData FROM [DataSet$]` solved this issue (my sheets were named according to their named ranges so that was convenient). – user1274820 Sep 13 '18 at 22:09