Workbook Object

The Workbook object represents an entire workbook loaded into Excel. The default workbook is Book1 . It is one level down from the Application object.

The Workbook object forms a part of a Workbooks collection, which represents all the workbooks currently loaded into Excel. You cannot reference the Workbook object directly; you must access it through the Workbooks collection. See the example in the next section for how to do this.

Main Properties, Methods , and Collections

These are the main properties, methods, and collections you will use within the Workbook object.

Activate

This method activates the workbook specified in the Collection object. It will automatically go to the active sheet within that workbook. This is the same as selecting Window from the Excel menu and clicking a workbook. However, a window does not necessarily equal a workbook. See the section ‚“ Windows Object, ‚½ later in this chapter, for more details.

 Workbooks("book1").Activate 

ActiveSheet

This property references the active sheet within a particular workbook. You may remember that ActiveSheet was also mentioned in relation to the Application object, but in that case it refers to the active sheet anywhere within the application. If several workbooks are loaded, it refers to the last sheet that was given the focus.

The Workbook object allows you to fine-tune your selection and to specify which workbook you want to see the active sheet in:

 MsgBox Workbooks("book1").ActiveSheet.Name 

This returns ‚“Sheet1 ‚½ if this is the active sheet.

Close

This method closes the workbook just as when you select File Close from the Excel menu. There are optional parameters to save the file, give it a different filename, and route the workbook:

 Workbooks("book1").Close (True,"C:\Myfile.xls") 

HasPassword

This property returns True or False, depending on whether the workbook is protected by a password. For obvious reasons, it is read-only!

 MsgBox Workbooks("book1").HasPassword 

PrintOut

This method prints out the active sheet of the referenced workbook to the current printer:

 Workbooks("book1").PrintOut 

PrintPreview

This method provides a print preview on the active sheet of the referenced workbook:

 Workbooks("book1").PrintPreview 

ReadOnly

This property returns True or False on whether the workbook is read-only. This could be quite important if your code changes spreadsheet cells , because the workbook would then need to be saved under a different name.

 MsgBox Workbooks("book1").ReadOnly 

Save and SaveAs

These methods save your workbook, normally before it is closed. You can save to the same name or you can use the SaveAs method to save to a different filename. Save will overwrite the previous version; SaveAs will create a new version.

 Workbooks("book1").Save 
 Workbooks("book3").SaveAs "C:\Myfile.xls" 

Saved

This property returns True or False, depending on whether the workbook has been saved pending any changes the user has entered. If the workbook has been saved and no further changes have been made, this will display True. If changes are then made, it will display False.

 MsgBox Workbooks("book1").Saved 

Sheets

Sheets work exactly the same way as the Sheets collection described in the ‚“Application Object ‚½ section in this chapter.

Windows

This collection represents all the windows within the Workbook object. See the ‚“Windows Object ‚½ section later in this chapter.

Worksheets

This collection represents all the worksheets within the Workbook object. See the ‚“Worksheet Object ‚½ section later in this chapter.