We have already covered how to take a static snapshot of data from a table or report and put the data in an Excel spreadsheet. Though the scope of this topic is a little beyond this manual, Access and Excel have the ability to share data between one another.

For example, if you want to create weekly reports about how your business is doing using the tables and charts in Excel, you can tell Excel to get data from an external source, in this case a Microsoft Access file. This way whenever the Excel file is opened, it will refresh the information it retrieved from the Access database and give up-to-the-minute results about sales.

In a nutshell, there are a few things you should do to the data in Excel before you link to Access. Make sure all the data is neatly organized, with Field headings (or column headings) in the source data. If the source data is organized in such a way that organizing the data into columns is not practical, specify named ranges for the data instead. (See Excel’s help file for more information on named ranges.) Make sure that all the data in one column is the same format, meaning you should not have text and numerical data in the same column. Also note that if you have any graphics or diagrams in the Excel file, you will not be able to see them in Access.

Open the database you wish to import the data into and click File à Get External Data à Link Tables. Select the destination folder of the spreadsheet which you want to link, select Microsoft Excel from the Files of Type combo box, click the name of the spreadsheet, and click the Link button:

If your data is neat and organized, the Link Spreadsheet Wizard is only a few steps. If your database contains many worksheets or named ranges, you can specify which range you want to import. Next, specify if the first row of data contains field names (this will make table construction much easier and is a good idea to do beforehand anyway!):

Click Next to proceed to the final step, where you will give the linked table a meaningful name. By default, the name of the linked table will be the same name as the filename of the spreadsheet.