It is quite simple to link an Access database table or a select type query to an Excel worksheet. Once the link has been defined, the data is saved on the worksheet and can be refreshed periodically or on demand.
Excel 2003 select menu option: Data | Import External Data | Import Data... |
Would you like to learn more?
If a function is required in each row in an adjacent column to the linked data, this can be automatically copied down all of the matching rows. Type a formula in an adjacent cell and Excel will use it to generate a new column. If the main table data is refreshed and lines or added or removed, the formula column will be similarly adjusted.
To do the same thing in Excel 2003 - select a cell within the range of the data table and then open the menu option: Data | Import External Data | Data Range Properties | or alternatively right mouse button click and select Data Range Properties from the short context menu. The final option in the dialog box allows you to 'Fill Down formulas in columns adjacent to data'.
The data can be updated (to match the source database) at any time using the right mouse button click and select 'Refresh' from the short context menu.
The Excel ribbon button for connecting to Access is intended for tables and a different method is required to connect to a database query.
Select [Data] Get external data | From other sources | From Microsoft query |.
In the Choose Data Source dialog box, select the MS Access Database option. In the Open file dialog box, find the appropriate database.
The microsoft Query program is separate to Excel, but is tightly integrated and sets up the live connection between Access and Excel.
The Query Wizard then starts and prompts you to choose a query and then select the required fields.
Although the Access query may itself contain criteria to exclude unwanted records, the Query Wizard offers you the option of adding additional filters before importing the data.
Another dialog lets you choose a field on which to sort the data.
Finally the wizard lets you return data to Microsoft Excel. In the Import Data dialog box, select a worksheet and the cell in which to place the top left hand corner of the imported records.
The option [Data] Connections | Connections | Properties | contains the settings for refreshing the data - either upon opening the spreadsheet or after a set time interval.
Note that while an Excel spreadsheet containing a linked table is open, the source Access database can only be opened in 'read-only' mode. It is effectively locked cannot be edited.
Once a data table has been created on a worksheet, it can be interrogated and summarised on another worksheet using functions such as SUMPRODUCT(), MATCH() or VLOOKUP(). If a list of account codes is linked and imported, it could be used to verify the input of codes on another form.
It is also possible to instruct Access to pass query results to a specific location within an Excel file.
|The information on this page is NOT
in the Excel functions guide.
|Click here for details about
obtaining this file
|file: excel-import-access.htm||Page last updated: Mar12||© MeadInKent.co.uk 2012|