How do I export Access data to Excel - Part 1

There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

1. Manual Methods (This post) 
   • Cut and Paste
   • Manual Export
   • Saved Exports
2. TransferSpreadsheet Methods
   • Macros
   • VBA
3. Office Automation Methods
   • Do…While
   • CopyFromRecordSet

Linked Excel Sheets Not Updateable
One surprising way that you CANNOT export Access data to Excel is by linking the spreadsheet into Access. Linked Excel spreadsheets are Read-Only in Access. Why? 
The answer is not technical but legal.  Several years ago, Microsoft lost a patent infringement lawsuit that involved the ability to update records in Excel from Access.  The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.
In this part, I’ll discuss manual methods and leave TransferSpreadsheet and Office Automation for later.
Cut and Paste
I remember a time when I couldn’t take for granted that everyone knew what cut and paste was or how to do it.  That’s no longer the case, but I thought I should mention it for completeness.
Sometimes the quickest and easiest way to get Access data into excel is just open the table or query in Datasheet View

and paste it into a blank workbook.

There are limitations, of course.  You can copy whole rows


or whole columns


But not a combination.  You can work around this, however, by creating a query that limits the rows and columns to just those you want. 
Manual Export
You can also export Access data to Excel without opening a workbook by using Access’s built in export facility.  With this, you can export tables or saved queries to either a new or existing Excel workbook.  There a several ways to do this.
On the Ribbon, there is the External Data tab where you can export in many different formats. Select the query or table you want to export in the Navigation Pane, and select Excel on the Export section of the External Data tab.

Or, you can Right Click on the object for context sensitive menu

After that, it’s just a matter of giving a file location and naming the file.

By default, the name will be the same as your table or query.  You can, of course, change either at this point.
New Vs. Existing Excel Workbook
You can export to either a new workbook or an existing workbook.There are several possibilities:

1. New Workbook – XLSX file does not exist
   • If the file name (TheDataQuery.xlsx) does not exist in that folder, a new workbook will be created with that name and a tab named for the object (TheDataQuery).
   • If you change the filename in the  and that new name also does not exist, the file will be created under the new name also with a tab named for the object.
2. Existing Workbook
   • If the workbook exists, AND it already has a tab named for the object, the data in the tab will be over-written.This will become important later when I discuss the TransferSpreadsheet Method. (Note: if the new dataset is narrower or shorter than the data already on the tab, only the cells in the shape of the new data will be over-written.) 
   • If the workbook exists and it DOES NOT have an existing tab named for your object, a new tab will be created, leaving all existing tabs alone.

Saved Exports
After clicking OK in the Excel – Export Spreadsheet dialog, you will be given one last option: Save Export Steps. If you check the Save export steps box, the export will be saved.

Running Saved Exports
Lastly, to run the saved export, just click the Saved Exports button on the External Data ribbon tab.
This has some limited utility, but no flexibility. It will export the same object to the same file in the same location. On the plus side, you don’t have to go through the whole Export Wizard again.
Next time in How do I export Access data to Excel - Part 2, I’ll discuss more flexible export automation techniques.