Access Export to Excel (2007)
When I tried to export a table/query from Access 2007 to Excel 2007, I was getting this annoying error:
You selected more records than can be copied onto the Clipboard at one time. Divide the records into two or more groups, and then copy and paste one group at a time. The maximum number of records you can paste at one time is approximately 65,000.
Access 2007 Export to Excel Error
I was unable to understand the reason, because I thought Excel 2007 did not limit number of rows to 65,536. It turns out however that it is the clipboard limitation.
Solution: If you check off the “export data with formatting and layout” option, Access exports successfully.
You can export successfully using Transferspreadsheet (VBA) option too.
Solution Update:Thanks to Brett, it looks like that only if you export table/query using right click > Export and have the checkbox cleared, then Access will export succesfully. That is what I always do, and it works for me. I have never tried External data option for exporting.
![[CiteULike]](http://nandeshwar.info/wp-content/plugins/bookmarkify/citeulike.png)
![[del.icio.us]](http://nandeshwar.info/wp-content/plugins/bookmarkify/delicious.png)
![[Digg]](http://nandeshwar.info/wp-content/plugins/bookmarkify/digg.png)
![[Facebook]](http://nandeshwar.info/wp-content/plugins/bookmarkify/facebook.png)
![[Furl]](http://nandeshwar.info/wp-content/plugins/bookmarkify/furl.png)
![[Google]](http://nandeshwar.info/wp-content/plugins/bookmarkify/google.png)
![[Reddit]](http://nandeshwar.info/wp-content/plugins/bookmarkify/reddit.png)
![[Slashdot]](http://nandeshwar.info/wp-content/plugins/bookmarkify/slashdot.png)
![[StumbleUpon]](http://nandeshwar.info/wp-content/plugins/bookmarkify/stumbleupon.png)
![[Technorati]](http://nandeshwar.info/wp-content/plugins/bookmarkify/technorati.png)
![[Twitter]](http://nandeshwar.info/wp-content/plugins/bookmarkify/twitter.png)
![[Email]](http://nandeshwar.info/wp-content/plugins/bookmarkify/email.png)
Actually, this is incorrect. The solution provided by M$ is pure rubbish and does not resolve the issue. Don’t believe me? I can show a total of 8 databases I tried to export this morning alone and not one of them would export. The issue is not within office but rather in the clipboard by the looks of things
Diana, thanks for your comment. I am rather surprised that it didn’t work for you. I had to scratch my head for long when I could not export big tables, but checking that option off worked for me. Can I know the details of the table? If you are exporting about eight tables, you should try and write a VBA procedure to export all the tables in your db to Excel files.
Oddly enough, I’ve found that if you go to the External Data -> Export -> Excel that you will experience Diana’s issue. However, If you right click on your table/query and then go to Export -> Excel, it will work if you have the formatting checkbox off.
Thanks for your comment, Brett. Hmm.. now that is something. I have always exported using right click on the table/query, and that is why checking off worked for me. Let me update my post with your solution. Thanks!