Move Access Data to Excel

Many Microsoft Office users prefer working in Excel instead of Access. Sometimes the reasons are excellent (for example, calculations are often more easily and robustly performed in Excel). Sometimes the reasons are less admirable (users don't want to learn Access).

Regardless, if your boss or client wants to see data in Excel, you'll want to deliver it in XLS format.

Copy and Paste

A plain old copy-and-paste inserts field names in the first Excel row and shows the data in each cell. Try the following example.

1.	Open the tblCustomers table and select the first four records.
2.	Right-click and choose Copy.
3.	Open Excel. In a new blank workbook, choose Edit, Paste. Excel pastes the records and includes the field names in the first row, as shown in Figure 13.10. (I've used the Format, Row and Format, Column commands to clean up the paste a bit.) Figure 13.10. Excel pasted the records beginning in the first row. You can also click a cell and Excel will begin the paste there.

If you have Smart Tags turned on, you will see tiny green triangles in the upper-left corner of each cell in the ZIP column. Excel regards this ZIP column with suspicion. If these are all five-digit numbers, why have they been formatted as text?

You can open the drop-down list beside the Smart Tag to deal with this issue (see Figure 13.10). You can also avoid the problem entirely by choosing among the options in the Paste Special dialog box, as follows:

1.	Click the Sheet2 tab for a new worksheet.
2.	Choose Edit, Paste Special. Most of these choices you've seen in the Paste Special dialog box in Word. The others include the Comma Separated Value (CSV) file format and Binary File Format (BIFF5).
3.	Select Text and click OK. The records are pasted as plain text without formatting.
4.	Save the file as PasteExcel.xls and close it. Close the tblCustomers table.

NOTE

You can copy and paste whole tables into Excel. The procedures are the same, except that you choose the table in the Database window.

Office Links

You can use the Office Links command to create and open a file of Access data in Excel. Try this exercise:

1.	In the Database window, select the tblOrderDetails table.
2.	Choose Tools, Office Links, Analyze It with Microsoft Office Excel.
3.	Access creates a new file with the table name and an XLS extension.
4.	Close the file and close Excel.

NOTE

If you want to delete this file or others created by Office Links commands, by default, the files are placed in your My Documents folder.

Export Command

You can also use an Export wizard to export data to Excel.

1.	In the Database window, choose the tblOrders table.
2.	Select File, Export.
3.	In the Export Table dialog box, open the Save As Type drop-down list and choose Microsoft Excel 97-2003 (*.xls).
4.	ClickExport. Access exports the table, using the first row for field names.

TIP

You might want to import from Excel instead of exporting from Access, especially if you have strong Excel skills. The Import External Data command in Excel, available from the Data menu, has numerous options and is a more robust tool than the Access Export feature.