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.

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

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.

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-8 shows a number of other properties related to saving.

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.

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.