Office Object Models
Almost all Office programming involves writing code that uses the object model of an Office application. The object model is the set of objects provided by the Office application that running code can use to control the Office application. The object model of each Office application is organized hierarchically with the object called Application forming the root of the hierarchy. From the Application object, other objects that make up the object model of the Office application can be accessed.
As an example of how object model objects are related in the object model hierarchy, Figure 1-1 shows some of the most important objects in the Word object model. The root object is the Application object. Also shown in this diagram are some other objects, including Documents, Document, Paragraphs, and Paragraph. The Application object and Documents object are related because the Documents object is returned via a property on the Application object. Other objects are not directly accessible from the root Application object, but are accessible by traversing a path. For example, the Paragraphs object is accessed by traversing the path from Application to Documents to Document to Paragraphs. Figure 1-2 shows a similar diagram for some major objects in the Excel object model hierarchy.
Figure 1-1. Hierarchy in the Word object model.
Figure 1-2. Hierarchy in the Excel object model.
Objects
Each Office application's object model consists of many objects that you can use to control the Office application. Word has 248 distinct objects, Excel has 196, and Outlook has 67. Objects tend to correspond to features and concepts in the application itself. For example, Word has objects such as Document, Bookmark, and Paragraphall of which correspond to features of Word. Excel has objects such as Workbook, Worksheet, Font, Hyperlink, Chart, and Seriesall of which correspond to features of Excel. As you might suppose, the most important and most used objects in the object models are the ones that correspond to the application itself, the document, and key elements in a document such as a range of text in Word. Most solutions use these key objects and only a small number of other objects in the object models. Table 1-1 lists some of the key objects in Word, Excel, and Outlook along with brief descriptions of what these objects do.
|
Object Name |
What It Does |
|---|---|
|
All Office Applications |
|
|
Application |
The root object of the object model. Provides properties that return other objects in the object model. Provides methods and properties to set application-wide settings. Raises application-level events. |
|
CommandBars |
Enables the developer to add, delete, and modify toolbars, buttons, menus, and menu items. |
|
Window |
Enables the developer to position windows and modify window-specific settings. In Outlook, the objects that perform this function are the Explorer and Inspector objects. |
|
Word Objects |
|
|---|---|
|
Document |
Represents the Word document. Is the root object of the content-specific part of the Word object model. Raises document-level events. |
|
Paragraph |
Enables the developer to access a paragraph in a Word document. |
|
Range |
Enables the developer to access and modify a range of text in a Word document. Provides methods and properties to set the text, set the formatting of the text, and perform other operations on the range of text. |
|
Excel Objects |
|
|---|---|
|
Workbook |
Represents the Excel workbook. Is the root object of the content-specific part of the Excel object model. Raises workbook-level events. |
|
Worksheet |
Enables the developer to work with a worksheet within an Excel workbook. |
|
Range |
Enables the developer to access and modify a cell or range of cells in an Excel workbook. Provides methods and properties to set the cell value, change the formatting, and perform other operations on the range of cells. |
|
Outlook Objects |
|
|---|---|
|
MAPIFolder |
Represents a folder within Outlook that can contain various Outlook items such as MailItem, ContactItem, and so on as well as other folders. Raises events at the folder level for selected actions that occur to the folder or items in the folder. |
|
MailItem |
Represents a mail item within Outlook. Provides methods and properties to access the subject and message body of the mail along with recipient and other information. Raises events when selected actions occur that involve the mail item. |
|
ContactItem |
Represents a contact within Outlook. Provides methods and properties to access the information in the contact. Raises events when selected actions occur that involve the contact. |
|
AppointmentItem |
Represents an appointment within Outlook. Provides methods and properties to access the information in the appointment. Raises events when selected actions occur that involve the appointment. |
Where objects in an Office object model start to differ from typical .NET classes is that the vast majority of object model objects are not creatable or "new-able." In most Office object models, the number of objects that can be created by using the new keyword is on the order of one to five objects. In most Office solutions, new will never be used to create an Office objectinstead, an already created Office object, typically the root Application object, is passed to the solution.
Because most Office object model objects cannot be created directly, they are instead accessed via the object model hierarchy. For example, Listing 1-1 shows how to get a Worksheet object in Excel starting from the Application object. This code is a bit of a long-winded way to navigate the hierarchy because it declares a variable to store each object as it traverses the hierarchy. The code assumes that the root Excel Application object has been passed to the code and assigned to a variable named app. It also uses C#'s as operator to cast the object returned from the Worksheets collection as a Worksheet, which is necessary because the Worksheet collection is a collection of object for reasons described in Chapter 3, "Programming Excel."
Listing 1-1. Navigating from the Application Object to a Worksheet in Excel
Excel.Workbooks myWorkbooks = app.Workbooks; Excel.Workbook myWorkbook = myWorkbooks.get_Item(1); Excel.Worksheets myWorksheets = myWorkbook.Worksheets; Excel.Worksheet myWorksheet = myWorksheets.get_Item(1) as Excel.Worksheet;
If the code does not need to cache each object model object in a variable as it goes but only needs to get a Worksheet object, a more efficient way to write this code is as follows:
Excel.Worksheet myWorksheet2 = app.Workbooks.get_Item(1). Worksheets.get_Item(1) as Excel.Worksheet;
Collections
Paragraphs and Documents are examples of a type of object called a collection. A collection is a specialized object that represents a group of objects. Typically, a collection is named so that its name is the plural of the type of the object it contains. For example, the Documents collection object is a collection of Document objects. Some collection objects may be collections of a value type such as a string.
Collections typically have a standard set of properties and methods. A collection has a Count property, which returns the number of objects in the collection. A collection also has an Item method, which takes a parameter, typically a number, to specify the index of the desired object in the collection. A collection may have other properties and methods in addition to these standard properties and methods.
Listing 1-2 shows iteration over a collection using the Count property of the collection and the Item method of the collection. Although this is not the preferred way of iterating over a collection (you typically use foreach instead), it does illustrate two key points. First, collections in Office object models are almost always 1-based, meaning they start at index 1 rather than index 0. Second, the parameter passed to the get_Item method is often passed as an object so you can either specify a numeric index as an int or the name of the object within the collection as a string.
Listing 1-2. Iterating Over a Collection Using the Count Property and the get_Item Method with Either an int or a string Index
Excel.Workbooks myWorkbooks = app.Workbooks; int workbookCount = myWorkbooks.Count; for (int i = 1; i <= workbookCount; i++) { // Get the workbook by its int index Excel.Workbook myWorkbook = myWorkbooks.get_Item(i); // Get the workbook by its string index string workbookName = myWorkbook.Name; Excel.Workbook myWorkbook2 = myWorkbooks.get_Item(workbookName); MessageBox.Show(String.Format("Workbook {0}", myWorkbook2.Name)); }
If you were to look at the definition for the Workbooks collection's get_Item method, you would see that it takes an object parameter. Even though the get_Item method takes an object parameter, we pass an int value and a string value to it in Listing 1-2. This works because C# can automatically convert a value type such as an int or a string to an object when you pass the value type to a method that takes an object. This automatic conversion is called boxing. C# automatically creates an object instance known as a box to put the value type into when passing it to the method.
The preferred way of iterating over a collection is using the foreach syntax of C#, as shown in Listing 1-3.
Listing 1-3. Iterating over a Collection Using foreach
Excel.Workbooks myWorkbooks = app.Workbooks; foreach (Excel.Workbook workbook in myWorkbooks) { MessageBox.Show(String.Format("Workbook {0}", workbook.Name)); }
Sometimes you may want to iterate over a collection and delete objects from the collection by calling a Delete method on each object as you go. This is a risky practice because behavior of a collection in the Office object models is sometimes undefined if you are deleting items from it as you iterate over it. Instead, as you iterate over the Office object model collection, add the objects you want to delete to a .NET collection you have created, such as a list or an array. After you have iterated over the Office object model collection and added all the objects you want to delete to your collection, iterate over your collection and call the Delete method on each object.
Enumerations
An enumeration is a type defined in an object model that represents a fixed set of possible values. The Word object model contains 252 enumerations, Excel 195, and Outlook 55.
As an example of an enumeration, Word's object model contains an enumeration called WdWindowState. WdWindowState is an enumeration that has three possible values: wdWindowStateNormal, wdWindowStateMaximize, wdWindowStateMinimize. These are constants you can use directly in your code when testing for a value. Each value corresponds to an integer value. (For example, wdWindowStateNormal is equivalent to 0.) However, it is considered bad programming style to make comparisons to the integer values rather than the constant names themselves because it makes the code less readable.