Access.2007.VBA.Bibl..

FIGURE 7.4

An Access table exported to an Excel 97-2003 worksheet.

The Excel Object Model ....................................................................................................

The two export options described in the previous section are fine for creating a simple, minimally

formatted or unformatted worksheet filled with data from an Access table or query, but if you need

to create fully formatted worksheets, such as personnel forms, timesheets, sales reports, factory

production data reports, and so forth, you will need to work with the Excel object model to create

worksheets using Automation code, fill them with Access data, and then apply formatting, using

components of the Excel object model.

The Excel Export.accdb sample database contains the tables, queries, forms, and code

used in this chapter.

The CreateObjectand GetObjectfunctions are used to either create a new Excel object, or

set a reference to an existing instance of Excel. Using GetObjectto retrieve a reference to an

existing workbook avoids creating extra instances of Excel, which uses up system resources. To

open a worksheet within a workbook, first set a reference to the Workbook object, then add a new

workbook to the Workbooks collection. By default, the new workbook will have three worksheets.

The CreateNewWorkbookprocedure creates a new, blank workbook from the default Excel

template, with three worksheets:

Public Function CreateNewWorkbook ()

On Error GoTo ErrorHandler

Dim appExcel As Excel.Application

Dim bks As Excel.Workbooks

Set appExcel = GetObject(, “Excel.Application”)

Set bks = appExcel.Workbooks

NOTENOTE

Working with Excel Worksheets 7