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

2017-07-07 02:10:07

Now that you understand the basic pattern of the Office object models, this chapter explains how developers pattern and build their Office solutions. Most solutions built using Office follow one of three patterns:

Office automation executable

Office add-in

Code behind an Office document

An automation executable is a program separate from Office that controls and automates an Office application. An automation executable can be created with development tools such as Visual Studio. A typical example is a stand-alone console application or Windows Forms application that starts an Office application and then automates it to perform some task. To start a solution built this way, the user of the solution starts the automation executable, which in turn starts the Office application. Unlike the other two patterns, the automation code does not run in the Office process but runs in its own process and talks cross-process to the Office process being automated.

An add-in is a class in an assembly (DLL) that Office loads and creates when needed. An add-in runs in process with the Office application instead of requiring its own process separate from the Office application process. To start a solution built this way, the user of the solution starts the Office application associated with the add-in. Office detects registered add-ins on startup and loads them. An add-in can customize an Office application in the same ways that code behind a document can. Code behind a document, however, unloads when the document associated with the code is closed; an add-in can remain loaded throughout the lifetime of the Office application.

The code-behind pattern was popularized by Visual Basic for Applications (VBA)the development environment included with Office that enables the developer to write Visual Basic code against the object model of a particular Office application and associate that code with a particular document or template. A document can be associated with C# or Visual Basic code behind using VSTO. To start a solution built this way, the user of the solution opens a document that has code behind it or creates a new document from a template that has code behind it. The code behind the document will customize the Office application in some way while the document is open. Code behind the document might add menu items that are present only when the document is open, for example, or associate code with events that occur while the document is open.

We discuss two advanced patterns later in this book. The server document pattern involves running code on a server to manipulate data stored in an Office document without starting the Office application. VSTO makes this scenario possible through a feature called cached data. Chapter 18, "Server Data Scenarios," discusses this pattern. The XML and XSLT pattern is similar to the server document pattern and involves writing code to generate Word or Excel documents in WordprocessingML or SpreadsheetML format without starting the Office application. You can also generate these formats by applying an XSLT transform to some XML data. Chapter 21, "Working with XML in Excel," and Chapter 22, "Working with XML in Word," discuss these scenarios.

Hosted Code

The add-in and code-behind patterns are sometimes called hosted code, which means that your code runs in the same process as the Office application.

Discovery of Hosted Code

For code to run in the Office application process, the Office application must be able to discover your code, load the code into its process space, and run your code. Office add-ins are registered in the Windows registry so that Office can find and start them. Using the registry seems a little non-.NET, but this is necessary because Office 2003 talks to add-ins as though they were COM objects through COM interop.

The code behind a document pattern does not require a registry entry. Instead, code is associated with a document by adding some special properties to the document file. Office reads these properties when the document opens and then Office loads the code associated with the document.

Context Provided to Hosted Code

It is critical that your hosted code get context; it needs to get the Application object or Document object for the Office application into which it is loading. COM add-ins are provided with context through an interface implemented by the add-in class. Outlook add-ins in VSTO are provided with context through a class created in the project that represents the application being customized. Code behind a document in VSTO is provided with context through a class created in the project that represents the document being customized.

Entry Point for Hosted Code

At startup, Office calls into an entry point where your code can run for the first time and register for events that might occur later in the session. For a COM add-in, this entry point is the OnConnection method of the IDTExtensibility2 interface implemented by the COM add-in. For a VSTO Outlook add-in and VSTO code behind a document, this entry point is the Startup event handler.

How Code Gets Run After Startup

After hosted code starts up, code continues to run in one or more of the following ways.

Code Runs in Response to Events Raised by Office

The most common way that code runs after startup is in response to events that occur in the Office application. Office raises events when a document opens or a cell in a spreadsheet changes, for example. Listing 1.17 in Chapter 1 shows a simple class that listens to the WindowActivate event that Excel's Application object raises. Typically, you will connect event listeners declaratively by using the WithEvents keyword to specify a member variable that raises events and the Handles keyword to tell Visual Basic that a particular method handles a particular event.

Interface Methods Called on Objects Provided to Office

Objects such as the startup class for a COM add-in implement an interface called IDTExtensibility2 that has methods that Office calls during the run of the Office application. If the user turns off the COM add-in, for example, Office calls the OnDisconnection method on the IDTExtensibility2 interface implemented by the COM add-in. In this way, additional code runs after the initial entry point has run.

Events Raised on Code Behind Classes

The classes generated in VSTO projects that represent the customized application or document handle the Startup and Shutdown events. After the constructor of the class executes, Office raises the Startup event. When the document is about to be closed, Office raises the Shutdown event.

How Code Gets Unloaded

Your code gets unloaded in a number of ways, depending on the development pattern you are using. If you are using the automation-executable pattern, your code unloads when the automation executable you have written exits. If you are using the add-in pattern, your code unloads when the Office application exits or when the user turns off the add-in via an add-in management dialog box. If you are using the code-behind pattern, your code unloads when the document associated with your code is closed.

In the hosted patterns of running code, there is some method that is called or event that is raised notifying you that you are about to be unloaded. For COM add-ins, Office calls the OnDisconnection method. For VSTO code behind documents and Outlook add-ins, Office raises the Shutdown event before your code is unloaded.