Access 2016 Advanced

Lesson 7 – Exporting Data

Access 2016

E XPORTING D ATA TO AN E XCEL W ORKBOOK



Discussion

You can export database records from a database file in Access to a workbook in Excel. The Access data source can be a table, query, form, report, view, or macro. When you export data from a database, Access creates a workbook file in Excel with the data from the database. Any field names from the data source appear in the first row of the workbook. When you export to Excel, you can choose to export to an existing workbook, or you can indicate that you want to export to a new workbook by providing a new name in the File name box in the Export dialog box. You can also choose to save the fonts, field widths, and data displayed from Lookup fields in your Access datasheet by selecting the Save formatted option before exporting.

Exporting data to Excel is a useful way to display and work with data using the capabilities of Excel. For example, you can transfer a table containing investment portfolio information to Excel to perform calculations on investment performance.

NOTE When you export to an existing Excel workbook file, the data in the workbook is deleted and replaced by the Access data, unless you are exporting to an Excel version 5.0 or later file, in which case the data is copied to the next available worksheet. When you export a form or a table containing a subform or subdatasheet, Access exports only the main form or table. To export the subform or subdatasheet, you must perform a separate export operation on each object.



Procedures

1. Select the data source you want to export.

2. On the External Data tab in the Export group select the Excel button . 3. Select the desired file format in the File format field.

4. Select

.

Page 78

OFFICEPRO, Inc.