Page 171 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

Workbook Methods
Workbooks are the basic document in Excel, so it’s not surprising that there are a lot of meth-
ods you can invoke to take action in your workbooks. You can add a workbook to your list of Chapter 7
favorite files and URLs, activate a workbook so you can work with it without naming the
workbook in every command, or preview a workbook before you print it.

Activate Method

When you first start writing macros, it’s likely that you’ll work within a single workbook most
of the time. The simpler macros you create for tasks such as manipulating the values in a
worksheet or backing up a workbook by saving a copy of the file using the SaveCopyAs
method all use properties and methods within the active workbook. When you want to start
working on several workbooks from within the same macro, however, you need to let the
VBA engine know that you’re changing gears. To do so, you use the Workbook object’s Acti­
vate method. As an example, the following code snippet would make the workbook named
Q42003Sales.xls the active workbook:

Workbooks ("Q42003Sales.xls").Activate

Caution It’s important to remember that the ThisWorkbook property and the ActiveWorkbook
property might refer to different workbooks. ThisWorkbook always refers to the workbook that
contains the module with the code you’re executing, but the file referred to by the ActiveWork­
book property can be changed.

Once you change the active workbook, every method you call using the ActiveWorkbook
property will affect the active workbook. For example, if you are working with five work-
books but have just made changes to two of them, you could activate the workbooks in turn,
save them, and return to the original workbook, as in the following procedure:

Sub SaveAfterChange()
Workbooks("Q4SalesSummary.xls").Activate
ActiveWorkbook.Save
Workbooks("2004SalesSummary.xls").Activate
ActiveWorkbook.Save
Workbooks("Q42004Sales.xls").Activate
End Sub

AddToFavorites Method
Earlier in this chapter you saw how setting the AddToMru property caused the name of a
saved file to be added to the recently used file list at the bottom of the File menu. You can also
add a file to the list of Favorites that shows up in Internet Explorer, My Computer (as shown
in Figure 7-4), or on the Web toolbar in Excel.

145