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.

Exchange Data Between Excel and Access

How to Exchange Data Between Microsoft Excel and Access

There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel. To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.

To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

The word IMPORT has two different meanings between Excel and Access. In Excel, the word import means to make a permanent connection to data that can be refreshed. In Access, the word import means to bring data into Access once, but without a data connection. You CANNOT save an Excel workbook as an Access database. Neither Excel nor Access provides functionality to create an Access database from Excel data.

Export Access Data to Excel

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or the many functions that are not available in Access. 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.

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, or form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use, and even schedule the export operation to run

Scenarios for Exporting Access Data to Excel

The following are common situations for exporting data from Access to Excel:

1. 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 when they need to, but if you would like to make this process more efficient, you might need to export date between the two office applications.
2. Another scenario may be that you are a long-time user of Access, but your manager prefers to view reports in Excel. At regular intervals, you do the work of copying the data into Excel, but you would like to automate this process to save yourself time, so you would need to export data.
   Note
1. To export data from Access to Excel, you must be working in Access. Excel does not provide any mechanism for importing data from an Access database. Again, you cannot save an Access database or table as an Excel workbook by using the Save As command of Access. You can only use the Save As command of Access to save an Access object in the current database as another Access database object.
2. You can export a table, query, or form. You can also export selected records in a view.
3. You cannot export macros, modules, or reports to Excel. When you export a form or datasheet that contains subforms or subdatasheets, only the main form or datasheet is exported. You must repeat the export operation for each subform and subdatasheet that you want to view in Excel.
4. You can only export one database object in a single export operation. However, you can merge the data in multiple worksheets in Excel after completing the individual export operations.