Query HUGE Excel Range With SQL

The following Code errors on the SQL statement if I make the range to 66K rows, It works fine on 65K. Why? My real range will be like 200K

Excel 2003 has a limit of 65,536 rows. I noticed in your signature you said you have Excel 2007, so you'll have to use that version as this has 1,048,576 rows available.

i'd like to replace the hard keyed range with the range variable I set as TableA

What part of the code do you want to use this range? The command to set the 'strSql' variable refers to a different tab name

I also set the sheet to a variable ws1

Assuming this tab will be the first tab (i.e. furtherest left of all tabs (hidden and unhidden)) in the workbook, you can use this:

Please Login or Register  to view this content.

HTH

Robert

But if TableA contains to many rows...bam! Error, what gives??

Ah, there seems to be a SQL (64K) limit rather than an Excel limit on the number of rows that can be imported via this method (see here for more info and a potential workaround).

Error says Microsoft Access Database engine could not find object 'sheet1$H9:J200000'

At a guess I would say this is missing an exclamation, i.e. should be sheet1!$H9:J200000 If not, return the part of the code that works to a message box i.e. MsgBox TableA.Address(False, False) and note what is exactly needed.

HTH

Robert

Change the query to:

Please Login or Register  to view this content.

and it should work.

How will a 2003 format workbook assist with querying 66k rows?

The asker originally stated that the posted code works when specifying a range down to row 65000 but not if you specify row 66000.
The code you provided won't work as it uses the Jet provider and specifies the Excel 8 BIFF format which is not applicable here.

Hi and welcome to the forum.

Unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

It seems you are correct - I must not have tested that very well. However, using:

Please Login or Register  to view this content.

works for me in 2007 and 2010 - although I am aware that having 2010 installed on the same machine may make a difference.