Page 163 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

The properties you’ll probably use the most in your work are FileName and AddToMru, with
FileFormat and Password in the running for third place in your heart. The AddToMru property,
which puts a file on the recently used files list that appears on the File menu, might seem to Chapter 7
be an odd choice, but you can use that property to remind yourself which workbooks you
need to work with the next time you or one of your colleagues run Excel. For example, if you
wrote a macro that updated the values in a series of workbooks that would later need to be
reviewed by the president of your company, adding the names of the updated workbooks to the
most recently used files list would make it easy for your boss to find the files that need a look.

If you want to save every open workbook, you can write a macro to do just that using a For
Each…Next loop, as in the following procedure:

Sub SaveThemAll()
Dim Wkbk as Workbook
For Each Wkbk in Workbooks
If Wkbk.Path <> "" Then Wkbk.Save
Next Wkbk
End Sub
The If…Then statement in the For Each…Next loop checks whether a workbook has an
undefined path, meaning that the workbook in question has never been saved. This check is
important if you want the procedure to run without human intervention; if someone needs
to be on hand to save the workbooks, you might as well save them manually.
A related method that’s available for use with workbooks is the SaveCopyAs method, which
saves a copy of the current workbook under a new name. The SaveCopyAs method is a great
way to make backup copies of a workbook during a lengthy procedure to guard against data
loss, and as part of a general backup and file maintenance policy. The syntax of the procedure
is simply this:

ThisWorkbook.SaveCopyAs "path\filename.xls"

Warning If you use the SaveCopyAs method to save a file using an existing file name, the
macro will overwrite the existing file without asking permission.

Activating Workbooks
In the Save and SaveAs property discussions, you noticed that the code used the ThisWork­
book object, which refers to the workbook to which the VBA code is attached. The Active-
Workbook object is related to the ThisWorkbook object in that it refers to a workbook
(obviously), but it refers to the workbook you have chosen to act on, not the workbook to
which the code is attached. You can change the active workbook by calling the Workbooks col-
lection’s Activate method, as in the following line of code:

Workbooks("2004Q3sales.xls").Activate

137