Access.2007.VBA.Bibl..

When all the contacts have been exported to the worksheet, a save name is constructed from the

template’s Title property and the current date, and displayed in an InputBox so it can be edited, if

desired; finally, the worksheet is saved with the save name and made visible.

The ExportContactsToExcelprocedure in this section can be run from the macro

mcrExportContactsToExcel.

The resulting worksheet is shown in Figure 7.7.

FIGURE 7.7

A minimally formatted worksheet filled with Access data.

Tabular Worksheets Formatted from Code ........................................................................

Many companies store data on customer or client accounts in an Access database and need to

export that data to Excel for further analysis or distribution. For example, an insurance company

might need to export data on the companies it insures, including the account number, account

type, policyholder, and account executive for use by their employees in the field. The

ExportAccountSummaryprocedure (listed as follows) exports this data, using a different

approach than the previous procedure. Instead of using a preformatted Excel template, all the

formatting and sizing is applied directly from VBA code, to an Excel 9 worksheet filled with Access

data by the TransferSpreadsheetmethod:

Public Function ExportAccountSummary()

Dim strWorksheet As String

Dim strWorksheetPath As String

Dim appExcel As Excel.Application

NOTENOTE

Working with Excel Worksheets 7