Excel Recordset only returns 65536 rows if you try to pull data from a range

[jeffreyweir]

Howdy folks. I've been doing some testing on an Excel .XSLM file with data in the entire column A of Sheet 2 i.e. a header in A1 and data from A2 to A1048576 i.e. the entire column.

I found that using SELECT * FROM [Sheet2$] works just fine, and pulls through the entire 1048765 records. But any time you want to return data from a named range on that sheet rather than just a blanket "Give me the entire sheet" i.e. [Sheet2$] then you get unexpected results:
1. SELECT * FROM [Sheet2$A:A] only returns 65535 records:
2. SELECT * FROM [Sheet2$A1:A1048576] returns an error: "The Microsoft Access database engine could not find the object 'Sheet1$A1:A65537'

There's nothing wrong with my syntax or connection strings.

So this returns 65535 records: SELECT * FROM [Sheet$A1:A65536]
...whereas if I ask for just one more record beyond old Excel's grid size, it errors out: SELECT * FROM [Sheet$A1:A65537]
...even though SELECT * FROM [Sheet2$] works just fine, and pulls through the entire 1048765 records.

So I'm thinking this is a bug. MS obviously haven't programmed their driver to handle the bigger grid whenever you are using range references.

Funny thing is that setting up a MS Query connection using ODBC works just fine.

Anyone have any thoughts they can share on this?

I've also posted this at a linked in group I'm a member of: http://www.linkedin.com/groups?home=&gid=58704&trk=anet_ug_hm

Here's my code, for what it's worth:

Sub SQL_test()
 
Dim con As ADODB.Connection
Dim rstData As ADODB.Recordset
Dim sDatabaseRangeAddress As String
 
 
Set con = New ADODB.Connection
Set rstData = New ADODB.Recordset
 
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties = ""Excel 12.0 Macro;HDR=Yes"";"
 
rstData.Open "SELECT * FROM [Sheet1$A1:A65537]", con
 
Range("F1").Value = "Results"
Range("f2").CopyFromRecordset rstData
 
rstData.Close
Set rstData = Nothing
Set con = Nothing
 
End Sub

 

[lohithsriram]

Hi Jeff,

It looks like Excel 2007,2010 is not purely built from scratch but still holds lot from their ancestors. As you mentioned the issue happens when we refer the named range.
I tried with this line of code without the reference to the sheet and it works.

rstData.Open "SELECT * FROM [A1:A65537]", con

Also, Excel behaves strange in understanding the sheets when you interchange the sheet order (Sheet2,Sheet1, Sheet3) . Have data in Sheet2, activate it and run with the above line. The data will be read from Sheet2 but copied to F column on Sheet1.

 

[Hui]

Lolith is correct
There are a number of functions/techniques that cannot address the full 1048576 rows.

 

[jeffreyweir]

Interesting work-around, lolith. Thank for that.