Microsoft Office Tutorials and References
In Depth Information
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:
If you want to close another workbook from within a procedure, you can use this type of
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:
Dim Wkbk as Workbook
For Each Wkbk in Workbooks
If Wkbk.Name <> ThisWorkbook.Name Then
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.
The procedure does display a Save As dialog box if any of the open workbooks
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.