(This is section 3 of 4 in this chapter)
When it comes to analytical capabilities, Access is somewhat lacking. If you really want to analyze numerical data in your database or create a top-notch chart, you need to use a spreadsheet program. This is where Excel comes in handy. Excel lets you analyze your data in ways it is impossible to do in Access. Recognizing that fact, Microsoft made sure you can easily use your Access data with Excel.
The following sections detail how you can use your Access data in an Excel workbook.
Access makes it very easy to transfer an entire table to Excel. To do this, open a database such as Customer.mdb, which you created earlier in the book. To then export a file to Excel, follow these steps:
Access will transfer the information in the selected table or query into an Excel file format. Excel will then automatically start and load the new file (see Figure 11-7). This is the only step you need to take in Access; everything else happens in Excel.
Notice that the file uses a name (Business Customers.xls) derived from the name of your Access table. The name of the table was Business Customers; the name of the Excel file is Business Customers.xls.
Sometimes, you will want to transfer only part of a table or query. For example, you may want to export only records 5 through 13 of the Business Customers table. To export only part of a table or query, perform these steps:
Notice that the steps are essentially the same as those you used when transferring an entire table. The only difference is that you select a portion of a table or query, instead of the whole thing. Access and Excel take care of the rest, transferring the desired data and displaying it in Excel.
So far you have learned how to output information from Access to Excel. There may be times when you want to establish an actual link between information in Access and Excel. Links are established using OLE (object linking and embedding), a standard for sharing information among Windows programs.
The easiest way to establish a link between Excel and Access is to use Windows cut-and-paste capabilities. To establish a link between Access and Excel, perform these steps:
Access pastes the information in the Clipboard into the Excel spreadsheet and establishes a link between the information and the original table or query. Your screen will look similar to what is shown in Figure 11-9.
Data Formats When Pasting in Excel
When you paste your Access information in Excel, you may have noticed that there are a number of different data formats you can use (see Figure 11-8). Excel offers these choices when pasting Access information:
Biff5. This is the Microsoft Excel Binary Interchange File Format, version 5. (That is a mouthful, isn't it?) This format allows information to appear as close to the original format as possible.
HTML. Excel pastes information in HTML format, which is the native format of the Web.
Unicode Text. The text of the original information is pasted using Unicode (two-byte) characters. All other attributes (formats, appearance, etc.) are left up to Excel. You would use this format if the original information contains foreign language symbols.
Text. Excel pastes the text of the original information. All other attributes (formats, appearance, etc.) are left up to Excel.
CSV. Excel interprets the incoming data as "comma-separated values," meaning that it tries to break fields based on locations of commas in the text. This is not necessarily a good format to use when pasting information from other Microsoft Office programs.
The format you choose should be based on how you want your information to ultimately appear in Excel.
Regardless of whether you are transferring an entire table or query or only part of one, Access transfers more than just raw data. Access also transfers the following formatting information to Excel:
However, if you use the Clipboard method of linking information and you choose a text-based data format, Access transfers only the data.
Access also transfers a header row which it may format with shaded text (refer back to Figure 11-7). Access provides this row for information purposes only; if you created an output file as discussed in the previous section, Transferring Data to Excel, you can safely delete the row. If you established a link between Excel and Access and there is a header row, you cannot delete it.
If you simply output Access information into an Excel file format, there is no relationship between the Excel data and what is in Access. In this instance, whenever there is a change in the original Access information, you will need to again output it to an Excel file.
If you establish a link between Excel and Access, the information in Excel is updated automatically as you change the Access data. Specifically, Excel updates the information when you open the Excel spreadsheet.
You can also update the information manually by following these steps: