Access.2007.VBA.Bibl..

Because the workbook was created in an older format, you will see “(Compatibility

Mode)” in its title bar.

The procedure starts by running a make-table query to create a table for export to Excel, then cre-

ates a save name for the worksheet, and deletes the old worksheet file, if it exists. The data in the

table created by the make-table query is then exported to a new Excel worksheet, using the

TransferSpreadsheetmethod. The new worksheet is opened and activated, and various

ranges in the worksheet are formatted, applying the Calibri font, hairline borders, and appropriate

column widths for each column.

I like to give tables created by make-table queries the prefix tmak, with the same base

name as the query. This lets me know that a table was created by a make-table query, so

I know that if I want to change it, I need to modify the query, not the table.

Next, the procedure inserts blank rows at the top of the worksheet, and title text is inserted at the top;

these header lines are then formatted with a gray background and upper and lower lines. Several

print setup and margin settings are done next, and finally the worksheet is saved, with an InputBox

so you can modify the save name, if desired. The finished worksheet is shown in Figure 7.8.

FIGURE 7.8

An Excel worksheet formatted in VBA code.

TIPTIP

NOTENOTE

Working with Excel Worksheets 7