Exporting Data to Excel

Don't try to imitate Excel's powerful analyzing functions in Access. Instead, export your data into Excel and run your analysis there. In short, don't ever work harder than you need tolet Office do its job!

Sending data to Excel is one of the easiest Office collaborations. You just select a table or query, select Analyze It with MS Excel from the OfficeLinks button, and you're done. After your data is in Excel, you can use any Excel featureExcel doesn't care where the data came from.

Let's export the Plantings table from your gardening database to Excel. To do so, follow these steps:

1. Open the gardening database and display the Database window by pressing F11 . (Just ignore the switchboard form for now.)

2. Click the Tables shortcut in the Database window and select the Plantings table.

3. Select Export from the File menu.

4. In the Export Table 'Plantings' To dialog box, select Microsoft Excel 97-2002 (*.xls) from the Save As Type control. Access uses the table's name to name the new spreadsheet, but you can enter another name if you want. You can also specify a folder other than the default folder in which to store the new file. Don't change either of these default settings for this example. However, do check the Save Formatted option to the left of the Export button. Then, click Export to finish the job.

Figure 16.1 shows the new spreadsheet in Excel. As you can see, all your data retains its original formatting as a result of selecting the Save Formatted option. Notice also that Excel uses the field names as column headings.

Figure 16.1. Access easily exports data to Excel.

You can't see all the data onscreen, so just scroll to the right and you'll see that Excel even respects the hyperlink data in the More Info field. Only your pictures didn't make the trip. You're ready to start analyzing!

Now, let's export the Catalogs table and see how Excel handles it. Repeat steps 14 from the previous procedure. But this time, specify the Catalogs table and don't select the Save Formatted option. This time, there's no formatting and Excel doesn't know how to interpret all the data. Can you see the small triangles in the upper-left corner of several of the ZIP entries?

Select one of those fields, and Excel displays an error icon. Click the drop-down arrow to see the possible types of errors shown in Figure 16.2. In this case, there really isn't a problem because ZIP codes don't need to be stored as numbers . If you want, you can select the fields and select Ignore Error, or you can do nothing. Save this file in Excel using the name CatalogsInExcel.xls ; you'll use it in the next section.

Figure 16.2. Excel lets you know when it can't interpret Access data.