Access: Export two Access tables into individual sheets in an Excel spreadsheet in Access 2003/XP/2000/97

Question:  In Access 2003/XP/2000/97, I want to set up a form with a button that a user can click. When the button is clicked, I want to export two Access tables into an Excel spreadsheet. I want each table to appear on its own sheet in the Excel file.

Answer:  We've created a sample Access database that you can download that demonstrates how to export two Access tables into the same Excel file.

Download version in Access 2000

Let's take a look at the example. Below, we have a form that allows you to enter a path to the Excel file and then a button called "Export tables ...". When the user clicks on this button, Access will export the inventory and inventory_details tables to the Excel file.

When the export has completed, the user will get the following message:

If you open up the Excel spreadsheet, you can see that there are two sheets - one called inventory and a second called inventory_details. Each of these sheets contains the data from the associated table.