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. The Paragraphs object, for example, 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. Word, for example, 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.

Table 1.1. Key Office Object Model Objects

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 tool-bars, 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 the 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 object; instead, 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. Listing 1.1, for example, 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. The code gets the Worksheets object, which is of type Sheets. It also uses Visual Basic's CType function to cast the Object returned from the Worksheets collection as a Worksheet, which is necessary because the Worksheets 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

Dim myWorkbooks As Excel.Workbooks = app.Workbooks Dim myWorkbook As Excel.Workbook = myWorkbooks.Item(1) Dim myWorksheets As Excel.Sheets = myWorkbook.Worksheets Dim myWorksheet As Excel.Worksheet myWorksheet = CType(myWorksheets.Item(1), Excel.Worksheet) 


If the code does not need to cache each object model object in a variable as it goes but needs only to get a Worksheet object, a more efficient way to write this code is as follows:

Dim myWorksheet As Excel.Worksheet myWorksheet = CType(app.Workbooks.Item(1).Worksheets.Item(1), _   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. The Documents collection object, for example, is a collection of Document objects. Some collection objects may be collections of other types, such as 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 property, which takes a parameter, typically a number, to specify the index of the desired object in the collection. The Item property is typically the default property of the collection. (Default properties are described later in this chapter.) 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 For Each instead), it does illustrate two key points. First, collections in Office object models are almost always 1-based, meaning that they start at index 1 rather than index 0. Second, the parameter passed to the Item property is often passed as an Object, so you can specify either a numeric index as an Integer 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 Item Property with Either an Integer or a String Index

Dim myWorkbooks As Excel.Workbooks = app.Workbooks Dim workbookCount As Integer = myWorkbooks.Count For i As Integer = 1 To workbookCount   ' Get the workbook by its integer index   Dim myWorkbook As Excel.Workbook = myWorkbooks.Item(i)   ' Get the workbook by its string index   Dim workbookName As String = myWorkbook.Name   Dim myWorkbook2 As Excel.Workbook = _     myWorkbooks.Item(workbookName)   MsgBox(String.Format("Workbook {0}", myWorkbook2.Name)) Next 


If you were to look at the definition for the Workbooks collection's Item property, you would see that it takes an Object parameter. Even though the Item property takes an Object parameter, we pass an Integer value or a String value to it in Listing 1.2. This works because Visual Basic can automatically convert a value type such as an Integer to an Object when you pass the value type to a method that takes an Object. This automatic conversion is called boxing. Visual Basic 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 For Each syntax of Visual Basic, as shown in Listing 1.3.

Listing 1.3. Iterating over a Collection Using For Each

Dim myWorkbooks As Excel.Workbooks = app.Workbooks For Each workbook As Excel.Workbook In myWorkbooks   MsgBox(String.Format("Workbook {0}", workbook.Name)) Next 


Advanced Topic

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. Listing 1.4 illustrates this technique.

Listing 1.4. Using a Secondary Collection When Deleting Objects

Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook Dim myCollection As New Collections.Generic.List(Of Excel.Name) For Each name As Excel.Name In myWorkbook.Names   myCollection.Add(name) Next For Each name As Excel.Name In myCollection   name.Delete() Next 



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, or wdWindowStateMinimize. These are constants you can use directly in your code when testing for a value. Each value corresponds to an integer value. (wdWindowStateNormal, for example, is equivalent to 0.) It is considered bad programming style, however, to make comparisons to the integer values rather than the constant names themselves because it makes the code less readable.




Visual Studio Tools for Office(c) Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
ISBN: 0321411757
EAN: 2147483647
Year: N/A
Pages: 221

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net