Manipulating Workbook Objects
Workbook objects appear directly below the Application object in Excel's object hierarchy. You can use VBA to create new workbooks, open or delete existing workbooks, save and close open workbooks, and much more. The next section takes you through various techniques for specifying workbooks in your VBA code; then you'll look at some Workbook object properties, methods, and events.
Specifying a Workbook Object
If you need to perform some action on a workbook, or if you need to work with an object contained in a specific workbook (such as a worksheet), you need to tell Excel which workbook you want to use. VBA gives you no fewer than three ways to do this:
Use the Workbooks object?
The Workbooks object is the collection of all the open workbook files. To specify a workbook, either use its index number (where 1 represents the first workbook opened) or enclose the workbook name in quotation marks. For example, if the Budget.xls workbook was the first workbook opened, the following two statements would be equivalent:
Use the ActiveWorkbook object?
The ActiveWorkbook object represents the workbook that currently has the focus.
Use the ThisWorkbook object
The ThisWorkbook object represents the work book where the VBA code is executing. If your code only deals with objects residing in the same workbook as the code itself, you can use the ActiveWorkbook object. However, if your code deals with other workbooks, use ThisWorkbook whenever you need to make sure that the code affects only the workbook containing the procedure.
Opening a Workbook
To open a workbook file, use the Open method of the Workbooks collection. The Open method has a dozen arguments you can use to fine-tune your workbook openings, but only one of these is mandatory. Here's the simplified syntax showing the one required argument (for the rest of the arguments, look up the Open method in the VBA Help system):
The full name of the workbook file, including the drive and folder that contain the file.
For example, to open a workbook named Data.xls in the C:\My Documents folder, you would use the following statement:
Workbooks.Open "C:\My Documents\Data.xls"
Creating a New Workbook
If you need to create a new workbook, use the Workbooks collection's Add method:
Template is an optional argument that determines how the workbook is created. If Template is a string specifying an Excel file, VBA uses the file as a template for the new workbook. You also can specify one of the following constants:
Creates a workbook with a single worksheet.
Creates a workbook with a single chart sheet.
Here's a sample statement that uses the Add method to open a new workbook based on Excel's Invoice.xlt template file:
Workbooks.Add "C:\Program Files\Microsoft Office" & _
Workbook Object Properties
Here's a rundown of some common properties associated with Workbook objects:
A new, unsaved workbook's Path property returns an empty string ("").
Returns a Worksheet object that represents the worksheet in Workbook that currently has the focus.
Returns the full pathname of the Workbook. The full pathname includes the workbook's path (the drive and folder in which the file resides) and the filename.
Returns the filename of the Workbook.
Returns the path of the Workbook file. Note, however, that there is no trailing backslash (\) in the returned value (for example, C:\My Documents).
Determines whether or not changes have been made to the Workbook since it was last saved. If changes have been made, Saved returns False.
Workbook Object Methods
Workbook objects have dozens of methods that let you do everything from saving a workbook to closing a workbook. Here are a few methods that you'll use most often:
Activates the specified open Workbook. For example, the following statement activates the Finances.xls workbook:
Closes the specified Workbook. This method uses the following syntax:
Workbook.Close(SaveChanges, FileName, RouteWorkbook)
The Workbook object you want to close.
If the workbook has been modified, this argument determines whether or not Excel saves those changes:
Saves changes before closing.
Doesn't save changes.
Asks the user if she wants to save changes.
Save the workbook under this filename.
Routes the workbook according to the following values:
Sends the workbook to the next recipient.
Doesn't send the workbook.
Asks the user if she wants to send the workbook.
Prints the specified Workbook using the following syntax:
Workbook.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate,
The Workbook object you want to print.
The page number from which to start printing.
The page number of the last page to print.
The number of copies to print. The default value is 1.
If True, Excel displays the Print Preview window before printing. The default value is False.
Specifies the printer to use.
If True, Excel prints the workbook to a file and prompts the user for a filename.
If True, and Copies is greater than 1, Excel collates the copies.
The name of the file to which you want to print (PrintToFile must be True).
Displays the specified Workbook in the Print Preview window.
Saves the specified Workbook. If the workbook is new, use the SaveAs method instead.
Saves the specified Workbook to a different file. Here's the simplified syntax for the SaveAs method (to see all 11 arguments in the full syntax, look up the SaveAs method in the VBA Help system):
The Workbook object you want to save to a different file.
The full name of the new workbook file, including the drive and folder where you want the file to reside.