Page 164 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Closing Workbooks
When you’re done changing a workbook, it’s a good idea to close it, both to save system
resources and to reduce the likelihood that something will happen to the file while it’s open.
After all, all it takes is a stray keystroke here or there and the best data can become a mean-
Chapter 7
ingless jumble. As with the Save and SaveAs techniques you saw earlier in this section, you
can use the ThisWorkbook property of the Application object to invoke the Close method, as in
the following statement:

ThisWorkbook.Close
If you want to close another workbook from within a procedure, you can use this type of
statement:

Workbooks("name").Close

When you’ve reached the end of a macro and want to clean up by saving and closing every
open workbook, you can use the following procedure:

Sub CloseAll()
Dim Wkbk as Workbook
For Each Wkbk in Workbooks
If Wkbk.Name <> ThisWorkbook.Name Then
Wkbk.Close SaveChanges:=True
End If
Next Wkbk
ThisWorkbook.Close SaveChanges:=True
End Sub
This procedure checks each workbook to ensure it isn’t the workbook containing the VBA
code. If the code were to close its own workbook while any other workbooks were open, any
remaining open workbooks wouldn’t be affected because the code would stop running.

Caution The procedure does display a Save As dialog box if any of the open workbooks
are new.

Workbook Properties

Even though workbooks are the focal point of Excel, you’ll actually spend less time manipu-
lating workbooks than working with worksheets and cell ranges. Even so, there are a number of
workbook properties you can use to help you and your colleagues work with Excel effectively.

138