Simply Exporting Access Data to Excel..............................................................................
Just as in earlier Office versions, Access offers two ways to do a quick-and-dirty export of table or
query data to an Excel worksheet. You can use the Excel button in the Export group of the External
Data tab of the Ribbon to export Access data without worrying about formatting, for an Office
2007 user who just wants the data. If you need to create worksheets that can be opened and edited
by users running older versions of Office, or using a handheld device such as a BlackBerry, you can
use the TransferSpreadsheetmethod to export data, selecting the desired output worksheet
format. This can be useful when you work for an organization that has upgraded its software and
you need to send a worksheet with client contact information to a sales representative who has not
updated her laptop yet.For a quick export to the new .xlsx worksheet format, use the Excel button in the Export group of
the External Data tab of the new Access Ribbon, as shown in Figure 7.1.FIGURE 7.1
Exporting a table to an Excel worksheet from the Ribbon.Clicking the Excel button opens a dialog where you can browse for the location for saving the
worksheet. This dialog has an option for preserving the layout and formatting of the original
Access object, which (curiously) is only available if a table or query is selected (see Figure 7.2).Tables and queries don’t have much in the way of formatting and layout, but if you check the
“Export data with formatting and layout” checkbox your Excel worksheet will use the same font as
the table or query (though not the alternate row shading), and it will show data from a linked table
instead of the linking ID field. Figure 7.3 shows two worksheets made from the same table; the top
one displays the customers’ company name in the CustomerID column (picking it up from the
linked table) and uses the Calibri 11 font; the second worksheet displays the CustomerID in that
column and uses the Arial 10 font.Part II Writing VBA Code to Exchange Data between Office Components