You can copy data from a Microsoft Office Access 2007 database into a worksheet by exporting a database object to a Microsoft Office Excel 2007 workbook. You do this by using the Export Wizard in Office Access 2007.
In this article
Exporting data to Excel: the basics
If your goal is to temporarily or permanently move some of your Access data to Office Excel 2007, you should consider exporting it from your Access database into an Excel workbook. When you export data, Access creates a copy of the selected data or database object, and then stores the copy in an Excel worksheet.
Exporting is also the best technique for you to use if you expect to copy data from Access to Excel frequently. When you perform an export operation, you can save the details for future use, and even schedule the export operation to run automatically at set 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 analyze the data and to distribute the results of your analysis. Your team currently exports data to Excel as and when they have to, but you want to make this process more efficient.
- You are a long-time user of Access, but your manager prefers to work with data in Excel. At regular intervals, you do the work of copying the data into Excel, but you want to automate this process to save yourself time.
If this is the first time you are exporting data to Excel
- To export data from Access to Excel, you must be working in Access. Excel provides no mechanism for importing data from an Access database. Also, you cannot save an Access database or table as an Excel workbook by using the Save As command of Access. It is important to note that the Save As command of Access lets you save an Access object in the current database only as another Access database object.
- You can export a table, query, or form. You can also export selected records in a view.
- 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 the data in multiple worksheets in Excel after you complete the individual export operations.
Top of Page
Prepare for the export operation
- Open the source database.
- In the Navigation Pane, select the object that contains the data that you want to export.
You can export a table, a query, a report, or a form.
- Review the source data to make sure that it does not contain any error indicators or error values.
If there are any errors, you must resolve them before you export the data to Excel. Otherwise, errors can occur during the export operation, and null values might be inserted into fields.
- If the source object is a table or a query, decide whether you want to export the data with or without its formatting.
This decision affects two aspects of the resulting workbook — the amount of data that is exported and the display format of the data. The following table describes the outcome of exporting formatted and unformatted data.
||Fields and records
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#.
||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.
- Choose the destination workbook and file format.
During the export operation, Access prompts you to specify the name of the destination workbook. The following table summarizes when a workbook is created (if it does not already exist) and when it is overwritten (if it does already exist).
|If the destination workbook
||And the source object is
||And you want to export
|Does not exist
||A table, query, form, or report
||The data, with or without the formatting
||The workbook is created during the export operation.
||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.
||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 always added in a new worksheet. You cannot append the data to any existing worksheet or named range.
Top of Page
Run the export operation
- If the destination Excel workbook is open, close it before you continue.
- In the Navigation Pane of the source database, select the object that you want 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:
- Double-click the form to open it.
- Right-click the form, and then click Datasheet View. If this option is not available:
- Click Design View.
- Press F4 to display the Property Sheet task pane.
- Select Form from the drop-down list at the top of the Property Sheet.
- On the Format tab of the Property Sheet, set the Allow Datasheet View property to Yes.
- On the Design tab, in the Views group, click Datasheet View.
Note You cannot export a portion of a report. However, you can select 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, click Excel.
- In the Export - Excel Spreadsheet dialog box, review 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, select the file format that you want.
- If you are exporting a table or a query, and you want to export formatted data, select 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 view the destination Excel workbook after the export operation is complete, select the Open the destination file after the export operation is complete check box.
If the source object is open, and if you selected one or more records in the view before starting the export operation, you can select Export only the selected records. To export all the records displayed in the view, leave this check box cleared.
Note This check box remains unavailable (dimmed) if no records are selected.
- Click OK.
If the export operation fails because of an error, Access displays a message that describes the cause of the error. Otherwise, in accordance with the export options specified in the wizard, Access exports the data and either does or does not open the destination workbook in Excel. Access then displays a dialog box in which you can create a specification that uses the details from the export operation.
Save the export specification
- Click Yes to save the details of the export operation for future use.
Saving the details helps you repeat the same export operation in the future without having to step through the wizard each time.
- In the Save as box, type a name for the export specification. Optionally, type a description in the Description box.
- If you want to perform the operation at fixed intervals (such as weekly or monthly), select the Create Outlook Task check box.
Doing this creates a Microsoft Office Outlook 2007 task that lets you run the specification by clicking a button.
- Click Save Export.
If Outlook is installed, Access starts it.
Note If Outlook is not installed, an error message appears. If Outlook is not configured correctly, the Outlook Startup Wizard starts. Follow the instructions in the wizard to configure Outlook.
- In Outlook, review and edit the task settings (such as Due date and Reminder).
To make the task recur, click Recurrence. This figure shows the task scheduler with some typical settings:
For information about scheduling Outlook tasks, see the article Schedule an import or export operation.
- Click Save and Close.
Run a saved task
- In the Outlook Navigation Pane, click Tasks, and then double-click the task that you want to run.
- On the Task tab, in the Microsoft Office Access group, click Run Import .
- In Excel, open the destination workbook, and then verify that all the data were copied into the correct cells.
If you chose to copy only the data and not the formatting into an existing workbook, the workbook might contain multiple worksheets. Locate the sheet that corresponds to the export operation, and review the cells on that sheet.
Top of Page
Troubleshoot missing and incorrect values in the worksheet
The following table describes various ways of troubleshooting common 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.
||Description and resolution
||Fields that support multiple values are exported as a list of values separated by semicolons (;). See Excel Help for more information.
|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.
||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.
||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 check box in the wizard. Conversely, if only some columns appear to be formatted differently 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 click Format Cells.
- On the Number tab, under Categories, select a format, such as Text, Number, Date, or Time.
- Click OK.
|"#" value in a column
||You might see the value # in a column that corresponds to a Yes/No field in a form. This can be the result 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).
Top of Page