Using Data from Access in Excel

© 2007 KnowWare International, Inc. DBA LifeStar
2253 S. Oneida St., Ste 3D
Denver, CO 80224

Jay Arthur
888-468-1537
303-756-9144
Email Us

We help people think!

You can:

Export/Import Access Data

Make sure fields you select are numeric by choosing design view and changing field type to number:
Select FILE-SAVE AS/Export,
Specify Excel format in "Save as type" box at bottom of screen and EXPORT.
Open the Excel file, select the desired data. (Note if numeric fields are left aligned, not right, the Access fields are "text" not "number" and will not allow you to run the macros. Go back to Access and change the field layout to numeric):
Run the desired chart:

Using QI Macros In Microsoft Access

Once you have a database, you can analyze the database by linking Excel and using the QI Macros.
The database might look like the following:
To use the QI Macros, you have to ensure that the data type of numeric fields is number, not text. Use View-Table Design to change the data type of all numeric fields to 'number'.
Then, select the table, query, form, or report you want to save and load into Excel. From the Access menu select Tools-OfficeLinks-Analyze It With MS Excel.
This will open Excel and create a .XLS file, which you can chart and graph using the QI Macros.

Using Microsoft Access Data from Excel
1. To link to Access data and get information, you'll need to select Tools-Add-ins and check AccessLinks Addin and MS Query Add-in. If they aren't visible, choose browse and look in Library. If they still aren't visible, you'll need to copy them in from your installation CD.
2. Then, from anywhere on your worksheet, you can select Data-Get External Data
3. This will start MS Query. Select MS Access Database.
4. Then select the database you want to use.
5. Then, using the Query Wizard, click on the > sign to select the fields you want.
6. Then the Query Wizard will let you filter any fields, or just click Next.
7. You can change the sort order, or just click Next:
8. Then, click Finish to complete the query:
9. Excel will ask you about getting the data and where to put it on the spreadsheet:
10. Excel may prompt you again to select a database:
11. Excel will then import the data.
12. You can then select Data-Refresh Data to update the data as required.
13. Excel will go to the Access database and update the spreadsheet.