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 CreateObject and GetObject functions are used to either create a new Excel object, or set a reference to an existing instance of Excel. Using GetObject to 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 CreateNewWorkbook procedure 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

Create and open a new, blank workbook:

bks.Add Make the workbook visible:

appExcel.Application.Visible = True

ErrorHandlerExit: Exit Sub

ErrorHandler:

Excel is not running; open Excel with CreateObject:

Set appExcel = CreateObject("Excel.Application") Resume Next Else

MsgBox "Error No: " & Err.Number & "; Description: " & _

Err.Description

Resume ErrorHandlerExit End If

End Sub

The procedures in this section can be run from macros; each procedure has a macro whose name is mcr plus the procedure name.

The error handler in the preceding procedure is similar to the one used in the Word Automation code in Chapter 6; sets a reference to the current Excel instance if Excel is running and otherwise creates a new Excel Application instance using the CreateObject function from a line in the procedure's error handler. If you run the CreateNewWorkbook procedure repeatedly, each time it runs a new workbook is created within the Excel window, named Book1, Book2, and so forth, as shown in Figure 7.5.

FIGURE 7.5

Three workbooks in the Excel window.

FIGURE 7.5

Three workbooks in the Excel window.

To open a specific saved workbook, use a procedure like the one listed next, which opens a saved workbook using the Worksheets collection's Open method and the file name and path:

Public Function OpenSpecificWorkbook()

On Error GoTo ErrorHandler

Dim appExcel As Excel.Application Dim bks As Excel.Workbooks Dim sht As Excel.Worksheet Dim strWorkbook As String

Set appExcel = GetObject(, "Excel.Application") strPrompt = "Enter path and title of workbook" strTitle = "Workbook Name"

strDefault = "D:\Documents\tblContacts2.xls"

strWorkbook = InputBox(prompt:=strPrompt, _

Title:=strTitle, Default:=strDefault) appExcel.Workbooks.Open (strWorkbook) Set sht = appExcel.ActiveWorkbook.Sheets(l)

sht.Activate appExcel.Application.Visible = True

ErrorHandlerExit: Exit Sub

ErrorHandler:

Excel is not running; open Excel with CreateObject:

Set appExcel = CreateObject("Excel.Application") Resume Next Else

MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description

Resume ErrorHandlerExit End If

End Sub

If Excel is open, the workbook will be opened in the same window, as shown in Figure 7.6.

FIGURE 7.6

Opening a saved workbook in an existing Excel window.

City

Seattle

3 2 NorthwiridTraders Janet Leverling Janet 722 Moss Bay Blvd. Kirkland

4 3 NorthwindTraders Andrew Fuller Andrew 90S W, Capital Way Tacoma

5 1 4 CYMUS Corporation Margaret Peacock Margaret 4110 Old Redmond Rd. Redrnon

6 5 Lakeside Shipping Steven Buchanan Steven 14 Garrett Hilf London

1« 4

► n tbIContacts

13

12

107

Theodore

Ted

5997 27th Blvd.BIdg. 82

Bitterwater

KS

14

13

11F

Marian

Yates

Marian

8535 Skyline Blvd. Suite 390

Springfield

NJ

in

14

fi

Sean

Christ ensen

Sean

12837 Cherry Ln.

Easton

LA

16

15

139

Aaron

Buckman

Aaron

1383 42nd Terr. Suite 325

Maple Lake

ID

17

16

97

Sheryl

Gleason

Sheryl

10139 38th Ln.

Springfield

LA

17

112

Melinda

Geary

Melinda

3037 Archer Blvd.Bldg. 479

Bitterwater

NH

19

18

Hh

Molly

Thompson

Molly

13DG5 Juniper Ln.

New Britain

NH

2G

19

137

Harriette

Fields

Harriette

11972 Highland WayRoom 976

Clearwater

Seattle

If Excel is not running, the workbook will open in a new Excel window.

As with exporting Access data to Word, when writing Automation code to work with Excel, you only need a few components of the Excel object model: Workbooks, Worksheets, the Range object, Rows, and Columns. These objects are used in the more complex Excel Automation procedures in the following sections.

0 0

Post a comment