Excel 2007 can import data from an Access 2007 database table into a worksheet, a process known as making an external data query. After importing the data into Excel, you can then use the Filter buttons attached to the various fields to sort and filter the data as in an Excel table.
To make an external data query to an Access 2007 database table, follow these steps:
Click the From Access command button in the Get External Data group on the Data tab.
Excel opens the Select Data Source dialog box.
Databases created and maintained with Access are not, of course, the only external data sources on which you can perform external data queries. To import data from other sources, click the From Other Sources button on the Data tab to open a drop-down menu with several other options.
Select the Access database file containing the table you want to import, and then click Open.
The Select Table dialog box appears (unless the database contains only one table).
Select the name of the Access data table that you want to import into the worksheet and click OK.
The Import Data dialog box appears.
Specify how and where to import the Access data in the Import Data dialog box.Select the desired options in the Import Data dialog box.
This dialog box contains the following option buttons:
Table to have the data in the Access data table imported into an Excel table in either the current or a new worksheet.
PivotTable Report to have the data in the Access data table imported into a new pivot table that you can construct with the Access data.
PivotChart and PivotTable Report to have the data in the Access data table imported into a new pivot table with an embedded pivot chart that you can construct with the Access data.
Existing Worksheet (default) to have the data in the Access data table imported into the current worksheet starting at the current cell address.
New Worksheet to have the data in the Access data table imported into a new worksheet that’s added to the beginning of the workbook.
Click OK.
The imported data from the specified Access table appears in the worksheet.
A worksheet after importing an Access data table.
Excel keeps a list of all the external data queries you make so that you can reuse them to import updated data from another database or Web page. To reuse a query, click the Existing Connections button on the Data tab to open the Existing Connections dialog box. Click the name of the query to repeat and click Open.