Microsoft Office Tutorials and References
In Depth Information
Importing Data from Microsoft Access
Figure 18-3: Use the Save Export Steps option if you export your data frequently.
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.
You may export your Access table or query to an existing Excel file instead of creating a
new file. But note the following: the name of the exported object is 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 the PriceMaster
table to an Excel worksheet that already has a worksheet named PriceMaster will
cause the 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 will likely receive an
error in Access.
The Get External Data icon
The option to pull data from Access has been available in Excel for many versions; it was just buried
several layers deep in somewhat cryptic menu titles. This made getting Access data into Excel seem
like a mysterious and tenuous proposition for many Excel analysts. With the introduction of the
Ribbon in Excel 2007, Microsoft put the Get External Data group of commands right on the Ribbon
under the Data tab, making it easier to import data from Access and other external data sources.
Excel allows you to establish an updatable data connection between Excel and Access. To see the
power of this technique, walk through these steps:
1. Open a new Excel workbook and select the Data tab on the Ribbon.
2. In the Get External Data group, select the From Access icon.