Applies to: Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007.
Using the Export Wizard, you can export data from an Access database to in a file format that can be read by Excel. This article explains how to prepare and export your data to Excel, and even offers you some troubleshooting tips for common problems that might occur.
In this article
Exporting data to Excel: the basics
After you export data to Excel, Access forms a copy of the selected data, and then stores the copied data in a file that can be opened in Excel. If you copy data from Access to Excel regularly, you can save the details of an export operation for future use, and also schedule the export operation to perform automatically at predefined intervals.
Common scenarios for exporting data to Excel
- Your department or workgroup uses both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyse the data and to distribute the results of your analysis. Your team presently exports data to Excel as and when they must, but you want to streamline this process.
- You are a long-time user of Access, but your manager favours working with data in Excel. At frequent intervals, you do the work of copying the data into Excel, but you want to automate this process to save yourself time.
About exporting data to Excel
- Access excludes a “Save As” command for the Excel format. To copy data to Excel, you must use the Export feature described in this article, or you can copy Access data to the clipboard and then paste it into an Excel spreadsheet.
- You can export a table, query, form, or report. You can also export selected records in a multiple-record view, such as a datasheet.
- Microsoft Excel includes a command to import data from an Access database. You can use that command instead of the export command in Access; however, the Excel import command only allows you to import tables or queries. For more information, see the Excel Help article Connect to (import) external data.
- You cannot export macros or modules to Excel. When you export a form, report, or datasheet that contains subforms, subreports, or subdatasheets, only the main form, report, or datasheet is exported. You must repeat the export operation for each subform, subreport, and subdatasheet that you want to export to Excel.
- You can only export one database object in a single export operation. However, you can merge multiple worksheets in Excel after you complete the individual export operations.
Prepare for the export operation
Before conducting an export procedure, it is sensible to check the data that you want to export to ensure that it is free of any error indicators or error values. If there are any errors, aim to resolve them before you export the data to Excel. Otherwise, problems can emerge during the export operation, and null values may be imported into cells in the Excel worksheet. For more information about problems that can occur when exporting to Excel, see the section Troubleshoot missing and incorrect values.
If the source object is a table or a query, choose whether you want to export the data with or without its formatting. This decision influences two aspects of the resulting workbook — the amount of data that is exported and the display format of the data. The following table summarises the outcome of exporting formatted and unformatted data.
|Export||Source Object||Fields and records||Formatting|
|Without formatting||Table or query|
Note: Forms and reports cannot be exported without their formatting.
|All fields and records in the underlying object are exported.||The Format property settings are ignored during the operation.|
For lookup fields, only the lookup ID values are exported.
For hyperlink fields, the contents are exported as a text column that displays the links in the format displaytext#address#.
|With formatting||Table, query, form, or report||Only fields and records that are displayed in the current view or object are exported. Filtered records, hidden columns in a datasheet, and fields not displayed on a form or report are not exported.||The wizard respects the Format property settings.|
For lookup fields, the lookup values are exported.
For hyperlink fields, the values are exported as hyperlinks.For rich text fields, the text is exported but the formatting is not.
- Select the destination workbook and file format. Note that reports can only be exported in the older *.xls file format, not in the newer *.xlsx file format. During the export operation, Access urges you to set the name of the destination workbook. The following table describes once a workbook is designed (if it does not already exist) and when it is updated (if it does already exist).
|If the destination workbook||And the source object is||And you want to export||Then|
|Does not exist||A table, query, form, or report||The data, with or without the formatting||The workbook is created during the export operation.|
|Already exists||A table or query||The data, but not the formatting||The workbook is not overwritten. A new worksheet is added to the workbook, and is given the name of the object from which the data is being exported. If a worksheet having that name already exists in the workbook, Access prompts you to either replace the contents of the corresponding worksheet or specify another name for the new sheet.|
|Already exists||A table, query, form, or report||The data, including the formatting||The workbook is overwritten by the exported data. All existing worksheets are removed, and a new worksheet having the same name as the exported object is created. The data in the Excel worksheet inherits the format settings of the source object.|
The data is constantly transferred in a new worksheet. You cannot import the data to any current worksheet or named range.
Run the export operation
- If the destination Excel workbook is open, exit it before you continue.
- In the Navigation Pane of the source database, choose your preferred object to export.
Export only a portion of the data
If the object is a table, query, or form, and you want to export only a portion of the data, open the object in Datasheet view and select the records you want.
To open a form in Datasheet view:
a. Double-click the form to access it.
b. Right-click the form, and then pick Datasheet View. If this option is unavailable:
i. Press Design View.
ii. Press F4 to show the Property Sheet task pane.
iii. Click Form from the drop-down list at the top of the Property Sheet.
iv. On the Format tab of the Property Sheet, format the Allow Datasheet View property to Yes.
v. On the Design tab, in the Views group, pick Datasheet View.
Note: You cannot export a portion of a report. However, you can choose or open the table or query that the report is based on, and then export a portion the data in that object.
- On the External Data tab, in the Export group, press Excel.
- In the Export – Excel Spreadsheet dialogue box, carefully read the suggested file name for the Excel workbook (Access uses the name of the source object). If you want, you can modify the file name.
- In the File Format box, choose the file format that you want.
- If you are exporting a table or a query, and you want to export formatted data, click Export data with formatting and layout. For more information, see the section Prepare for the export operation.
Note: If you are exporting a form or report, this option is always selected but unavailable (it appears dimmed).
- To check the destination Excel workbook after the export operation is complete, tap the Open the destination file after the export operation is complete checkbox. If the source object is open, and if you picked one or further records in the view before starting the export operation, you can choose Export only the selected records. To export every record illustrated in the view, leave this checkbox cleared.
Note: This checkbox remains unavailable (dimmed) if no records are selected.
- Press OK. If the export operation stalls due to an error, Access presents a message that outlines the cause of the error. Otherwise, Access exports the data and, depending on your selection in step 7, launches the destination workbook in Excel. Access then shows a dialogue box in which you can design a specification that uses the details from the export operation.
What else should I know about exporting?
- For information on how to save the details of your export into a specification that you can reuse later, see the article Save the details of an import or export operation as a specification.
- For information on how to run saved export specifications, see the article Run a saved import or export operation.
- For information on how to schedule specifications to run at specific times, see the article Schedule an import or export operation.
- For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks.
Troubleshoot missing and incorrect values
The following table summarises several ways of troubleshooting frequent errors.
Tip: If you find that just a few values are missing, fix them in the Excel workbook. Otherwise, fix the source object in the Access database, and then repeat the export operation.
|Issue||Description and resolution|
|Calculated fields||The results of calculated fields are exported; the underlying expressions that perform the calculations are not.|
|Multivalued fields||Fields that support multiple values are exported as a list of values separated by semicolons (;).|
|Pictures, objects, and attachments||Graphical elements (such as logos, contents of OLE object fields, and attachments that are part of the source data) are not exported. Add them to the worksheet manually after you complete the export operation.|
|Graph||When you export a form or report that contains a Microsoft Graph object, the graph object is not exported.|
|Data in the wrong column||Null values in the resulting worksheet are sometimes replaced by the data that should be in the next column.|
|Missing date values||Date values earlier than Jan 1, 1900 are not exported. The corresponding cells in the worksheet will contain a null value.|
|Missing expressions||Expressions that are used to calculate values are not exported to Excel. Only the results of the calculations are exported. Manually add the formula to the Excel worksheet after you complete the export operation.|
|Missing subforms, subreports, and subdatasheets||When you export a form, report, or datasheet, only the main form, report, or datasheet is exported. You must repeat the export operation for each subform, subreport, and subdatasheet that you want to export.|
|Missing or incorrectly formatted columns||If none of the columns appear to be formatted in the resulting worksheet, repeat the export operation, being sure to select the Export data with formatting and layout checkbox in the wizard. Conversely, if only some columns seem to be formatted uniquely from the versions in the source object, apply the formatting that you want manually in Excel.|
Format a column in Excel – Open the destination Excel workbook, and then display the worksheet that contains the exported data.
Right-click a column or a selected range of cells, and then select Format Cells.
On the Number tab, below Categories, select a format, such as Text, Number, Date, or Time.
|“#” value in a column||You may see the value # in a column that corresponds to a Yes/No field in a form. This can be the outcome of starting the export operation from the Navigation Pane or in Form view. To resolve this issue, open the form in Datasheet view before exporting the data.|
|Error indicators or error values||Check the cells for error indicators (green triangles in the corners) or error values (strings that begin with the # character instead of the appropriate data).|