Exchange (copy, import, export) data between 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.
Notes:
- The word "import" has two different meanings between Excel and Access. In Excel, when you import, you make a permanent connection to data that can be refreshed. In Access, when you import, you bring data into Access once, but without a permanent 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.

What do you want to do?

Work with Access data in 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.

Copy Access data into Excel

From Access, you can copy data from a datasheet view and then paste the data into an Excel worksheet.

Start Access, and then open the table, query, or form that contains the records that you want to copy.
On the Home tab, click View, and then click Datasheet View.
Select the records that you want to copy.

If you want to select specific columns, drag across adjacent column headings.
On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut You can also press CTRL+C.
Start Excel, and then open the worksheet that you want to paste the data into.
Click in the upper-left corner of the worksheet area where you want the first field name to appear.

To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell that you click.
On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut You can also press CTRL+V.

Top of Page

Export Access data to Excel

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 automatically at specified intervals.

The following are common scenarios for exporting data from Access 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 when they need to, but you would like to make this process more efficient.
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.

For more information about exporting data from Access to Excel, see the Access Help system.

Top of Page

Connect to Access data from Excel

To bring Access data that can be refreshed into Excel, you can create a connection, often stored in an Office Data Connection file (.odc), to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data instead of importing it is that you can periodically analyze this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically refresh (or update) your Excel workbooks from the original Access database whenever the database is updated with new information. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

Click the cell where you want to put the data from the Access database.
On the Data tab, in the Get External Data group, click From Access.
In the Look in list, locate and double-click the Access database that you want to import.

In the Select Table dialog box, click the table or query that you want to import, and then click OK.
In the Import Data dialog box, do the following:
- Under Select how you want to view this data, do one of the following:
  - To view the data as a table, select Table.
  - To view the data as a PivotTable report , select PivotTable report.
  - To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report.
- Optionally, click Properties to set refresh, formatting, and layout options for the imported data, and then click OK.
- Under Where do you want to put the data? do one of the following:
  - To return the data to the location that you selected, click Existing worksheet.
  - To return the data to the upper-left corner of the new worksheet, click New worksheet.
Click OK.

Excel puts the external data range in the location that you specify.

For more information about connecting to data see, Connect to (Import) external data.

Top of Page

Work with Excel data in Access

You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users might find particularly useful for their Excel data:

Reports If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports.
Forms If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or view a lengthy row of data more easily on one screen.

For more information about working with Access forms and reports, see the Access Help system.

Top of Page

Copy Excel data into Access

From Excel, you can copy data in a worksheet view and then paste the data into an Access datasheet.

Note: If you paste data from multiple fields in a worksheet to a datasheet, make sure that the columns match the order of the data that you want to copy. When you paste data from multiple fields to a form, Access pastes the data into fields with the same name as the source fields, regardless of their order on the form. If the data that you want to copy contains fields that don't exist in the destination form, Access asks you if you want to paste only fields with matching names. If there are no matching field names, Access pastes the fields according to the destination form's tab order, and this might not be the order that you want. If the field names in the source are different from the field names in the destination, you might want to paste the data into a datasheet instead of a form.

Start Excel, and then open the worksheet that contains the data that you want to copy.
Select the rows that you want to copy.
On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut You can also press CTRL+C.
Start Access, and then open the table, query, or form in which you want to paste the rows.
On the Datasheet tab, in the Views group, click View, and then click Datasheet View.
Do one of the following:
- To replace records, select those records, and then on the Home tab, in the Clipboard group, click Paste .
  
  Keyboard shortcut You can also press CTRL+V.
- To append the data as new records, on the Home tab, in the Clipboard group, click Paste Append on the Edit menu.

Top of Page

Import Excel data into Access

To store data from Excel in an Access database, and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

The following are common scenarios for importing Excel data into Access:

You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases.
Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them.
You use Access to manage your data, but the weekly reports that you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.

For more information about importing data from Excel to Access, see the Access Help system.

Top of Page

Link to Excel data from Access

You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want it to be available from within Access. You create this type of link from within the Access database, not from Excel.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file.

The following are common scenarios for linking to an Excel worksheet from within Access:

You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access.
Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don't want to maintain copies of external data, but want to be able to work with it in Access.

For more information about linking data from Access to Excel, see the Access Help system.

Top of Page

Microsoft Office Tutorials

Thursday, July 6, 2017