Excel Annoyances

IMPORT AND EXPORT ANNOYANCES

MOVE DATA BACK AND FORTH FROM EXCEL TO ACCESS

The Annoyance:

I’ve tried copying data from an Excel worksheet and pasting it into an Access database table, but the paste operation created errors. Is there an easy way to transfer data from Excel to Access? And vice versa, from Access to Excel?

The Fix:

There are several ways to handle this. One of these options is to simply use the Get External Data feature in Access. This method will import your Excel data and either put it into a new Access table or append it to an existing table. Here’s how.

Moving data from Excel to Access

1. In Access, select File → Get External Data → Import.

2. In the “Files of type” drop down, select Microsoft Excel (*.xls) from the list.

3. Navigate to and select the Excel workbook that has your data, and click the Import button.

4. The Import Spreadsheet Wizard starts up (see Figure 6-20). In the Wizard you can select the worksheet or range that has your data. Additional pages in the Wizard ask whether the data has a header row, whether to place the data in a new table or an existing one, which fields (columns) to import or not, and so on. When you’ve made all the appropriate choices, click Finish.

Figure 6-21. Import Excel data into Access with a few clicks!.

An alternative way to transfer data from Excel to Access is to use Paste Append. This command is a special variation of the Paste ...