using transferspreadsheet to export query result from Access 2003 to Excel 2010

[rickross99]

I'm using access 2003 and the Export function cuts off at 65,000 rows when i export my query results to excel. I was reading that

DoCmd​

.TransferSpreadsheet doesn't have this limitation. Can someone help me with a macro example that uses this function in Access?

 

[TerryHogarth21]

you could also do this easier with MS Query into Access
Use Microsoft Query to retrieve external data - Excel

If you are on Excel 2010 it's on the Ribbon of Data -> From Other sources and follow it from there to link it to your DB and then put your SQL Command there.
If you get stuck post your access query here.

 

[Joe4]

Welcome to the Board!

I was reading that DoCmd.TransferSpreadsheet doesn't have this limitation.

I think that depends upon which version of Access you are using. If you read something that suggests otherwise, please post that link here.

I believe that the issue is that Access 2003 only has the ability to export to Excel files with a ".xls" extension, and not the new ".xlsx" extension introduced in Excel 2007. And Excel files with the ".xls" extension only allow to export up to 65,536 rows.

Here are a few options/workarounds (there are others):
1. Upgrading Microsoft Office to 2007 or newer to take advantage of this new option.
2. Exporting the file to a text file, where that text file can now be imported into Excel.

 

[rickross99]

Welcome to the Board!


Here are a few options/workarounds (there are others):
1. Upgrading Microsoft Office to 2007 or newer to take advantage of this new option.
2. Exporting the file to a text file, where that text file can now be imported into Excel.

thank you! We upgraded to 2010 and it works now and I used the exporting to text file as a workaround in the meantime.

 

[Joe4]

Your welcome! Glad you got it all sorted out!