Practical Export of Data Between Access and Excel

Operation to Export Data Between Access and Excel

In my previous post I showed the several ways to exchange data between Microsoft Access and Microsoft Excel. whether to bring data into Excel from Access, to bring data into Access from Excel; you can take the following steps to accomplish it.

1. If you are working in Microsoft Access and want to export data to Microsoft Excel, open the source database.
2. In the Navigation Pane, select the object that contains the data that you want to export. (It can be a table, a query, or a form).
3. Review the source data to ensure 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).
4. If the source object is a table or a query, decide whether you want to export the data with or without its formatting.
5. Choose the destination workbook and file format.
6. During the export operation, Access prompts you to specify the name of the destination workbook. The workbook is then created if it does not exist. But if it exists, it is overwritten if the data, including the formatting is exported, otherwise; if the data, but not the formatting is exported, the workbook is not overwritten, instead a new worksheet is added to the workbook, and is given the name of the object from which the data is being exported.
7. If the destination Excel workbook is open, close it before you continue.

Export the Data

8. In the source database, if you want to export only a portion of a table, query, or form, open the object and select the records you want.


9. On the External Data tab, in the Export group, click Excel.
10. In the Export - Excel Spreadsheet dialog box, accept the suggested name for the Excel workbook or enter another name.
11. In the File Format box, select the file format that you want.
12. If you are exporting a table or a query, and you want to export formatted data, select 'Export data with formatting and layout'.
13. 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.
14. 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. However, if you want to export all of the records displayed in the view, leave this check box cleared.

View that dialog box below.

15. Click Ok.

If the export operation fails due to an error, Access displays a message that describes the cause of the error. Otherwise, Access exports the data to 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

1. Click Yes if you want to save the details of the export operation for future use. This helps you repeat the same export operation in the future without having to go through the entire steps in the wizard each time.
2. In the Save as box, type a name for the export specification.
3. If you want to perform the operation at fixed intervals (such as weekly or monthly), select the Create Outlook Task check box.
4. Click Save Export.

If Outlook is installed, Access starts it immediately. This creates a Microsoft Office Outlook 2007 task that lets you run the specification by clicking a button.

5. In Outlook, review and modify the task settings (such as Due date and Reminder).
6. To make the task recur, click Recurrence. This dialog box below shows the task scheduler with some typical settings.
7. Click Save and Close.

Run the Saved Task

1. In the Outlook Navigation Pane, click Tasks, and then double-click the task that you want to run.
2. On the Task tab, in the Microsoft Office Access group, click Run Import.
3. In Excel, open the destination workbook, and then verify that all of the data were copied into the correct cells.