Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath

2017-07-07 02:10:07

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 documenttypically, Book1. This name can be passed to the Item property 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\book1.xls"). For a new, unsaved workbook, it returns the default name Excel gave 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 selected chart sheet in the workbook. If the selected sheet is not a chart sheet, this property returns `Nothing`.
ActiveSheet	`Object`	Returns the 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	Last print date	Number of pages
Author	Last save time	Number of paragraphs
Category	Manager	Number of slides
Comments	Number of bytes	Number of words
Company	Number of characters	Revision number
Creation date	Number of characters (with spaces)	Security
Format	Number of hidden slides	Subject
Hyperlink base	Number of lines	Template
Keywords	Number of multimedia clips	Title
Last author	Number of notes	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 Boolean 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 new, blank 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. 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 be saved only 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

Public Class ThisWorkbook Private Sub ThisWorkbook_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup Dim oldSaved As Boolean = Me.Saved Try Dim props As Office.DocumentProperties Props = Me.BuiltinDocumentProperties props("Author").Value = "Mark Twain" Finally Me.Saved = oldSaved End Try End Sub End Class

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.

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 by 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 filename 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 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 Settings\Eric Carter\My 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 documenttypically, Book1. This can be passed to 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	`Boolean`	Sets whether a backup is created when the workbook is saved.
EnableAutoRecover	`Boolean`	Sets whether the autosave feature of Excel is enabled. If enabled, Excel saves the workbook on a timed interval so that if Excel should crash or the system should fail, a backed-up file is available.
FileFormat	`XlFileFormat`	Returns the file format this workbook is saved as.
ReadOnly	`Boolean`	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. You can also select a cell or range of cells you want to associate a name with and then type the name in the Name Box to the left of the formula bar, as shown in Figure 5.2. When you type the name in the Name Box, you need to press the Enter key after typing to set the name.

Figure 5.2. Naming a range of cells myCells using the Name Box.

The Names property returns the Names collection, which you can use 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 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 Boolean 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 to involve calling Add on the Windows collection, it does not. The only way to create a new window is to use 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. You could have two "tiled" windows showing a workbook, for example; 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.

Properties That Return Active or Selected Objects

Table 5.4. Workbook Properties That Return Active Objects

Properties That Return Important Collections

Table 5.5. Workbook Properties That Return Important Collections

Accessing Document Properties

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

Saving an Excel Workbook

Listing 5.12. A VSTO Customization That Manipulates Document Properties Without Affecting the Saved Property

Table 5.7. Workbook Properties That Return Filename and Path Information

Table 5.8. Workbook Properties Related to Saving an Excel Workbook