Excel Dashboards & Reports For Dummies
Book image
Explore Book Buy On Amazon

Microsoft Access is used in many organizations to manage a series of tables that interact with each other, such as a Customers table, an Orders table, and an Invoices table. Managing data in Access provides the benefit of a relational database in which you can ensure data integrity, prevent redundancy, and easily generate datasets via queries.

Access has an Export wizard, and it's relatively simple to use. Just follow these steps:

  1. With your Access database open, click your target table or query to select it.

  2. On the External Data tab on the Ribbon, select the Excel icon under the Export group.

    The wizard that you see here opens.

    Export data to Excel using the Excel Export Wizard.
    Export data to Excel using the Excel Export Wizard.

    As you can see, you can specify certain options in the Excel Export wizard. You can specify the file location, the file type, and some format preservation options.

  3. In the Excel Export Wizard, select Export Data with Formatting and Layout and then select Open the Destination File After the Export Operation is Complete.

  4. Click OK.

    Excel opens to show you the exported data.

In Access, the last page in the Export wizard, shown in the following figure, asks whether you want to save your export steps. Saving your export steps can be useful if you expect to frequently send that particular query or table to Excel. The benefit to this method is that unlike dragging and dropping, the ability to save export steps allows you to automate your exports by using Access macros.

Use the Save Export Steps option if you export your data frequently.
Use the Save Export Steps option if you export your data frequently.

You may export your Access table or query to an existing Excel file instead of creating a new file. But note that the name of the exported object will be the name of the table or query in Access. Be careful if you have an Excel object with that same name in your workbook, because it may be overwritten.

For example, exporting an Access table named PriceMaster to an Excel worksheet that already has a worksheet named PriceMaster causes the original Excel PriceMaster worksheet to be overwritten. Also, make sure the workbook to which you're exporting is closed. If you try to export to an open workbook, you'll likely receive an error in Access.

About This Article

This article is from the book:

About the book author:

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

This article can be found in the category: