Written by 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.
Related posts:
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!
I lost 4 hours trying to figure out what was wrong with the export of 380k record from Access 2007 using the export function from the main menu. However right-clicking on the Query results and export to Excel as mentionned in the thread worked beautifully. This is very annoying and time consuming. How dumber can this be ? Thanks for the hint. MS screwed up again.
Thanks,
I haven’t found the right-click method to work when the normal export doesn’t, whether or not the formatting box is checked. I think that “solution” was just luck.
The limitations of the clipboard are also not the reason for failure.
I’ve had the problem on occasion and other times I’ve successfully exported hundreds of thousands of records.
I believe the problem has to do with how the database was created in the first place – at least in my case. My original database was created using Access 2003 and I opened it with Access 2007. More than 64 KB rows of records wouldn’t export. It would not export as text either.
I “converted” the database to Access 2007 using “Save as” and I still couldn’t export over 64 KB of rows to Excel 2007 (also would not export as text).
I think that the limitations of the Access 2003 version are carried forward to the 2007 version of Access when converted by either of these methods but this is just a guess.
What I had to do was create a new, blank, Access 2007 database and import the tables and queries I needed to produce my export file for Excel. After doing this I was able to export several hundred thousand records to Excel by right-clicking on the query object and selecting Excel OR by using the Excel export button under the “External Data” tab.
Hope this helps.
Paul, thanks for your reply and other suggestions. I do not think it was pure luck. Formatting does take significant resources and right-click does give you that option. If exporting worked for you after creating a new database, could it be a db corruption issue?
I was having similar problems exporting more than 65K+ from an Access 2007 db to Excel. I did find a workaround…Export the table/query from Access as a txt file and then open that file in Excel. It worked for a 290K+ line table I had. This was the only way I was able to export the whole table at one time.
I am trying to export via a query and I don’t see an option to export without formatting, any ideas?
thanks.
Thanks for the quick solution a7n9, I right clicked on the table as you suggested and it exported 236K records flawlessly.
I have the some issue in exporting a report excel.
I have header as test report
where as in excel i get this printed as “40789″ in place of test report. it is kind of format error. any clue why this happens?
The reason this is happening is because the database is older than 2007 the database needs to be converted to MS Access 2007
ext – accdb . Go to save as and you can save it as the new version.
i was able to build an access database in 2003 and create a macro which exported more than 65,000 records with no problem. i am trying to build a new macro and get the error of cannot export more than 65,000 records. what is the workaround for a macro.
larry