Manipulating Workbook Objects





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:

Workbooks(1)
Workbooks("Budget.xls")

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):

Workbooks.Open(FileName)

FileName

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:

Workbooks.Add(Template)

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:

xlWBATWorksheet

Creates a workbook with a single worksheet.

xlWBATChart

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" & _
    "\Templates\Spreadsheet Solutions\Invoice.xlt"

Workbook Object Properties

Here's a rundown of some common properties associated with Workbook objects:

graphics/note_icon.gif

A new, unsaved workbook's Path property returns an empty string ("").


Workbook.ActiveSheet? Returns a Worksheet object that represents the worksheet in Workbook that currently has the focus.

Workbook.FullName? 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.

Workbook.Name? Returns the filename of the Workbook.

Workbook.Path? Returns the path of the Workbook file. Note, however, that there is no trailing backslash (\) in the returned value (for example, C:\My Documents).

Workbook.Saved? 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:

Workbook.Activate? Activates the specified open Workbook. For example, the following statement activates the Finances.xls workbook:

Workbooks("Finances.xls").Activate

Workbook.Close? Closes the specified Workbook. This method uses the following syntax:

Workbook.Close(SaveChanges, FileName, RouteWorkbook)

Workbook

The Workbook object you want to close.

SaveChanges

If the workbook has been modified, this argument determines whether or not Excel saves those changes:

 

SaveChanges

Action

 

True

Saves changes before closing.

 

False

Doesn't save changes.

 

Omitted

Asks the user if she wants to save changes.

FileName

Save the workbook under this filename.

RouteWorkbook

Routes the workbook according to the following values:

 

RouteWorkbook

Action

 

True

Sends the workbook to the next recipient.

 

False

Doesn't send the workbook.

 

Omitted

Asks the user if she wants to send the workbook.

Workbook.PrintOut? Prints the specified Workbook using the following syntax:

Workbook.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate,
graphics/ccc.gif PrToFileName)

Workbook

The Workbook object you want to print.

From

The page number from which to start printing.

To

The page number of the last page to print.

Copies

The number of copies to print. The default value is 1.

Preview

If True, Excel displays the Print Preview window before printing. The default value is False.

ActivePrinter

Specifies the printer to use.

PrintToFile

If True, Excel prints the workbook to a file and prompts the user for a filename.

Collate

If True, and Copies is greater than 1, Excel collates the copies.

PrToFileName

The name of the file to which you want to print (PrintToFile must be True).

Workbook.PrintPreview? Displays the specified Workbook in the Print Preview window.

Workbook.Save? Saves the specified Workbook. If the workbook is new, use the SaveAs method instead.

Workbook.SaveAs? 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):

Workbook.SaveAs(FileName)

Workbook

The Workbook object you want to save to a different file.

FileName

The full name of the new workbook file, including the drive and folder where you want the file to reside.


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows