Working with the Workbook Object

The Workbook object represents an open workbook in Excel. The workbook has a Name property that returns the name of the workbook as a string (for example "book1.xls"). If the workbook has not yet been saved, this property returns the temporary name of the document, typically Book1. This name can be passed to get_Item on the Workbooks collection to access the workbook by name from that collection. Workbook also has a FullName property that returns the full filename of the workbook if the workbook has been saved (for example, "c:my documents ook1.xls"). For a new unsaved workbook, it returns the default name Excel gave to the workbook, such as Book1.

Properties That Return Active or Selected Objects

The Workbook object has a number of properties that return active objectsobjects representing things that are selected within the Excel workbook. Table 5-4 shows two of these properties.

Table 5-4. Workbook Properties That Return Active Objects

Property Name	Type	What It Does
ActiveChart	Chart	Returns the currently selected chart sheet in the workbook. If the currently selected sheet is not a chart sheet, this property returns null.
ActiveSheet	object	Returns the currently selected sheet in the workbook, which can be either a worksheet or a chart sheet. You can cast this to either a Worksheet or a Chart.

 

Properties That Return Important Collections

The Workbook object has a number of properties that return collections that you will frequently use. Table 5-5 shows some of these properties

Table 5-5. Workbook Properties That Return Important Collections

Property Name	Type	What It Does
Charts	Charts	Returns the Charts collection, which contains all the chart sheets in the workbook. The Charts collection has methods and properties to access a particular chart or to add a new chart sheet.
Sheets	Sheets	Returns the Sheets collection, which contains all the sheets in the workbook (both worksheets and chart sheets). The Sheets collection has methods and properties to access a particular sheet or to add a new sheet.
Windows	Windows	Returns the Windows collection, which contains all the open windows that are showing the workbook. The Windows collection has methods and properties to arrange and access windows.
Worksheets	Sheets	Returns the Worksheets collection, which contains all the worksheets in the workbook in a Sheets collection. The Worksheets collection has methods and properties to access a particular worksheet or to add a new worksheet.

 

Accessing Document Properties

Workbook has a BuiltinDocumentProperties property that returns an object that can be cast to a Microsoft.Office.Core.DocumentProperties collection representing the built-in document properties associated with the workbook. These are the properties that you see when you choose Properties from the File menu and click the Summary tab, including properties such as Title, Subject, Author, and Company. Table 5-6 shows the names of the built-in document properties associated with a workbook.

Table 5-6. The Names of the Built-In Document Properties in Excel

Application name	Author	Category
Comments	Company	Creation date
Format	Hyperlink base	Keywords
Last author	Last print date	Last save time
Manager	Number of bytes	Number of characters
Number of characters (with spaces)	Number of hidden slides	Number of lines
Number of multimedia clips	Number of notes	Number of pages
Number of paragraphs	Number of slides	Number of words
Revision number	Security	Subject
Template	Title	Total editing time

Workbook also has a CustomDocumentProperties that returns an object that can be cast to a Microsoft.Office.Core.DocumentProperties collection representing any custom document properties associated with the workbook. These are the custom properties that you see when you choose Properties from the File menu and click the Custom tab. Custom properties can be created by your code and used to store name and value pairs in the workbook. The DocumentProperties collection is discussed in more detail in the section "Working with Document Properties" later in this chapter.

Saving an Excel Workbook

The Workbook object has a number of properties and methods that are used to save a workbook, detect whether a workbook has been saved, and get the path and filename of a workbook.

The Saved property returns a bool value that tells you whether the latest changes to the workbook have been saved. If closing the document will cause Excel to prompt the user to save, the Saved property will return false. If the user creates a blank new workbook and does not modify it, the Saved property will return true until the user or your code makes a change to the document. You can set the Saved property to true to prevent a workbook from being saved, but be careful: any changes made in that document may be lost because the user will not be prompted to save when the document is closed.

A more common use of the Saved property is to try to keep the state of the Saved property the same as before your code ran. For example, your code might set or create some custom document properties, but if the user does not make any changes to the document while it is open, you might not want the user to be prompted to save. Your code can get the value of the Saved property, make the changes to the document properties, and then set the value of Saved back to the value before your code changed the workbook. This way the changes your code made will only be saved if the user makes an additional change to the document that requires a save. Listing 5-12 shows this approach.

Listing 5-12. A VSTO Customization That Manipulates Document Properties Without Affecting the Saved Property

private void ThisWorkbook_Startup(object sender, EventArgs e)
{
 bool oldSaved = this.Saved;

 try
 {
 Office.DocumentProperties props = this.
 BuiltinDocumentProperties as Office.DocumentProperties;

 props["Author"].Value = "Mark Twain";
 }
 finally
 {
 this.Saved = oldSaved;
 }
}

To save a workbook, you can use the Save method. If the workbook has already been saved, Excel just overwrites the file from the previous save. If the workbook is newly created and has not been saved yet, Excel tries to create a filename (such as Book2.xls if the new workbook was called Book2) and save it to the default file path set by Application.DefaultFilePath.

If you want to specify a filename to save the workbook to, you must use the SaveAs method. SaveAs takes the filename as a string parameter. It also takes a number of optional parameters that you can omit by passing Type.Missing.

