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.