Access.2007.VBA.Bibl..

To open the Object Browser for examining components of an object model, open the

Visual Basic window and select Object Browser from the View menu, or press F2.

Formatting Excel Worksheets in VBA Code ........................................................................

If you need to sort, group, indent, or otherwise format exported data in an Excel worksheet, or cre-

ate a total under the last row of data, you can write VBA code to use Excel commands to do the

work in code. You can apply formatting to a worksheet created by the TransferSpreadsheet

method, or one created from the Ribbon command, or a worksheet created programmatically from

a template.

See Chapter 7 for examples of creating worksheets using the TransferSpreadsheet

method.

In this section, data from qryOrdersAndDetails is exported to a new worksheet made from a tem-

plate and is then formatted in code. For convenience, the ExportNorthwindDataprocedure

can be run from the macro mcrExportNorthwindData.

The procedure starts by creating a new worksheet from a template (Northwind Orders.xltx), as for

the ArchiveDataprocedure. Data from the query qryOrdersAndDetailsis written to rows

in the worksheet, and then a set of Excel commands is used to apply hairline borders to the data

area, and a double bottom border to the column headings row.

Next, the worksheet’s data area is sorted by the first two columns (Country and Category), and the

extra values are removed (the effect is similar to turning on Hide Duplicates in an Access report).

Finally, a Grand Total is created under the last row, made large and bold, and enclosed in a box.

The procedure is listed as follows:

Public Sub ExportNorthwindData()

On Error GoTo ErrorHandler

Dim appExcel As Object

Dim i As Integer

Dim lngCount As Long

Dim lngCurrentRow As Long

Dim lngRows As Long

Dim n As Long

Dim objFind As Object

Dim rng As Excel.Range

Dim rngData As Excel.Range

Dim rngStart As Excel.Range

Dim strCategory As String

Dim strCountry As String

Dim strCurrAddress As String

Dim strDBPath As String

Dim strFormula As String

CROSS-REFCROSS-REF

NOTENOTE

Part I The Office Components and What They Do Best