If you want to save a copy of the workbook, use the SaveCopyAs method and pass it the copy's filename as a string parameter. SaveCopyAs creates a backup copy of the workbook. It does not affect the filename or save location of the Workbook it is called on.

You can also save the workbook while closing it using the Close method. If you omit all the optional parameters, the user will be prompted to save the workbook if it has been changed since it was created or opened. If you pass false to the SaveChanges parameter, it will close the workbook without saving changes. If you set the SaveChanges parameter to TRue and pass a file name as a string for the Filename parameter, it will save the workbook to the filename you specified.

Several additional properties are used to access the filename and location of the Workbook, as shown in Table 5-7.

Table 5-7. Workbook Properties That Return Filename and Path Information

Property Name	Type	What It Does
FullName	string	Returns the full name of the workbook, including the path. For a saved workbook, it returns the full filename of the workbook. For a new unsaved workbook, it returns the default name Excel gave to the workbook, such as Book1.
FullName-URLEncoded	string	Returns as a URL-encoded string the full name of the workbook, including the path.
Path	string	Returns the full path to the workbook (for example, "C:Documents and SettingsEric CarterMy Documents"). If the workbook has not yet been saved, this property returns an empty string.
Name	string	Returns the name of the workbook (for example, "book1.xls"). If the workbook has not yet been saved, this property returns the temporary name of the document, typically Book1. This can be passed to get_Item on the Workbooks collection to access this workbook.

Table 5-8 shows a number of other properties related to saving.

Table 5-8. Workbook Properties Related to Saving an Excel Workbook

Property Name	Type	What It Does
CreateBackup	bool	Sets whether a backup is created when the workbook is saved.
EnableAuto Recover	bool	Sets whether the auto-save feature of Excel is enabled. If enabled, Excel saves the workbook on a timed interval so if Excel should crash or the system should fail, a backed-up file is available.
FileFormat	XlFile Format	Returns the file format this workbook is saved as.
ReadOnly	bool	Returns true if the file was opened as read-only.

 

Naming Ranges of Cells

Excel enables you to associate a name (a string identifier) with any range of cells. You can define a name for a range of cells by writing code or by using the Define Name dialog box that is shown when you choose Insert > Name > Define from the Excel menu bar. You can also select a cell or range of cells you want to associate a name with and then type the name into the Name Box to the left of the formula bar, as shown in Figure 5-2. When you type the name into the Name Box, you need to press the Enter key after typing the name to set the name.

Figure 5-2. Naming a range of cells myCells using the Name Box.

The Names property returns the Names collection that can be used to access any ranges you have named within the workbook. The Names collection also enables you to create new named ranges. The Names collection is discussed in more detail in the section "Working with the Names Collection and the Name Object" later in this chapter.

When Excel Is Embedded in Another Application

CommandBars, Container, and IsInPlace are properties used when the workbook is opened inside another application such as Internet Explorer or Word. IsInPlace is a property that returns a bool value that tells you whether the workbook has been opened inside another application. The CommandBars property returns the Microsoft.Office.Core.CommandBars collection that is used when a document is in-place. The Container property returns an object that can be used to access the object model of the containing application.

Creating and Activating Windows

The Workbook class has a NewWindow method that you can use to create a new window on the workbook. Although you might expect the way to create new windows would involve calling Add on the Windows collection, it does not. The only way to create a new window is by using this method.

There is also an Activate method that activates the workbook by making the first window associated with the workbook the active window. You can activate a window other than the first window associated with the workbook by using the Windows collection and the Window object. For more information on the Windows and Window objects, see the section "Working with the Window Object" later in this chapter.

Printing a Workbook

The PrintOut method prints the workbook. It takes eight optional parameters, as shown in Table 5-9.

Table 5-9. The Optional Parameters of the PrintOut Method

Parameter Name	Type	What It Does
From	object	Sets the page number at which to start printing.
To	object	Sets the last page number to print.
Copies	object	Sets how many copies to print.
Preview	object	Set to true to show print preview.
ActivePrinter	object	Set to a string representing the printer to print to.
PrintToFile	object	Set to true to print to a file.
Collate	object	Set to TRue to collate multiple copies.
PrintToFileName	object	Set to a string representing the file name to print to if PrintToFile is set to true.

 

Protecting a Workbook

Excel enables you to protect two things at the workbook level: the order of the worksheets in a workbook, and the size and positioning of the windows associated with a workbook. The Protect method takes three optional parameters: Password, Structure, and Windows. Password is an optional parameter that you can pass a string for the password for the workbook. Structure is an optional parameter that can be set to true to protect the sheet order so that the user cannot rearrange the order of the sheets in the workbook.

Windows is an optional parameter that can be set to true to protect the windows associated with the workbook from being moved or resized. For example, you could have two "tiled" windows showing a workbook; locking them prevents the user from moving them from the tiled positions. (See the section "Arranging Windows" later in this chapter for more information about tiling windows.)

Although all these parameters are optional, workbook protection does not really do anything unless you set the Structure or Windows parameter to true. If you want to protect cells in the workbook from being edited, you must use the Worksheet.Protect method.