Microsoft Office Tutorials and References
In Depth Information
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
meaningless 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
manipulating 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.
Search JabSto ::




Custom Search