This is a problem that I have asked about many times and one that you will no doubt have come across if you use Access and Excel together and export any data from Access on a regular basis. The issue is an error or warning in Microsoft Access when trying to export data to Microsoft Excel that says
‘You selected more records than can be copied onto the clipboard at one time’ when exporting data from Microsoft Access to Microsoft Excel.
By default when exporting data from Access you would normally follow this method
- Select External data
- Got to Export
- Hit Excel
- You will be given a choice to select the destination for your data
- Select where you want to save your exported data
- In the Specify Report Options Area- you have the option to select and tick Export Data With Formatting And Layout
- You also have the option to open the destination file after the export operation is complete- tick if you want to utilise this option
- Hit OK
- You may now get the above error at this stage if you have ticked the Export Data With Formatting And Layout Option and are trying to export more than 65,000 data lines
- When the data is exported you can then hit Close
From Excel 2007 onwards the capacity of Excel Rows is way more than previous versions (in fact it is 1,048,576 whereas previous versions the capacity. So, why is Access limiting me to 65000 lines?! like in Pre 2007 times.
Not quite pre- historic but it is sure as heck feeling like it when you get the above warning when exporting data.
When you select the Export Data With Formatting And Layout Option in Specify Report Options Excel will actually copy the contents to the old style Office Clipboard, hence you get the warning of not being able to copy more than the 65,000 lines.
So, to avoid the error all you need to do is un tick the Export Data With Formatting And Layout Option in Specify Report Options Excel, if it is ticked then go right ahead and hit OK. This will enable you to export more than one million lines- from Access, but no formatting or layout settings will be transferred over.
The issue is not one that is difficult to resolve but it is not an obvious solution either. But once you know why the issue is happening it is really easy to resolve.