Kusleika and I are at the Excel and Access Power User Workshop this week. Between you and me, he’s not doing that well.
Anyway, one of our attendees asked why Access 2007 can’t export more than 65,000 rows to Excel 2007. I replied with my usual professorial demeanor “What? You’re on crack!”
This is when he demonstrated these steps:
- Right click on the Table or Query
-
Click Export -> Excel
- Click the “Export data with formatting” option
- Press OK
After a few gyrations, Access pops up this nasty-gram. Indeed, the table gets cut off at 65,000 lines.
Of course, being the expert I am, I immediately pointed out the issue.
The problem comes from the selection of “Export data with formatting” in Step 3.
If you uncheck this (leave it not checked), Access will export more than 65,000 rows just fine.
The reason for this isn’t all that transparent, but it’s pretty simple.
When the Export data with formatting option is checked, the windows clipboard is used. That is, Access will send the data to the Windows clipboard so that all the formatting and layout can be copied. The problem is that the old windows clipboard limits you to only 65,000 lines of data. Leaving this option unchecked allows access to simply transfer the data, bypassing the need for the windows clipboard. Problem solved.
He was pretty happy with this answer – although, he still wants his money back.
RELATED STUFF
- Using Access to Combine Multiple Excel Files: Method 1
- Sending Data to Excel using Access SQL
- Using Access to Combine Multiple Excel Files: Method 2
- Sending Variable Access Data To An Excel Range
- Running an Access Parameter Query from Excel
- Documenting Access Queries in Excel
- Easily Move Access Query Results to Excel
- The Benefits of a Good Technical Editor
- Clearing Access ImportError Tables
- Avoiding Data Cut-Off in Excel
A better and more secure way would be to pull the data into excel via an ODBC connection to access.
Thus nullifying the clipboard issue.
I am using Access 2007 to Excel 2007 and the first fix listed on this page is not working. If I select no formating and begin the process the window asking for the exporting formats simply closes and no export file is generated. This happens it I attempt to export it to a TXT file as well.
I have no idea what an ODBC connection is.
I thank you in advance for any help. How it is that this software is developed without accounting for these eventualities is beyond me.
This does not resolve the issue at all. It still cuts off at 65535 lines.
Thanks So much, it worked.. this is one of those where you feel gratitude for the internet, and people that take the time to publish this knowledge. THANKS THANKS
Thanks so much for sharing this knowledge, it works perfectly.
Hi,
I use a macro to run several queries to gather the correct info. Then i run the export to excel.
But how do I switch off “export data without formatting and layout” in a macro ?
ODBC is a MUCH MUCH better way to go. I linked 1,000,000 rows of data in about 30 seconds!
Thanks so much for this, I was spending ages trying to figure out how to export large amounts of data from Access to Excel. This was much appreciated.