Automating Microsoft Office 97 and Microsoft Office 2000

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Lori Turner
Microsoft Corporation

March 2000

Summary: This article provides the fundamentals for understanding how to automate Microsoft Office 97 and Microsoft Office 2000 applications. This article starts with the basics and walks you through creating a variety of fully functional Automation clients. It is structured as a tutorial with a large assortment of code samples and provides tips throughout that will facilitate development of your Automation clients. Exercises and sample code are presented for Microsoft Visual Basic, Microsoft Visual C/C++, and Microsoft Foundation Classes (MFC) developers. However, much of the information is generic (not language-specific), so you can apply it regardless of the development language you use. (123 printed pages)

Download Offautmn.exe.

Contents

Introduction
Office Object Models
How an Object Exposes Its Methods and Properties
Creating an Automation Client with Visual Basic
Creating an Automation Client with C++
Creating an Automation Client with MFC
Improving the Performance of Your Automation Code
Handling Events in the Automation Server
Automating Embedded and Linked Office Documents
Sample Code for Automating Microsoft Office Applications
Troubleshooting Common Problems
For More Information

Introduction

Automation, formerly called "OLE Automation," is a technology that allows you to take advantage of an existing program's content and functionality, and to incorporate it into your own applications. Automation is based on the Component Object Model (COM). COM is a standard software architecture based on interfaces that is designed to separate code into self-contained objects, or components. Each component exposes a set of interfaces through which all communication to the component is handled.

With Automation, you can use the Microsoft® Word mail merge feature to generate form letters from data in a database without the user being aware that Word is involved. You could even use Automation to incorporate all of the charting and data analysis functionality that Microsoft Excel provides. You don't need to write your own calculation engine to provide the multitude of mathematical, financial, and engineering functions that Excel provides; instead, you can automate Microsoft Excel to "borrow" this functionality and incorporate it into your own application.

Automation consists of a client and a server. The Automation client attaches to the Automation server so that it can use the content and functionality that the Automation server provides. The terms client and server are mentioned frequently throughout this article, so it is important that you understand their relationship.

This article is intended to provide you with a foundation for developing your own Automation clients for Microsoft Office applications. In this article, we provide a hands-on approach to help you to do the following:

• Understand how Office applications expose their content and functionality to Automation clients
• Identify the specific functions for the task you choose to Automate
• Locate the resources and documentation you need
• Understand how Automation works behind the scenes
• Create Automation clients with Visual Basic®, Visual C++®, and Microsoft Foundation Classes (MFC)
• Develop a controller that uses the server as efficiently as possible

All the Microsoft Office applications have their own scripting language, which can be used to perform tasks within applications. This scripting language is Microsoft Visual Basic for Applications (VBA). The set of functions that a VBA routine, or macro, can use to control its host application is the same set of functions that the Automation client can use to control the application externally, regardless of the programming language for the controller. Understandably, the Office applications provide documentation on their scripting functions in a syntax that is easily interpreted by the VBA programmer. So, if you choose to write your controller in another programming language, such as Visual C++, MFC, or Microsoft Visual J++®, you must translate the VBA syntax of Office functions so that you can apply it to the programming language you choose for your controller.

This article is structured as a tutorial and consists of a series of discussions and exercises. For the exercises, you need the following:

• Microsoft Office 97 or 2000
• Microsoft Visual Basic 5.0 or 6.0
• Microsoft Visual C++ 5.0 or 6.0

You can use the steps and code in each exercise to create a complete and functional application. If you run into problems during an exercise, you can refer to the samples included with this article. The projects for the exercises and samples are outlined below.

Office Object Models

Microsoft Office applications expose their functionality as a set of programmable objects. Every unit of content and functionality in Office is an object that you can programmatically examine and control. A workbook, a document, a table, a cell, and a paragraph are all examples of objects that are exposed by Microsoft Office applications.

Objects, Methods, and Properties

Objects for each of the Microsoft Office applications are arranged hierarchically, similar to a family tree, in what is called an object model. Each object model has an uppermost object from which all other objects are derived. This uppermost object usually represents the Office application itself, and is appropriately named the Application object. The Application object has child objects, which, in turn, have child objects of their own.

To visualize this idea of object hierarchy, you can examine some of the objects exposed by Microsoft Excel. Figure 1 is a graphical representation of a small fragment of the Excel object model.

Figure 1. This diagram represents just a small portion of the Excel object model, portraying the parent-child relationships of the objects.

As you can see, the uppermost object in the Excel object model is the Application object. The Excel Application object has many children, two of which are Workbooks and CommandBars. Workbooks and CommandBars are collection objects that contain other objects. A Workbooks collection object contains Workbook objects and a CommandBars collection object contains CommandBar objects. A Workbook has many children of its own, two of which are the Worksheets collection object and the Charts collection object. A Worksheet object has many children of its own as well, including the Range object and the Shapes collection object. The list goes on, but this should be enough to help you understand how object models are organized hierarchically. Understanding the relationships between objects is fundamental to automating Office applications.

An object by itself does nothing unless you can do something with that object. To programmatically examine or control an object, you can use the properties and methods that the object supports. A property is a function that sets or retrieves an attribute for an object. A method is a function that performs some action on an object.

Once again, look at the Excel object model to get an idea of what is considered a property and what is considered a method. Excel exposes a Range object that represents a range of one or more cells on a worksheet. A Range object has attributes that describe its contents, number format, and font. These attributes can all be determined by inspecting properties of the Range object: the Value property, the NumberFormat property, and the Font property, respectively. There are actions that you can make on a Range object; you can select it, delete it, and copy it. These actions represent methods of the Range object; the Select method, the Delete method, and the Copy method.

In Visual Basic, you navigate to an object by starting at the uppermost object and working your way down to your target. Consider the Workbooks collection object, which represents all the open workbooks in the Excel application. You could use its Count property to acquire the count of workbooks open in Excel:

i =  Application.Workbooks.Count

For another example, consider the Range object. To navigate to a Range object, you would start at the Application object and work your way down. The following code returns the value of cell A1 on Sheet1 in a workbook named Book1.xls:

x= Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Value

Note that Workbooks("Book1.xls") returns a single Workbook object from the Workbooks collection. Another way that you can return a single object from a collection is to use the Item property for the collection. You could use the following code to achieve the same results:

x= Application.Workbooks.Item("Book1.xls"). _
                      Worksheets.Item("Sheet1").Range("A1").Value

For simplicity, Visual Basic programmers typically use the shorter form and omit the Item property when obtaining a single object from a collection. However, as the sample code presented in this article will illustrate, the Item property is important for C/C++ programmers because it must be explicitly called to obtain a single object from a collection.

Type Libraries

A type library is a file or part of a file that provides information about the functionality of a COM object. More specifically, the type library contains information about classes. A class is a description of an object. Type libraries themselves do not store actual objects; they only store information about those objects.

A type library specifies all the information an Automation client needs to call a method or property for an object. For properties, the type library describes the value it accepts or returns. For methods, the type library provides a list of all the arguments the method can accept, tells you the data type of each argument, and indicates whether an argument is required.

Type libraries can appear in any of the following forms:

• A resource in a .dll file
• A resource in an .exe file
• A stand-alone type library file (.tlb)

Each Microsoft Office application provides multiple type library resources in a single .dll file. A .dll file with multiple type library resources is typically called an object library (.olb). The following table lists the file names for Microsoft Office 97 and Office 2000 type libraries. The type library for each application can be found in the same folder as the application's .exe file.

Object browser

In Visual Basic and in the Visual Basic Editor of Office applications, you can use the Object Browser to view a type library. To display the Object Browser, press F2 or click Object Browser on the View menu.

Figure 2. The Object Browser provides information about the classes, methods, and properties a COM object exposes.

The Object Browser in Figure 2 displays all of the objects that the Microsoft Word type library exposes. When you select a class, all of its properties and methods (or class members) are provided in a list. When you select a property or method, the syntax for the item you have selected appears in the bottom pane of the Object Browser.

OLE/COM Object Viewer

To view type library information, you can also use the OLE/COM Object Viewer utility that is included with Microsoft Visual Studio®. Like the Object Browser that Visual Basic and Microsoft VBA use, the OLE/COM Object Viewer lists all the classes exposed in a type library, along with the methods and properties those classes support. For the C++ programmer, the OLE/COM Object Viewer provides important information, such as function return types, argument types, and DISPIDs (which will be discussed shortly). You can use the information that the viewer provides in conjunction with the Office object model documentation.

To view a type library with the OLE/COM Object Viewer:

1. In Visual C++, on the Tools menu, click OLE/COM Object Viewer.
2. On the File menu, click View TypeLib, and browse to locate the type library you want to view.

The OLE/COM Object Viewer illustrated in Figure 3 displays information from the Microsoft Word 97 type library. More specifically, it shows the details for the Add member function of the Documents class.

Figure 3. Use the OLE/COM Object Viewer to view class information in a type library.

Where to Find the Object Model Documentation

The object models for the Office applications are documented in the language reference for both versions of Office:

• Microsoft Office 97 Visual Basic for Applications Language Reference
  ISBN 1-57231-339-0
• Microsoft Office 2000 Visual Basic for Applications Language Reference
  ISBN 1-57231-955-0

The language references are available on MSDN and in the Help files that are included with Microsoft Office. They can also be purchased in printed form. For ordering information, please visit http://mspress.microsoft.com/.

The following table lists the Help files for each Office application.

The Help files that are included with Microsoft Office 97 are installed by default in the C:\Program Files\Microsoft Office\Office folder. If you cannot find the Office 97 Visual Basic for Applications Help file you need, it probably was not installed when you initially ran Office 97 Setup. To install the Help file, run Office Setup to add the Visual Basic for Applications Help file. Note that Office Setup does not install the Outlook 97 Visual Basic for Applications Help file. For information on installing the Outlook 97 Visual Basic for Applications Help file, please see the following article in the Microsoft Knowledge Base:

Q166738 OL97: How to Install Visual Basic Help

The Help files that are included with Microsoft Office 2000 are installed by default in the C:\Program Files\Microsoft Office\Office\1033 folder. Microsoft Office 2000 Setup will install the VBA Help files "on first use." Therefore, you might not see the Help file in this folder if you have not previously attempted to access VBA Help in the Office application.

How to Use the Object Model Documentation

There are several methods you can use to find documentation for a specific class, method, or property:

• Search the Visual Basic for Applications Help file.
  In the Visual Basic Editor for the Office application, click Contents and Index on the Help menu. On the Contents tab, select the language reference you want and click Display. VBA Help for the language reference you selected appears. At this point, you can use either the Index or the Find tab to locate information on a specific class, method, or property.
• Use Context Sensitive Help in a module or in the Immediate Window.
  In the Visual Basic Editor for the Office application, type the class, method, or property in the code window of a module or in the Immediate Window. Select the text and press F1. The Help topic for the item appears.
• Use the Object Browser.
  Press F2 in the Visual Basic Editor for the Office application to display the Object Browser. The Object Browser lists all of the objects that the application exposes and, for each object, it lists its methods, properties, and events. To view Help on a specific class or class member, select it in the Object Browser and press F1.

Exercise 1: Determining which Classes, Methods, and Properties to Use

If you are not already familiar with the object model of the application you intend to automate, you can use the application's macro recorder to get an idea of where you should begin. To illustrate, suppose you want to automate Microsoft Word to add some text to a new document and then save the document, but you don't know which methods and properties to use. You can start with the macro recorder:

1. Start Microsoft Word.
2. On the Tools menu, click Macro, and then select Record New Macro. In the Store Macro In drop-down box, select the name of the active document. Make note of the new macro's name, and then click OK to start recording.
3. Start a new document.
4. Type one and press ENTER.
5. Type two and press ENTER.
6. Type three.
7. On the File menu, click Save, and save the document as C:\doc1.doc.
8. Click the Stop Recording button (or, on the Tools menu, click Macro and then Stop Recording).

To view the VBA code that the macro recorder generated from your actions, on the Tools menu, click Macro, and then click Macros. Select the name of the new macro in the list and click Edit. The Visual Basic Editor displays the recorded macro.

    Documents.Add
    Selection.TypeText Text:="one"
    Selection.TypeParagraph
    Selection.TypeText Text:="two"
    Selection.TypeParagraph
    Selection.TypeText Text:="three"
    ActiveDocument.SaveAs FileName:="Doc1.doc",
        FileFormat:=wdFormatDocument, _
        LockComments:=False, Password:="", AddToRecentFiles:=True, 
        WritePassword:="", ReadOnlyRecommended:=False, 
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, 
        SaveFormsData:=False, SaveAsAOCELetter:= False 

You can benefit from understanding how each class fits within the object model, and from learning the description and type of all parameters for the methods and properties you use for your task.

Start by examining the first line of the recorded macro: Documents.Add. Select Documents in the code module for the recorded macro and press F1. The Help topic provides you with the following important information:

• The Documents property returns a Documents collection that represents all of the open documents.
• The Documents property applies to the Application object.

Return to the recorded macro, select Add on the code module, and press F1. A Help topic appears explaining that many different objects have an Add method. Click Documents to see the Help for the Add method of the Documents collection. The Help topic provides the following important information:

• The Add method adds a new, empty document to the collection of open documents.
• The Add method can take two arguments, both of which are optional.

Now examine the next line in the recorded macro: Selection.TypeText Text:="one". Click Selection in the code module and press F1:

• The Selection property returns the Selection object that represents a selected range or the insertion point.
• The Selection property applies to the Application object.

Return to the recorded macro, click TypeText on the code module, and press F1:

• The TypeText method inserts the specified text.
• The TypeText method has one required argument of type String.
• The TypeText method applies to the Selection object.

Next, see the Help topic for TypeParagraph:

• The TypeParagraph method inserts a new blank paragraph.
• The TypeParagraph method applies to the Selection object and has no arguments.

Now, examine the Help topics for the ActiveDocument property and the SaveAs method:

• The ActiveDocument property returns a Document object that represents the document with the focus. The ActiveDocument property applies to the Application object.
• The SaveAs method saves a document. This method has eleven arguments, only one of which is required. The SaveAs method applies to a Document object.

You might have noticed that the Documents property, Selection property, and ActiveDocument property are all properties that apply to the Application object, yet are not qualified with the Application object in the recorded macro. The Application object is the default object for all "unqualified" properties in Word VBA and can therefore be omitted when writing code in a Word VBA macro. This is not the case when writing Automation code. As you will see in code samples presented later in this article, all properties and methods should be fully qualified. Failure to fully qualify properties and methods in your Automation client can result in errors and unpredictable results at run time.

Upon examination of the recorded macro, notice that the SaveAs method has an argument for which it passes the built-in constant wdFormatDocument. Depending on the programming language you choose for your Automation client, you might need to pass the numeric value for built-in constants. The Help topic for the SaveAs method does not give you this information, but you can find it in the Object Browser. Press F2 to display the Object Browser. Type wdFormatDocument in the search window and press ENTER. In the bottom pane of the Object Browser, note that the numeric equivalent of wdFormatDocument is 0, as well as noting other information about the constant.

PROGIDs and CLSIDs

Office applications register all of their classes in the Windows® system registry. Each class is associated with a globally unique identifier (or GUID) called a class identifier. In the registry, each class identifier (or CLSID) is mapped to a programmatic identifier (or PROGID) and to its application, as shown in Figure 4.

Figure 4. The relationship between PROGID, CLSID, and Server as described in the Windows registry

When automating an Office application, you can use either the CLSID or the PROGID to create an object from one of the classes that the Office application exposes. The following table lists the most commonly used PROGIDs for Office 97 and Office 2000 applications:

This table provides version-independent PROGIDs. You should note that Office applications have version-dependent PROGIDs as well. For example, Excel 97 has Excel.Application.8 and Excel 2000 has Excel.Application.9. You can use these PROGIDs in your Automation code, but it is recommended that you use the version-independent PROGIDs so that your code can be compatible with multiple versions of the application you automate.

How an Object Exposes Its Methods and Properties

All COM objects that can be automated implement a special interface: the IDispatch interface. It is this IDispatch interface that provides Automation clients with access to an object's content and functionality. An object can expose its methods and properties in two ways: by means of a dispatch interface and in its vtable.

The Dispatch Interface

An object's methods and properties collectively make up its dispatch interface (or dispinterface). Within the dispinterface, each method and property is identified by a unique member. This member is the function's dispatch identifier (or DispID).

Figure 5. An object can provide clients access to its functions using a dispinterface, an array of function names, and an array of function pointers that are indexed by DispIDs.

To execute a method or a property of the object portrayed in Figure 5, an Automation client can:

1. Call GetIDsOfNames to "look up" the DispID for the method or property.
2. Call Invoke to execute the method or property by using the DispID to index the array of function pointers.

**Note   **IDispatch and its functions are described in greater detail in this article under "Creating an Automation client with C++."

Virtual Function Table (vtable)

The pointer to an interface references a table of pointers to functions that the interface supports. This table is called the virtual function table (or vtable). A COM object can expose its functions in its vtable to provide clients more direct access to the functions. Subsequently, this eliminates the need to call Invoke and GetIDsOfNames.

An object that exposes its methods through both a dispinterface and its vtable supports a dual interface. Figure 6 represents an object that has a dual interface. Clients can either:

• Access its functions using GetIDsOfNames and Invoke.

  -or-

• Access its functions through the vtable. It is up to the Automation client to decide which method it uses to gain access to the object's functions.

Figure 6. An object that has a dual interface can provide clients access to its functions with a dispinterface and in its vtable.

You can examine type libraries in the OLE/COM Object Viewer to determine if an object provides a dual interface. An object that provides a dual interface specifies the dual attribute in its interface declaration.

Creating an Automation Client with Visual Basic

Visual Basic programmers have an advantage when it comes to writing code to automate Office applications in that any VBA macro can be copied and pasted and, with a few modifications, can become valid Visual Basic Automation code. An important factor determining how your recorded macro code should be modified so that it becomes Automation code is the type of binding you plan to use.

Binding

Binding describes how an Automation client internally invokes a given method or property. There are two types of binding: early binding and late binding. With Visual Basic, the type of binding you choose is determined solely by the manner in which you declare your variables.

Early binding

With early binding, your project maintains a reference to the Automation server's type library and you declare your variables as types defined by that type library. For example, if your project had a reference to the Microsoft Word type library, you could declare an object variable as shown:

Dim oWordApp as Word.Application

Because information from the server's type library is available at compile time, Visual Basic does not need to "look up" the property or method at run time. If the object exposes its function in its vtable, Visual Basic uses vtable binding and calls the function through the vtable. If the object does not support vtable binding, Visual Basic invokes the function using the DispId it obtained from the type library. In other words, it uses DispID binding.

Late binding

With late binding in Visual Basic, your project does not need a reference to the Automation server's type library and you can declare your variables as type Object.

Dim oWordApp as Object

When you declare a variable as type Object, Visual Basic cannot determine at compile time what sort of object reference the variable contains, so binding occurs at run time. When you call a property or method of an object with late binding, the process by which Visual Basic makes the call, is twofold:

• Using an interface pointer to the object, Visual Basic calls GetIDsOfNames to "look up" the name of that property or method to retrieve its DispID.

  -and-

• Visual Basic calls Invoke to execute the property or method using the DispID.

Choosing the correct type of binding for your automation client

There are advantages to both types of binding. You should weigh the benefits of each before deciding which type of binding to choose for your Automation client.

Early binding provides you with increased performance and compile-time syntax checking. In addition, you receive direct access to the server's type library in the Visual Basic design environment and to the Help for the object model.

Despite the benefits of early binding, late binding is most advantageous when you are writing Automation clients that you intend to be compatible with future versions of your Automation server. With late binding, information from the server's type library is not "hard-wired" into your client, so you can have greater confidence that your Automation client can work with future versions of the Automation server without code changes.

For more information about binding in Visual Basic, please see the following article in the Microsoft Knowledge Base:

Q245115 INFO: Using Early Binding and Late Binding in Automation

Exercise 2: Creating a Visual Basic Automation Client that Uses Early Binding

Earlier, you recorded a macro in Word to perform the simple task of starting a new document, adding text to that document, and saving it. Now you will convert that recorded code to Automation code that uses early binding. Early-bound code in Visual Basic is characterized by a reference to the Automation server's type library and variables declared as objects that are defined by that type library.

1. Start a new Standard EXE project in Visual Basic. Form1 is created by default.

2. On the Project menu, click References, and select "Microsoft Word 8.0 Object Library" (or "Microsoft Word 9.0 Object Library" if you are using Word 2000).

3. Add a CommandButton to Form1.

4. Add the following code to the Click event of the CommandButton:

   Dim oWordApp As Word.Application
   
   'Start a new instance of Microsoft Word
   Set oWordApp = New Word.Application
   
   With oWordApp
   
      'Create a new document
      .Documents.Add
   
      'Add text to the document
      .Selection.TypeText Text:="one"
      .Selection.TypeParagraph
      .Selection.TypeText Text:="two"
      .Selection.TypeParagraph
      .Selection.TypeText Text:="three"
      'Save the document
      .ActiveDocument.SaveAs FileName:="c:\Doc1.doc", _
          FileFormat:=wdFormatDocument, LockComments:=False, _
          Password:="", AddToRecentFiles:=True, WritePassword _
          :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
          SaveNativePictureFormat:=False, SaveFormsData:=False, _
          SaveAsAOCELetter:= False
   
   End With
   
   'Quit Word
   oWordApp.Quit
   
   'Unload this form
   Unload Me 
   

5. Press F5 to run the program and click the CommandButton. When the program ends, examine the Word document ("C:\doc1.doc") it created.

Compare this Automation code to the macro you recorded in the previous exercise. There are several important differences you should note:

• The variable oWordApp, which contains your reference to the instance of Word, is declared as the type Word.Application. This is what makes the Automation code "early-bound."

• When Word is started through Automation, it is not visible. Most Automation servers have a Visible property for the Application object that you can set to True if you want to make the server visible at run time.

• The Documents, Selection, and ActiveDocument properties are now all "qualified" with the reference to the Word Application object (oWordApp). Remember that, in a Word VBA macro, the Application object is assumed and can be omitted. This is not the case when you write Automation code; all properties and methods should be fully qualified.

  **Note   **Failure to fully qualify your calls to an Automation server's methods and properties can generate run-time errors or give you unexpected results.

• In the exercise, you added a line to quit the Word application. If you do not call Quit, a "hidden" instance of an Automation server might remain running even after your code ends, and this is almost always undesirable.

Exercise 3: Creating a Visual Basic Automation Client that Uses Late Binding

Now you will modify the project you created for early binding to late binding. "Late-bound" Automation code does not require a reference to the server's type library, and the variables are declared as type Object.

1. On the Project menu, click References, and remove the reference to the Word type library.

   Modify the code for Form1 so that it is "late-bound." The modified code should appear as follows:

   Dim oWordApp As Object
   
   'Start a new instance of Microsoft Word
   Set oWordApp = CreateObject("Word.Application")
   
   With oWordApp
   
      'Create a new document
      .Documents.Add
   
      'Add text to the document
      .Selection.TypeText Text:="one"
      .Selection.TypeParagraph
      .Selection.TypeText Text:="two"
      .Selection.TypeParagraph
      .Selection.TypeText Text:="three"
      'Save the document
      .ActiveDocument.SaveAs FileName:="c:\Doc1.doc", _
          FileFormat:=0, LockComments:=False, _
          Password:="", AddToRecentFiles:=True, WritePassword _
          :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
          SaveNativePictureFormat:=False, SaveFormsData:=False, _
          SaveAsAOCELetter:= False
   
   End With
   
   'Quit Word
   oWordApp.Quit
   
   'Unload this form
   Unload Me 
   

2. Press F5 to run the program and then click the CommandButton. When the program ends, examine the Word document ("C:\doc1.doc") it created. The results are exactly the same as they were with the "early-bound" code.

Note that in the "late-bound" code you have replaced the constant wdFormatDocument with its numeric equivalent (0). This is necessary because you removed the reference to the Microsoft Word type library. If you choose to use late binding in your Visual Basic projects, all built-in constants should be replaced with their numeric equivalents, which you can determine using the Object Browser as previously illustrated.

Automating a Running Instance of an Office Application

To automate an instance of an Office application that is already running, you can use the GetObject function in a Visual Basic project that uses either early or late binding. In the previous examples for early and late binding, if Word were already running and you wanted to use that running instance for Automation, you would replace the statement that starts a new instance of Word with the following:

Set oWordApp = GetObject( , "Word.Application")

The remainder of the code could remain the same. Of course, you probably would not want to call the Quit method to quit an instance of Word that the user had possibly established.

Creating an Automation Client with C++

The IUnknown and IDispatch Interfaces

Interfaces are the cornerstone to COM. An interface is a table of pointers to related functions. Once you acquire a pointer to an interface, you have access to the functions in that interface. The IUnknown and IDispatch interfaces are at the heart of Automation.

All COM interfaces inherit from the IUnknown interface. IUnknown gives COM objects the means to manage their lifetimes and provides clients access to other interfaces that an object supports. The IUnknown interface has only three functions, all of which a COM object must support.

Each COM object is responsible for maintaining a count of the number of interface pointers it has handed out to clients by means of AddRef and Release. This count is called the reference count. When the object's reference count goes to zero, this is an indication to the object that there are no clients currently using its interfaces and that it can safely remove itself from memory. As you can imagine, properly maintaining reference counts is very important; if references to objects are not properly released, you risk the chance of leaving an object in memory even when it is no longer in use.

In addition to IUnknown, every COM object that can be automated implements IDispatch because it is IDispatch that gives a client access to the object's properties and methods. The IDispatch interface provides the means for automating COM objects using only four functions.

To begin the Automation process, a client creates an instance of the Automation server by making a call to ::CoCreateInstance. With ::CoCreateInstance, you provide a CLSID for the Automation server and make a request for the IUnknown interface. (Note that you can determine the CLSID from the ProgID at run time with ::CLSIDFromProgID.) Once the pointer to IUnknown is received, the client can then make the call to IUnknown::QueryInterface for a pointer to the object's IDispatch interface. The following code illustrates how an Automation client can create a new instance of Microsoft Word and obtain an IDispatch pointer to Word's Application object:

// Get the CLSID for Word's Application Object
CLSID clsid;
CLSIDFromProgID(L"Word.Application", &clsid);  

// Create an instance of the Word application and obtain the pointer
// to the application's IUnknown interface
IUnknown* pUnk;
HRESULT hr = ::CoCreateInstance( clsid,
                                 NULL,
                                 CLSCTX_SERVER,
                                 IID_IUnknown,
                                 (void**) &pUnk);

// Query IUnknown to retrieve a pointer to the IDispatch interface
IDispatch* pDispApp;
hr = pUnk->QueryInterface(IID_IDispatch, (void**)&pDispApp);

Once the client has a pointer to the object's IDispatch interface, it can begin the work of calling the object's exposed methods and properties. To call methods and properties, it needs their corresponding DISPIDs. The Automation client can call IDispatch::GetIDsOfNames to retrieve a DISPID for a function of the object. Then, with the DISPID in hand, the client can use IDispatch::Invoke to invoke the method or property.

Now consider the IDispatch interface in terms of a C/C++ Automation client that automates Word to create a document similar to that of your Visual Basic Automation client. Figure 7 represents how this Automation client might use the IDispatch interface for the Automation server:

Figure 7. A representation of the COM objects that an Automation client might access for Microsoft Word

The Automation client represented in Figure 7:

• Calls ::CoCreateInstance to create a new instance of Microsoft Word and obtains a pointer to the Application object's IUnknown interface, pUnk.
• Obtains a pointer to the Application's object's IDispatch interface through a call to IUnknown::QueryInterface. This pointer is pDispApp.
• Calls IDispatch::GetIDsOfNames on pDispApp to acquire the DISPID of the Application's Documents property and receives the DISPID 0x6.
• Calls IDispatch::Invoke with the DISPID 0x6 to get the Documents property. The call to get the Documents property returns a pointer to IDispatch for the Documents collection. This pointer is pDispDocs.
• Calls IDispatch::GetIDsOfNames on pDispDocs to acquire the DISPID of the Documents' Add method and receives the DISPID 0xb.
• Calls IDispatch::Invoke with the DISPID 0xb to execute the Add method so that a new document is added in Microsoft Word.
• Continues in this same manner, making calls to pairs of IDispatch::GetIDsOfNames and IDispatch::Invoke, until the automated task is complete.

When IDispatch::Invoke is called upon to invoke a method or property, it also passes on parameters for the invoked method or property and receives its return value, if a value is returned. As you can see, IDispatch::Invoke really does most of the work in this process and rightfully deserves a little extra attention.

HRESULT Invoke( 
  DISPID  dispIdMember,         // DISPID for the member function  
  REFIID  riid,                 // Reserved, must be IID_NULL    
  LCID  lcid,                   // Locale
  WORD  wFlags,                 // Flags describing the call's context
  DISPPARAMS FAR*  pDispParams, // Structure containing the arguments
  VARIANT FAR*  pVarResult,     // Return Value of invoked call
  EXCEPINFO FAR*  pExcepInfo,   // Error information
  unsigned int FAR*  puArgErr   // Indicates which argument causes error
);

Examine the arguments for IDispatch::Invoke in more detail:

• dispIDMember is the DISPID of the method or property you want to invoke.

• riid is reserved and must be IID_NULL.

• lcid is the locale context and can be used to allow the object to interpret the call specific to a locale.

• wFlags indicates the type of member function you're invoking; are you executing a method, getting a property, or setting a property? The wFlags parameter can contain the following:

  DISPATCH_METHOD	Executes a method
  DISPATCH_PROPERTYGET	Gets a property
  DISPATCH_PROPERTYPUT	Sets a property
  DISPATCH_PROPERTYPUTREF	Sets a property by a reference assignment rather than a value assignment

• pDispParams is a pointer to a DISPPARAMS structure; a DISPPARAMS is a single "package" that represents all of the parameters to pass to the method or property you're invoking. Each parameter represented by a DISPPARAMS structure is type VARIANT. (VARIANTs are discussed in greater detail later in this section).

• pVarResult is a pointer to another VARIANT type and represents the value returned from the invoked property or method.

• pExcepInfo is a pointer to an EXCEPINFO structure that contains exception information.

• puArgErr also contains error information. If an error occurs due to one of the parameters you passed to the invoked method or property, puArgErr identifies the offending parameter.

The simplest use of IDispatch::Invoke is to call a method that has no parameters and does not return a value. Now, once again consider the IDispatch interface in terms of your Automation client for Microsoft Word. As you might recall, the TypeParagraph method of the Selection object is one such method that had no return value and no arguments. Given the IDispatch pointer to the Selection object, you could invoke the TypeParagraph method with the following code:

// pDispSel represents a pointer to the IDispatch interface of the
// Selection object.

DISIPD dispid;
DISPPARAMS dispparamsNoArgs = {NULL, NULL, 0, 0};
HRESULT hr;
OLECHAR FAR* szFunction;
szFunction = OLESTR("TypeParagraph");

hr = pDispSel->GetIDsOfNames (IID_NULL, &szFunction, 1, 
                              LOCALE_USER_DEFAULT, &dispid);

hr = pDispSel->Invoke (dispid, IID_NULL, LOCALE_USER_DEFAULT,
                       DISPATCH_METHOD, &dispparamsNoArgs, NULL, NULL, 
                       NULL);

Once again, this is the simplest form of IDispatch::Invoke; the method does not have any arguments and does not return a result, so all pDispParams needs is an empty DISPPARAMS structure. When you start packaging up VARIANTs for parameters, your code can start to become rather lengthy.

Before getting into sample code that uses DISPPARAMS, a discussion about VARIANTs is warranted.

Passing Parameters and Receiving Return Values

A VARIANT is a multi-purpose data type that is used with IDispatch::Invoke to both pass arguments and return values because it is considered safe. VARIANT is a C structure that contains a member vt ** that specifies what type of data the VARIANT represents. The member vt can contain a wide variety of type codes. You set or retrieve the actual data from the VARIANT using the VARIANT's member that corresponds to the type code in vt. The table below outlines the most commonly used type codes for vt and the VARIANT member corresponding to that type code.

Note   The full definition of the VARIANT structure is in Oaidl.h.

VT_I2

short iVal

VT_I4

long lVal

VT_R4

float fltVal

VT_R8

Double dblVal

VT_DATE

DATE date

VT_CY

CY vtCy

VT_BSTR

BSTR bstrVal

VT_ARRAY 

SAFEARRAY* parray

VT_DISPATCH

IDispatch* pdispVal

VT_ERROR

SCODE scode

If vt contained the type code VT_I4, which represents a long integer value, you would query the VARIANT's lVal ** member for the data. Likewise, if vt contained the type code VT_R4 to represent a 4-byte float value, you would query the VARIANT's fltVal member for the data. Observe how this might look in code:

// Long integer with value 9999
VARIANT vLongInt;
vLongInt.vt = VT_I4;
vLongInt.lVal = 9999;

// Float with value 5.3
VARIANT vFloat;
vFloat.vt = VT_R4;
vFloat.fltVal = 5.3;

You might remember DISPPARAMS in the discussion of IDispatch::Invoke. To use a VARIANT as an argument to an invoked method or property, the VARIANT argument is added to an array, which is then referenced by a DISPPARAMS structure. The arguments should be packaged into the array in reverse order. To use the two VARIANTs that were just illustrated as arguments for an invoked method, you can modify the code as shown below:

VARIANT Args[2];

//Long integer with value 9999
Args[0].vt = VT_I4;
Args[0].lVal = 9999;

//Float with value 5.3
Args[1].vt = VT_R4;
Args[1].vt = 5.3;

DISPPARAMS dp;
dp.cArgs = 2;
dp.rgvarg = Args;
dp.cNamedArgs = 0;

Note   Do not forget to add the arguments to the array in reverse order. If you do not, then the invoked method fails or, at best, succeeds but gives you results that you do not expect.

In the previous code snippet, you could use your DISPPARAMS dp to invoke a method that has two arguments. Suppose that the method's first parameter expects a type Float and the second parameter expects a Long Integer. You would add the Long Integer as the first element to the array and the Float as the second element of the array.

For the most part, retrieving and setting data with VARIANTs is straightforward. However, there are two data types that deserve special mention because they are very frequently used in Automation and they require a little more work: BSTRs and SAFEARRAYs.

Using the BSTR string type

To represent a string with a VARIANT, use the VT_BSTR type code, which corresponds to a BSTR type string. Automation uses BSTRs (or Basic STRings) so that Automation clients and servers can be created with Visual Basic. Visual Basic stores strings in the BSTR format:

Figure 8. The BSTR (Basic STRing) format

A BSTR variable is a pointer to the zero-terminated character string, so it is essentially the same as a wchar ** pointer. However, because of the allocated memory for the character count, you should always allocate and de-allocate the BSTR properly. Windows provides the ::SysAllocString and ::SysFreeString functions for this purpose:

// Allocate a new BSTR
BSTR bstrMsg;
bstrMsg = ::SysAllocString(OLESTR("Hello"));

//Create a VARIANT of type VT_BSTR for the new string
VARIANT v;
v.vt = VT_BSTR;
v.bstrVal = bstrMsg;

// . . . Do something with the string data . . .

// Deallocate the BSTR
::SysFreeString(bstrMsg);

Using SAFEARRAYs

A SAFEARRAY is an array of various data types and is considered safe because the array is a protected structure that contains boundary information as well as references to the actual data.

A SAFEARRAY can have type codes similar to those of VARIANTs. You can create an array where all of the elements are of the same type, such as VT_I4 or VT_BSTR, or you can have an array that contains a variety of data types by creating a safe array of VT_VARIANT. To create a SAFEARRAY, you first set up a SAFEARRAYBOUND structure that contains information about the lower bounds and number of elements for each dimension of the array. Then, using this structure for the bounds and dimension information, call ::SafeArrayCreate to create the array and specify the base type for the array. Use ::SafeArrayPutElement to populate the array with data and then use ::SafeArrayGetElement to retrieve the array's data. When you're done with the array, call ::SafeArrayDestroy. The code below illustrates how to create a safe array and manipulate the array's data:

// Create a two dimensional array (2 rows x 5 columns)
SAFEARRAY * sa; 
SAFEARRAYBOUND saDims[2]; 
saDims[0].lLbound = 1;     // Lower bound of the first dimension
saDims[0].cElements = 2;   
saDims[1].lLbound = 1;     // Lower bound of the second dimension
saDims[1].cElements = 5;

sa = ::SafeArrayCreate(VT_I4, 2, saDims);

// Fill the array with data one element at a time
HRESULT hr;
long lIndex[2];
long lNum;
int r, c;

for (r=1; r<=2;r++)
{
   lIndex[0]=r;
   for (c=1;c<=5;c++)
   {
      lIndex[1] = c;
      lNum = c * r;
      hr = ::SafeArrayPutElement(sa, lIndex, &lNum);
   }
}

// Get the data from the array
for (r=1; r<=2;r++)
{
   lIndex[0]=r;
   for (c=1;c<=5;c++)
   {
      lIndex[1] = c;
      hr = ::SafeArrayGetElement(sa, lIndex, &lNum);
      printf("%d\t", lNum);
   }
   printf("\n");
}

// . . . Do something with the array . . . 

// Destroy the array when done
hr = ::SafeArrayDestroy(sa);

Exercise 4: Creating an Automation Client with C/C++

1. Start a new Win32® Console Application project. When prompted to select a type of console application project, choose Empty Project.
2. Add a new C++ source file to the project and copy the following code into the source file:

#include <ole2.h>
#include <stdio.h>

int main(int argc, char* argv[])
{

    // *************** Declare Some Variables ********************

    // Variables that will be used and re-used in our calls
    DISPPARAMS dpNoArgs = {NULL, NULL, 0, 0};
    VARIANT vResult;
    OLECHAR FAR* szFunction;
    BSTR bstrTemp;

    // IDispatch pointers for Word's objects
    IDispatch* pDispDocs;      //Documents collection
    IDispatch* pDispSel;       //Selection object
    IDispatch* pDispActiveDoc; //ActiveDocument object

    // DISPIDs
    DISPID dispid_Docs;        //Documents property of Application 
                               //object
    DISPID dispid_DocsAdd;     //Add method of Documents collection 
                               //object
    DISPID dispid_Sel;         //Selection property of Application 
                               //object
    DISPID dispid_TypeText;    //TypeText method of Selection object
    DISPID dispid_TypePara;    //TypeParagraph method of Selection 
                               //object
    DISPID dispid_ActiveDoc;   //ActiveDocument property of 
                               //Application object
    DISPID dispid_SaveAs;      //SaveAs method of the Document object
    DISPID dispid_Quit;        //Quit method of the Application 
                               //object

    // ******************** Start Automation ***********************

    //Initialize the COM libraries
    ::CoInitialize(NULL);

    // Create an instance of the Word application and obtain the 
    // pointer to the application's IDispatch interface.
    CLSID clsid;
    CLSIDFromProgID(L"Word.Application", &clsid);  

    IUnknown* pUnk;
    HRESULT hr = ::CoCreateInstance( clsid, NULL, CLSCTX_SERVER,
                                     IID_IUnknown, (void**) &pUnk);
    IDispatch* pDispApp;
    hr = pUnk->QueryInterface(IID_IDispatch, (void**)&pDispApp);

    // Get IDispatch* for the Documents collection object
    szFunction = OLESTR("Documents");
    hr = pDispApp->GetIDsOfNames (IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, &dispid_Docs);
    hr = pDispApp->Invoke (dispid_Docs, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, 
                           &dpNoArgs, &vResult, NULL, NULL);
    pDispDocs = vResult.pdispVal;

    // Invoke the Add method on the Documents collection object
    // to create a new document in Word
    // Note that the Add method can take up to 3 arguments, all of 
    // which are optional. You are not passing it any so you are 
    // using an empty DISPPARAMS structure
    szFunction = OLESTR("Add");
    hr = pDispDocs->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, 
                                  &dispid_DocsAdd);
    hr = pDispDocs->Invoke(dispid_DocsAdd, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpNoArgs, &vResult, NULL, NULL);

    // Get IDispatch* for the Selection object
    szFunction = OLESTR("Selection");
    hr = pDispApp->GetIDsOfNames (IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, &dispid_Sel);
    hr = pDispApp->Invoke (dispid_Sel, IID_NULL, LOCALE_USER_DEFAULT,
                           DISPATCH_PROPERTYGET, &dpNoArgs, &vResult, 
                           NULL, NULL);
    pDispSel = vResult.pdispVal;


    // Get the DISPIDs of the TypeText and TypeParagraph methods of 
    // the Selection object.  You'll use these DISPIDs multiple
    // times.
    szFunction = OLESTR("TypeText");
    hr = pDispSel->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, 
                                  &dispid_TypeText);

    szFunction = OLESTR("TypeParagraph");
    hr = pDispSel->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, 
                                  &dispid_TypePara);

    // The TypeText method has and requires only one argument, a 
    // string, so set up the DISPPARAMS accordingly
    VARIANT vArgsTypeText[1];
    DISPPARAMS dpTypeText;

    bstrTemp = ::SysAllocString(OLESTR("One"));
    vArgsTypeText [0].vt = VT_BSTR;
    vArgsTypeText [0].bstrVal = bstrTemp;
    dpTypeText.cArgs = 1;
    dpTypeText.cNamedArgs = 0;
    dpTypeText.rgvarg = vArgsTypeText;

    //Invoke the first TypeText and TypeParagraph pair
    hr = pDispSel->Invoke (dispid_TypeText, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpTypeText, NULL, NULL, NULL);
    hr = pDispSel->Invoke (dispid_TypePara, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpNoArgs, NULL, NULL, NULL);
    ::SysFreeString(bstrTemp);

    //Invoke the second TypeText and TypeParagraph pair
    bstrTemp = ::SysAllocString(OLESTR("Two"));
    hr = pDispSel->Invoke (dispid_TypeText, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpTypeText, NULL, NULL, NULL);
    hr = pDispSel->Invoke (dispid_TypePara, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpNoArgs, NULL, NULL, NULL);
    ::SysFreeString(bstrTemp);

    //Invoke the third TypeText and TypeParagraph pair
    bstrTemp = ::SysAllocString(OLESTR("Three"));
    hr = pDispSel->Invoke (dispid_TypeText, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpTypeText, NULL, NULL, NULL);
    hr = pDispSel->Invoke (dispid_TypePara, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                           &dpNoArgs, NULL, NULL, NULL);
    ::SysFreeString(bstrTemp);

    // Get IDispatch* for the ActiveDocument object
    szFunction = OLESTR("ActiveDocument");
    hr = pDispApp->GetIDsOfNames (IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, 
                                  &dispid_ActiveDoc);
    hr = pDispApp->Invoke (dispid_ActiveDoc, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, 
                           &dpNoArgs, &vResult, NULL, NULL);
    pDispActiveDoc = vResult.pdispVal;

    //Set up the DISPPARAMS for the SaveAs method (11 arguments)
    VARIANT vArgsSaveAs[11];
    DISPPARAMS dpSaveAs;
    dpSaveAs.cArgs = 11;
    dpSaveAs.cNamedArgs = 0;
    dpSaveAs.rgvarg = vArgsSaveAs;

    BSTR bstrEmptyString;
    bstrEmptyString = ::SysAllocString(OLESTR(""));

    VARIANT vFalse;
    vFalse.vt = VT_BOOL;
    vFalse.boolVal = FALSE;

    bstrTemp = ::SysAllocString(OLESTR("c:\\doc1.doc"));
    vArgsSaveAs[10].vt = VT_BSTR;         
    vArgsSaveAs[10].bstrVal = bstrTemp;        //Filename
    vArgsSaveAs[9].vt = VT_I4;            
    vArgsSaveAs[9].lVal = 0;                   //FileFormat
    vArgsSaveAs[8] = vFalse;                   //LockComments
    vArgsSaveAs[7].vt = VT_BSTR;
    vArgsSaveAs[7].bstrVal = bstrEmptyString;  //Password
    vArgsSaveAs[6].vt = VT_BOOL;      
    vArgsSaveAs[6].boolVal = TRUE;             //AddToRecentFiles
    vArgsSaveAs[5].vt = VT_BSTR;
    vArgsSaveAs[5].bstrVal = bstrEmptyString;  //WritePassword
    vArgsSaveAs[4] = vFalse;                   //ReadOnlyRecommended
    vArgsSaveAs[3] = vFalse;                   //EmbedTrueTypeFonts
    vArgsSaveAs[2] = vFalse;                //SaveNativePictureFormat
    vArgsSaveAs[1] = vFalse;                   //SaveFormsData
    vArgsSaveAs[0] = vFalse;                   //SaveAsOCELetter


    //Invoke the SaveAs method
    szFunction = OLESTR("SaveAs");
    hr = pDispActiveDoc->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, 
                                  &dispid_SaveAs);
    hr = pDispActiveDoc->Invoke(dispid_SaveAs, IID_NULL, 
                                LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                                &dpSaveAs, NULL, NULL, NULL);
    ::SysFreeString(bstrEmptyString);

    //Invoke the Quit method
    szFunction = OLESTR("Quit");
    hr = pDispApp->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                                 LOCALE_USER_DEFAULT, &dispid_Quit);
    hr = pDispApp->Invoke (dispid_Quit, IID_NULL, 
                           LOCALE_USER_DEFAULT, DISPATCH_METHOD,
                           &dpNoArgs, NULL, NULL, NULL);

    //Clean-up
    ::SysFreeString(bstrTemp);
    pDispActiveDoc->Release();
    pDispSel->Release();
    pDispDocs->Release();
    pDispApp->Release();
    pUnk->Release();

    ::CoUninitialize();

    return 0;

}

1. Build and run the application. When the application ends, examine the document C:\doc1.doc that the Automation code created.

Please note that this Automation code could be streamlined somewhat so that the code is shorter. There are other improvements, based on the Word object model, that you can implement. However, the goal of this example is to show you the one-to-one correlation between the macro you originally recorded and its equivalent C++ Automation code. How to improve upon your Automation code is dealt with in the section "Improving the Performance of Your Automation Code" later in this article. For now, consider some important points about this code sample that can help you when writing your own Automation code:

• In the sample, you first called ::CoCreateInstance and IUnknown::QueryInterface to obtain the IDispatch pointer to the Application object of Microsoft Word. Given the IDispatch pointer for the Application, you were then able to acquire IDispatch pointers to other interfaces by calling properties of the Application that return objects or object collections. Typically, this is the technique you use for navigating the object model with C++. To illustrate, you call the Selection property with the context DISPATCH_PROPERTYGET to obtain the IDispatch pointer for the Selection object. Given this IDispatch pointer, you are then able to call methods on the Selection object such as TypeText and TypeParagraph.
• Note that you use an empty DISPPARAMS (dpNoArgs) when you invoke a method or property where there are no arguments to pass. For example, you invoke the Selection property of the Application object in the context DISPATCH_PROPERTYGET with an empty DISPPARAMS because this property has no arguments. Note that you also invoke the Add method on the Documents object with an empty DISPPARAMS. If you examine the documentation for the Add method, you find that the Add method does have three optional arguments but, because you were not providing any, you can use the empty DISPPARAMS.
• You pass the arguments for the SaveAs method of the Document object in an order that is reversed from the documentation. In the documentation for the SaveAs method, the FileName argument is the first of eleven arguments, but when you set up the VARIANT array for the DISPPARAMS structure, you specify the Filename as the eleventh element (index 10) of the array. When you are using multiple arguments with an invoked function, always pass them in reverse order.

Passing Optional Arguments to Methods

One thing you can do to streamline the code in the previous exercise is to pass only those arguments that you really need for invoked functions and omit some of the optional arguments, especially for the SaveAs method, which has eleven arguments.

In the exploration of the object model documentation, you'll recall that some methods of Automation objects have arguments that are optional. When you need to omit an optional argument for a method and you cannot leave it blank, you can pass a VARIANT of type VT_ERROR with its scode member set to DISP_E_PARAMNOTFOUND.

The SaveAs method of Word's Document object is one such method; only the first argument, the Filename argument, is required and all other arguments are optional. Revisit your Automation code from the previous exercise and rewrite the call to invoke the SaveAs method so that you pass only the arguments that you need to provide; any arguments that you omit take on the default values as specified in the documentation for the SaveAs method. Suppose that you wanted to provide values for only two arguments: the Filename argument (the first argument) and the Password argument (the fourth argument). In VBA syntax, this would look like:

ActiveDocument.SaveAs "c:\doc1.doc", , ,"pwd"

This call to SaveAs specifies only the first and fourth of the eleven arguments and omits all the others. With C++, you can accomplish the same behavior by creating a four-element VARIANT array where element 3 is type VT_BSTR for the filename, elements 2 and 1 are type VT_ERROR with an scode of DISP_E_PARAMNOTFOUND for "omitted," and element 0 is type VT_BSTR for the password (remember that you set up the VARIANT array with the arguments in reverse order):

    //Set up the DISPPARAMS for the SaveAs method, using only 4 of its 11 
    //possible arguments
    VARIANT vArgsSaveAs[4];
    DISPPARAMS dpSaveAs;
    dpSaveAs.cArgs = 4;
    dpSaveAs.cNamedArgs = 0;
    dpSaveAs.rgvarg = vArgsSaveAs;

    BSTR bstrPassword;
    bstrPassword = ::SysAllocString(OLESTR("pwd"));

    VARIANT vOpt;
    vOpt.vt = VT_ERROR;
    vOpt.scode = DISP_E_PARAMNOTFOUND;

    bstrTemp = ::SysAllocString(OLESTR("c:\\doc1.doc"));
    vArgsSaveAs[3].vt = VT_BSTR;         
    vArgsSaveAs[3].bstrVal = bstrTemp;       //Filename
    vArgsSaveAs[2] = vOpt;                   //FileFormat - omitted
    vArgsSaveAs[1] = vOpt;                   //LockComments - omitted
    vArgsSaveAs[0].vt = VT_BSTR;
    vArgsSaveAs[0].bstrVal = bstrPassword;   //Password


    //Invoke the SaveAs method
    szFunction = OLESTR("SaveAs");
    hr = pDispActiveDoc->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                                  LOCALE_USER_DEFAULT, &dispid_SaveAs);
    hr = pDispActiveDoc->Invoke(dispid_SaveAs, IID_NULL, 
                                LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                                &dpSaveAs, NULL, NULL, NULL);
    ::SysFreeString(bstrPassword);

Special Case for Property Put Functions

For methods and property get functions, all arguments can be accessed as positional without the need for named arguments. On the other hand, property put functions require named arguments because they have a named argument that is the new value for the property you are setting. The DispID of this argument is DISPID_PROPERTYPUT.

To illustrate, revisit your sample C++ Automation Client in Exercise 4. Suppose that you want to make the Word application visible instead of quitting it after the document is saved. To do this, you can set the Application's object Visible property to True by invoking the Visible property in the DISPATCH_PROPERTYPUT context. In your sample C++ Automation client, you can replace the invocation of the Quit method with the following code to make Word visible:

//Make Word Visible
DISPID dispid_Visible;
szFunction = OLESTR("Visible");
hr = pDispApp->GetIDsOfNames(IID_NULL, &szFunction, 1, 
                             LOCALE_USER_DEFAULT, &dispid_Visible);
VARIANT pArgs2[1];
DISPPARAMS dpVisible;
pArgs2[0].vt= VT_BOOL;
pArgs2[0].boolVal = TRUE;
dpVisible.cArgs = 1;
dpVisible.cNamedArgs = 1;
dpVisible.rgvarg = pArgs2;
DISPID dispidNamed = DISPID_PROPERTYPUT;
dpVisible.rgdispidNamedArgs = &dispidNamed;

hr = pDispApp->Invoke (dispid_Visible, IID_NULL, LOCALE_USER_DEFAULT,
                       DISPATCH_PROPERTYPUT, &dpVisible, NULL, NULL, 
                       NULL);

Note that for the DISPPARAMS structure, you set the cNamedArgs member to 1 and the rgdispidNamedArgs member to a DispID of DISPID_PROPERTYPUT. If you attempt to call the property put function (the Visible property) without specifying the named argument, IDispatch::Invoke would fail with an HRESULT 0x80020004 "Parameter not found."

Error Handling

With COM, there are two general types of errors that can occur:

• An error can occur with a call because COM could not make it work.

  -or-

• An error can occur on the server's end in a function call.

These errors are described in HRESULT and EXCEPINFO structures, respectively.

An HRESULT is a 32-bit return value that represents success or failure. When a call fails, the HRESULT contains an error value that provides you with a general idea of the cause. For example, if you try to pass a parameter to an invoked function and the parameter cannot be coerced to the correct type, you receive an HRESULT with the value of DISP_E_TYPEMISMATCH. Or, if you call IDispatch::GetIDsOfNames to obtain the DISPID for a function that an object does not support, you get an HRESULT of DISP_E_MEMBERNOTFOUND. You can call ::FormatMessage to retrieve a textual description for the error.

Note   For a complete list of HRESULT values and their descriptions, refer to the documentation for IDispatch::Invoke on MSDN.

If IDispatch::Invoke returns an HRESULT of DISP_E_EXCEPTION, this indicates that an error occurred in the actual execution of the invoked function on the server's end. You can examine the EXCEPINFO structure you passed to IDispatch::Invoke for more details on the error. The EXCEPINFO structure contains a numeric error code, a textual error description, and information about how you can find the Help topic for the error.

Exercise 5: Implementing an Error Handler for Your Client

In this exercise, you implement an error handling routine for the Automation client you created in Exercise 4 and intentionally break some of the code so that you can see what types of errors can occur:

1. Add the following error routine to the CPP file you created for the Automation client in the previous exercise:

   void ErrHandler(HRESULT hr, EXCEPINFO excep)
   {
       if(hr==DISP_E_EXCEPTION)
       {
           char errDesc[512];
           char errMsg[512];
           wcstombs(errDesc, excep.bstrDescription, 512);
           sprintf(errMsg, "Run-time error %d:\n\n %s", 
                   excep.scode & 0x0000FFFF,  //Lower 16-bits of SCODE
                   errDesc);                  //Text error description
           ::MessageBox(NULL, errMsg, "Server Error", MB_SETFOREGROUND | 
                        MB_OK);
       }
       else
       {
           LPVOID lpMsgBuf;
           ::FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER | 
                           FORMAT_MESSAGE_FROM_SYSTEM | 
                           FORMAT_MESSAGE_IGNORE_INSERTS, NULL, hr,
                           MAKELANGID(LANG_NEUTRAL, 
                           SUBLANG_DEFAULT),(LPTSTR) &lpMsgBuf,
                           0, NULL);
           ::MessageBox(NULL, (LPCTSTR)lpMsgBuf, "COM Error", 
                        MB_OK | MB_SETFOREGROUND);
           ::LocalFree( lpMsgBuf );
       }
   
   }
   

2. Modify the call to invoke the SaveAs method of the Document object to implement error handling by changing this line:

    hr = pDispActiveDoc->Invoke(dispid_SaveAs, IID_NULL, 
                                LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                                &dpSaveAs, NULL, NULL, NULL); 
   

   to this line:

       EXCEPINFO excep;
       hr = pDispActiveDoc->Invoke(dispid_SaveAs, IID_NULL, 
                                   LOCALE_USER_DEFAULT, DISPATCH_METHOD, 
                                   &dpSaveAs, NULL, &excep, NULL); 
       if (FAILED(hr))
       {
           ErrHandler(hr, excep);
       }
   

3. Compile and run the application. Everything should work as expected without error and the error handling routine should not be called.

4. Break the code to see the error handler in action. Change the Filename argument for the SaveAs method to an incorrect type, VT_I4:

   pArgsSaveAs[10].vt = VT_I4;
   

   This causes a COM failure because the call to IDispatch::Invoke fails when trying to coerce the long integer data type to a string, which is what SaveAs expects for the Filename argument. Build and run the application. IDispatch::Invoke returns DISP_E_TYPEMISMATCH as you expected and the error handler displays the error:

   

5. Change the type code for the Filename argument back to VT_BSTR so that the type code is correct but change the string for the Filename argument so that the path is not valid:

   bstrTemp = ::SysAllocString(OLESTR("c:\\badpath\\doc1.doc")); 
   

   In this scenario, IDispatch::Invoke can call the SaveAs method with the proper argument types but the SaveAs function itself fails because of the invalid path. Build and run the application. IDispatch::Invoke returns DISP_E_EXCEPTION to indicate that the server reported an error with the function execution. The error handler routine examines the EXCEPINFO structure passed to IDispatch::Invoke and displays a descriptive error message:

   

Automate a Running Instance of an Office Application

Automation servers that are running register their objects in what is called the Running Object Table (or ROT). To attach to a running instance of an Automation server, you can provide the object's CLSID to ::GetActiveObject() and obtain a pointer to the object's IUnknown interface. Given the pointer to IUnknown, you can call IUnknown::QueryInterface to get to the object's IDispatch interface. The following sample illustrates this behavior:

// Get a pointer to IUnknown for the running instance
HRESULT hr;
IUnknown *pUnk;
CLSID clsid;
CLSIDFromProgID(L"Word.Application", &clsid);  
hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);

// Get IDispatch 
IDispatch *pDisp;
hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pDisp);

// . . . Use the IDispatch pointer for automation . . .

// Release interface pointers for proper clean-up
pDisp->Release();
pUnk->Release();

There are special considerations you should note when using ::GetActiveObject(). If there are multiple running instances of the Office application you want to control, ::GetActiveObject() returns the pointer to the IUnknown interface of the instance that was first running. There is no foolproof solution for attaching to a specific instance of an application.

An Office application does not register its Application object if another instance is already in the ROT because the moniker for it is always the same. However, because the Office applications also register their documents in the ROT, you can attach to instances other than the one that was first running by:

1. Iterating the ROT to locate a specific open document.
2. Attaching to the open document.
3. Navigating the object model for the Application object.

Note that this solution is still not foolproof because there is no guarantee that a document is not open in two separate instances of an application. If you are interested in a code example that illustrates how to iterate the ROT to locate an open document by name, see the following article in the Microsoft Knowledge Base:

Q190985 HOWTO: Get IDispatch of an Excel or Word Document From an OCX

Creating an Automation Client with MFC

Now that you have created your Automation client with straight C++, you can see how MFC simplifies this task for you and cuts down the amount of code you write.

The COleDispatchDriver Class

MFC provides a COleDispatchDriver class for handling IDispatch interfaces of Automation objects. You can use the Visual C++ ClassWizard to automatically generate wrapper classes from a Microsoft Office type library. All the classes in the type library that implement IDispatch are wrapped into a new class that is derived from COleDispatchDriver. The ClassWizard also creates member functions that map to all the properties and methods that a class exposes.

These wrapper classes provide several benefits to the MFC programmer:

• ClassWizard sets up the call to invoke the function and provides the DISPID, context, parameters, and return value. Thus, you rarely need to make COM calls directly to retrieve function DISPIDs or to invoke functions.
• COleDispatchDriver handles reference counts.
• ClassWizard generates a member function in such a way that you can pass the function arguments without building a DISPPARAMS structure.
• The COleVariant class encapsulates the VARIANT data type. With MFC, you can use COleVariants for your parameters and return values for the wrapped functions.

COleDispatchDriver supports several functions for communicating with Automation servers. The following list describes the ones you are most likely to use.

COleDispatchDriver also has a data member m_lpDispatch, which, as its name implies, is the pointer to the IDispatch interface and is type LPDISPATCH. This data member is often useful when you need to package a VARIANT of type VT_DISPATCH as an argument to an invoked function.

Exercise 6: Creating an automation client with MFC

In this exercise, you will create a MFC Automation client with the same functionality as your C/C++ Automation client. As you progress through the exercise, you might notice that the code has a much clearer one-to-one correlation with the recorded Word macro. This is the attraction of writing Automation code with MFC and the wrapper classes that ClassWizard generates for you.

1. In Visual Studio, start a new dialog-based MFC AppWizard(EXE) project named Exercise6.

2. On the View menu, click ClassWizard, and go to the Automation tab. Click Add Class and choose From A Type Library. Browse to the Microsoft Word 97 type library Msword8.olb (or Msword9.olb if you are using Word 2000) and click OK. Select all of the classes the ClassWizard presents and click OK.

   

   Figure 9. Use the ClassWizard to create wrapper functions from a type library.

This process generates two new files in your project: Msword8.cpp and Msword8.h. These files make up the wrapper classes for all the classes and class member functions that the Word type library exposes. Next, examine one of the properties that you will use in your code: the Documents property. As you might recall from your examination of the Object model documentation, Documents is a property of the Application object and returns a Documents collection object that represents all the open documents.

1. On the Project Workspace window, click the ClassView tab. Expand "Exercise6 Classes" to display all the classes that the ClassWizard generated from the Word type library. Double-click the _Application class to display its definition in the Text Editor window. Note that the _Application class is derived from COleDispatchDriver:

   // _Application wrapper class
   
   class _Application : public COleDispatchDriver
   {
   
   . . .
   
   }
   

2. Expand the _Application class on the ClassView tab to display all of its member functions. Double-click GetDocuments() to display its definition in the Text Editor window:

   LPDISPATCH _Application::GetDocuments()
   {
      LPDISPATCH result;
      InvokeHelper(0x6, DISPATCH_PROPERTYGET, VT_DISPATCH, (void*)&result, 
                   NULL);
      return result;
   }
   

The definition of Application::GetDocuments tells you that it makes a call to COleDispatchDriver::InvokeHelper to invoke the Documents property (DISPATCH_PROPERTYGET) using the DISPID 0x6. _Application::GetDocuments() returns an IDispatch pointer to the Documents class.

Note   In VBA, this function is called the Documents property, but the ClassWizard names this function GetDocuments. The ClassWizard names a function based on the context in which the function is invoked.

With ClassView, examine the equivalent member functions for all the objects, properties, and methods that the recorded Word macro used. Compare the member function to its VBA equivalent in the Object Model documentation and you can see a definite correlation. The documentation provides you with a description of the function's purpose, a description of each argument, and the function's return value.

1. Add the following code to CExercise6App::InitInstance in Exercise6.cpp so that COM services are enabled when your application is initialized:

   if(!AfxOleInit()) 
   {
      AfxMessageBox("Could not initialize COM services");
      return FALSE;
   }
   

   Next, select the dialog resource IDD_EXERCISE6_DIALOG. Add a button to the dialog box and name the button IDC_RUN. Add the following code to the button handler:

       COleVariant vTrue((short)TRUE),
                   vFalse((short)FALSE),
                   vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   
       //Start a new instance of Microsoft Word
       _Application oWordApp;
       if (!oWordApp.CreateDispatch("Word.Application", NULL))
       {
           AfxMessageBox("CreateDispatch failed.", MB_OK | MB_SETFOREGROUND);
           return;
       }
   
       //Create a new document
       Documents oDocs;
       _Document oDoc;
       oDocs = oWordApp.GetDocuments();
       oDoc = oDocs.Add(vOpt, vOpt);  
       //Note for Word 2000: The Add method has 4 arguments in Word 2000.  If 
       //you wrapped the classes from the Word type library (msword9.olb),
       //modify the Add method to provide 4 optional arguments:
       //    oDoc = oDocs.Add(vOpt, vOpt, vOpt, vOpt);  
   
       //Add text to the document
       Selection oSel;
       oSel = oWordApp.GetSelection();
       oSel.TypeText("one");
       oSel.TypeParagraph();
       oSel.TypeText("two");
       oSel.TypeParagraph();
       oSel.TypeText("three");
   
       //Save the document
       _Document oActiveDoc; 
       oActiveDoc = oWordApp.GetActiveDocument();
       oActiveDoc.SaveAs(COleVariant("c:\\doc1.doc"), 
                       COleVariant((short)0),
                       vFalse, COleVariant(""), vTrue, COleVariant(""),
                       vFalse, vFalse, vFalse, vFalse, vFalse);
   
       //Quit the application
       oWordApp.Quit(vOpt, vOpt, vOpt);
   

   Note   The Documents::Add method in the type library for Microsoft Word 2000 has four arguments, whereas Microsoft Word 97 only has two. If you added wrappers using the Word 2000 type library (Msword9.olb) in step 2, modify the Add method in the code so that it has four arguments, for example:

   oDoc = oDocs.Add(vOpt, vOpt, vOpt, vOpt);
   

2. Add the directive to include Msword8.h in Exercise6Dlg.cpp:

   #include "msword8.h"   //or "msword9.h" for Word 2000
   

   Note   Add this include directive after the include directive for "stdafx.h." Otherwise, you might receive compile errors.

3. Build and run the application. Click the button to run the Automation code. Confirm that your Automation client created C:\Doc1.doc and that the document contains the text that you expect.

There are some important points to observe about the Automation code in this exercise:

• When you call a member function for a wrapper class, you must provide all arguments to the function. You can omit optional arguments by passing a COleVariant with the type code VT_ERROR and the scode DISP_E_PARAMNOTFOUND.

• When calling member functions that return a pointer to an IDispatch interface (type LPDISPATCH), you can directly assign the return value to a COleDispatchDriver derived class to attach it to the returned IDispatch pointer:

      Documents oDocs;
      _Document oDoc;
      oDocs = oWordApp.GetDocuments();
      oDoc = oDocs.Add(vOpt, vOpt);
  

  This is essentially the same as:

      Documents oDocs;
      _Document oDoc;
      oDocs.AttachDispatch(oWordApp.GetDocuments());
      oDoc.AttachDispatch(oDocs.Add(vOpt, vOpt));
  

• You did not directly call COleDispatchDriver::ReleaseDispatch() to release the objects and decrement reference counts. COleDispatchDriver has a data member, m_bAutoRelease, which is set to True when it is constructed. When m_bAutoRelease is True, the object is released when it is destroyed. Because all of the COleDispatchDriver-derived classes in your client have procedure-level scope, the objects are all released automatically when the procedure ends.

• In this particular example, you did not use the Document object Doc that was returned from Documents::Add(), but you assigned the return value anyway. Documents::Add() effectively increases the reference count for the Document object when it requests a pointer to its IDispatch interface. This reference count is properly decremented when the procedure ends and the Document object variable Doc loses scope. If you had ignored the returned LPDISPATCH, you might have encountered a problem with the object's reference count and the server might have remained in memory even after your Automation client ended. You should be aware of return values, especially those of type LPDISPATCH.

Exception Handling

MFC provides two classes for handling exceptions with your Automation code: COleException and COleDispatchException. Both of these classes are derived from the CException base class and can be used with try blocks and catch handlers.

You use COleException to handle general failures with COM calls and COleDispatchException to handle errors that occur on the server's end.

Exercise 7: Implement exception handling in your automation client

This exercise implements exception handling in the MFC Automation client you created in the previous exercise, and then breaks the Automation code so that you can see the exception handler in action.

1. Modify CExercise6Dlg::OnRun to implement exception handling:

   
       //Commonly used variants.
       COleVariant vTrue((short)TRUE),
                   vFalse((short)FALSE),
                   vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   
       //Start a new instance of Microsoft Word
       _Application oWordApp;
       if (!oWordApp.CreateDispatch("Word.Application", NULL))
       {
           AfxMessageBox("CreateDispatch failed.", 
                         MB_OK | MB_SETFOREGROUND);
           return;
       }
   
       try
       {
   
         //Create a new document
         Documents oDocs;
         _Document oDoc;
         oDocs = oWordApp.GetDocuments();
         oDoc = oDocs.Add(vOpt, vOpt);
         //Note for Word 2000: The Add method has 4 arguments in Word 2000.  
         //If you wrapped the classes from the Word type library 
         //(msword9.olb),modify the Add method to provide 4 optional 
         //arguments:
         //    oDoc = oDocs.Add(vOpt, vOpt, vOpt, vOpt);  
   
         //Add text to the document
         Selection oSel;
         oSel = oWordApp.GetSelection();
         oSel.TypeText("one");
         oSel.TypeParagraph();
         oSel.TypeText("two");
         oSel.TypeParagraph();
         oSel.TypeText("three");
   
         //Save the document
         _Document oActiveDoc; 
         oActiveDoc = oWordApp.GetActiveDocument();
         oActiveDoc.SaveAs(COleVariant("c:\\ doc1.doc"), 
                           COleVariant((short)0),
                           vFalse, COleVariant(""), vTrue, COleVariant(""),
                           vFalse, vFalse, vFalse, vFalse, vFalse);
      }
   
      catch(COleException *e)
      {
         LPVOID lpMsg;
         ::FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER | 
                        FORMAT_MESSAGE_FROM_SYSTEM | 
                        FORMAT_MESSAGE_IGNORE_INSERTS, NULL, e->m_sc,
                        MAKELANGID(LANG_NEUTRAL, 
                        SUBLANG_DEFAULT),(LPTSTR) &lpMsg,
                        0, NULL);
         ::MessageBox(NULL, (LPCTSTR)lpMsg, "COM Error", 
                     MB_OK | MB_SETFOREGROUND);
         ::LocalFree( lpMsg );
      }
   
      catch(COleDispatchException *e)
      {
         char msg[512];
         sprintf(msg, "Run-time error '%d':\n\n%s", 
              e->m_scError & 0x0000FFFF, e->m_strDescription);
         ::MessageBox(NULL, msg, "Server Error", 
                     MB_OK | MB_SETFOREGROUND);      
      }
   
   
      //Quit the application
      oWordApp.Quit(vFalse, vOpt, vOpt);
   

2. Build and run the application. Everything should work as expected and no exceptions should occur.

3. Modify the SaveAs method so that you are passing it a COleVariant of type VT_I4 for the first argument, the Filename argument:

      oActiveDoc.SaveAs(COleVariant((long)5)), 
                        COleVariant((short)0),
                        vFalse, COleVariant(""), vTrue, COleVariant(""),
                        vFalse, vFalse, vFalse, vFalse, vFalse);
   

   SaveAs expects a string for the Filename argument, so you expect modifying the SaveAs method to generate an error. Build and run the application. When you click the button, the code generates a COleException when invoking the SaveAs method due to a type mismatch for the Filename argument. The exception handler displays a message box:

   

4. Modify the SaveAs method so that you are passing it a Filename with an invalid path:

      oActiveDoc.SaveAs(COleVariant("c:\\badpath\\doc1.doc"), 
                        COleVariant((short)0),
                        vFalse, COleVariant(""), vTrue, COleVariant(""),
                        vFalse, vFalse, vFalse, vFalse, vFalse);
   

   COM is able to invoke the SaveAs method because, as far as COM is concerned, it has the correct DISPID, the correct argument types, and so forth. However, the SaveAs method itself fails because of the invalid path. Build and run the application. When the SaveAs method is invoked, the method fails and generates a COleDispatchException and the exception handler kicks in to display the error:

   

Note that the server's Quit method is called after the catch handlers. Using this layout, when an exception occurs, the Quit method is still called. If the Quit method were in the try block instead, it might not be invoked if an exception occurs and you could risk leaving the server in memory when the procedure ends.

Improving the Performance of Your Automation Code

Your best tools for writing robust Automation code are:

• A good understanding of the Automation server's object model

  -and-

• The documentation for the objects, methods, and properties you intend to use

The macro recorder in Office applications is an excellent tool for understanding how to accomplish a task with the application's object model and, in some situations, it gives you a great foundation for starting your Automation client. This was illustrated in the previous exercises by translating a recorded Word macro, line-by-line, to create perfectly functional Automation clients.

The macro recorder records actions in the user interface; it records every click and every keystroke you make. Thus, by its very nature, the macro recorder can often generate rather lengthy code that is not as efficient as it could be. To improve a recorded macro, you need an understanding of the application's object model. Obviously this article cannot cover every object in all the Office object models, but it can provide an overview of the more common techniques you can use to improve your Automation code.

Tip #1—Minimize "Selecting" or "Activating" Objects When Possible

Remember that the macro recorder records every action you take in the user interface. In the user interface, when you change an object's property, the macro recorder records both the action of selecting the object and the action of setting the object's property. To shorten this process, you could omit the selection action altogether, when possible, and set the property directly.

To illustrate, if you were to record a macro in Microsoft Excel while performing the following actions:

1. Start a new workbook
2. Select "Sheet2"
3. Type "Hello World" in cell B2, and press the ENTER key

your recorded macro should look like this:

 Workbooks.Add
 Sheets("Sheet2").Select
 Range("B2").Select
 ActiveCell.FormulaR1C1 = "Hello World"
 Range("B3").Select

By examining the object model documentation for Excel, you can determine that a Range is a child of a Worksheet and a Worksheet is a child of a Workbook. Given this information, you can streamline the code to omit both the selection of the sheet and the selection of the cell:

 Dim Book as Workbook
 Set Book = Workbooks.Add
 Book.Sheets("Sheet2").Range("B2").Value = "Hello World"

This is a small example and might not look like much, but if you are automating Excel to perform this operation 100 times or more, then this change can be significant.

Now consider a macro you record in Word to start a new document. Insert a 2 x 2 table and add data to all four cells in the table. Your recorded macro might look like this:

Documents.Add DocumentType:=wdNewBlankDocument
ActiveDocument.Tables.Add Range:=Selection.Range, _
         NumRows:=2, NumColumns:=2
Selection.TypeText Text:="a"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="b"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="c"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="d"

With an understanding of how the Word object model is constructed, you can improve the code by omitting the need to select each cell in the table to fill it with data:

Dim Doc As Document
Set Doc = Documents.Add

Dim Tbl As Table
Set Tbl = Doc.Tables.Add(Range:=Selection.Range, _
    NumRows:=2, NumColumns:=2)
    
Tbl.Cell(1, 1).Range.Text = "a"
Tbl.Cell(1, 2).Range.Text = "b"
Tbl.Cell(2, 1).Range.Text = "c"
Tbl.Cell(2, 2).Range.Text = "d"

Using this solution, you are able to fill a cell in a Word table by referencing the cell by its row and column number rather than having to use the "move" methods to move from one cell to another.

There are reasons other than just performance for you to avoid selecting or activating objects in your Automation code. When an Office application is automated, it is often hidden and therefore does not have a window associated with it. Some methods or properties that rely on a window might not behave as expected when the application is not visible. So, to be safe, it is best that you avoid "select" and "activate" methods with Automation whenever possible.

Tip #2—Minimize the Server's Screen Repaints

When an Automation server is visible, changes you make to the server's content can force the server to refresh. If you have a relatively large task to accomplish with an Automation server, complete the task before making the application visible. If your situation requires that the server be visible while you automate it, you might benefit from telling the server not to refresh until the task is complete. The Application objects for Excel and Word have a ScreenUpdating property that you can set to False to prevent screen updates while your Automation code executes. Microsoft Access has a similar property: the Echo property of the Application object.

Tip #3—Use Arrays

Some methods and properties can take either a single item or an array of items for an argument. Whether or not an array can be passed for an argument is documented in the Help for the property or method in question. Use the Office VBA documentation to your advantage.

Consider the following Excel macro code that populates four cells with data:

With Worksheets(1)
    .Range("a1").Value = "Last Name"
    .Range("b1").Value = "First Name"
    .Range("c1").Value = "MI"
    .Range("d1").Value = "Title"
End With

The Value property of a Range object can accept either a single value or an array of values. So, you can rewrite the code to use an array:

Worksheets(1).Range("a1:d1").Value = _
               Array("Last Name", "First Name", "MI", "Title")

In this example, you are using a single dimensional array but the Value property of the Range object can be set to a two-dimensional array as well.

Note   For C/C++ and MFC developers: You can pass a SAFEARRAY or COleSafeArray for the invoked Value property of a Range object.

For Visual Basic and MFC code samples that illustrate how you can use arrays to transfer data to an Excel worksheet, see the section "Automate Excel to Create and Format a New Workbook."

Tip #4—Use the Features of the Automation Server to Your Benefit

Office Automation servers provide many timesaving methods and properties that can simplify your Automation code when you are using repetitive tasks.

Again, consider Excel as an Automation server for a moment. Suppose you wanted to format all of the "used" cells in column "A," starting at row 1, with a bold font, but do not know exactly how many used cells there are in that column. You might try something like this:

'Where oSheet is a reference to a particular worksheet . . . 
counter = 1
Do While (oSheet.Cells(counter,1).Value <> "")
     oSheet.Cells(counter, 1).Font.Bold = True
     counter = counter + 1
Loop

If you were to examine the Excel object model documentation, you would find that:

• You can apply a format to a range of multiple cells.
• The object model provides you with a property (UsedRange), that returns a Range object representing the "used" cells on the worksheet.
• You can use the Resize method to resize a range X rows by X columns.

Armed with this knowledge, you can rewrite your code:

oSheet.Cells(1,1).Resize(oSheet.UsedRange.Rows.Count, 1).Font.Bold = True

This makes a significant improvement in your Automation code if you have a relatively large range of "used" cells to format.

Now consider a repetitive task you could simplify while automating Microsoft PowerPoint. If you had a slide with three shapes to which you wanted to apply the same text formats, you might try code similar to the following to format all three shapes:

'Where oSlide represents a Slide object . . .

With oSlide.Shapes("Text Box 2").TextFrame.TextRange.Font
    .Name = "Arial"
    .Size = 16
    .Bold = True
End With

With oSlide.Shapes("Text Box 3").TextFrame.TextRange.Font
    .Name = "Arial"
    .Size = 16
    .Bold = True
End With

With oSlide.Shapes("Text Box 4").TextFrame.TextRange.Font
    .Name = "Arial"
    .Size = 16
    .Bold = True
End With

However, if you were to examine the object model documentation for Microsoft PowerPoint, you would find that there is a way to format all three shapes at once by using a ShapeRange object that represents multiple shapes. You can simplify your code with:

 'Where oSlide represents a Slide object . . .
 Dim oShapes As ShapeRange
 Set oShapes = oSlide.Shapes.Range( _
                    Array("Text Box 2", "Text Box 3", "Text Box 4"))
 With oShapes.TextFrame.TextRange.Font
     .Name = "Arial"
     .Size = 16
     .Bold = True
 End With

As you can see, there are benefits to finding ways to improve repetitive tasks. Understandably, it is difficult to know every single detail of an Automation server's object model and the features that it exposes. However, with a little research into the object model documentation and forethought regarding repetitive tasks you are automating, you can improve your Automation code and simplify some of the tasks you undertake.

Tip #5—Minimize Interface Requests

To improve the performance of your code, you should minimize interface requests whenever possible. Because of the way a C/C++ or MFC programmer writes Automation code, minimizing interface requests is not as much of an issue as it is for Visual Basic programmers. Visual Basic provides the programmer the ability to drill down to many levels within the object model in a single statement. When writing Automation code, it is important to keep in mind how drilling down from one object to another affects the number of interfaces you are requesting from the Automation server. Consider this example that drills down from Excel's Application object to a Range object:

oApp.Workbooks.Item("Book1").Worksheets.Item("Sheet1").Cells(1,1).Value = 5

In this single line of code, you are requesting five interfaces: one interface each for a Workbooks collection object, a Workbook object, a Worksheets collection object, a Worksheet object, and a Range object. If this were the only line of code where you were accessing a range on this particular sheet, this would really not make a big difference in the larger scheme of things. But what if the following code is nested in a loop?

For counter = 1 to 100
     oApp.Workbooks.Item("Book1").Worksheets.Item( _
                                "Sheet1").Cells(counter,1).Value = 5
Next

In this loop, you would be making 5 x 100, for a total of 500 interface requests, which is a large number for this small portion of code. You can improve this number by setting object variables for items that you use frequently. In this particular example, you can benefit from setting an object variable for the worksheet that you are populating with data. Consider the following code that provides you with the same results:

Dim oSheet as Excel.Worksheet

Set oSheet = _
  oApp.Workbooks.Item("Book1").Worksheets.Item("Sheet1") '4 Requests

For counter = 1 to 100
  oSheet.Cells(counter,1).Value = 5   '1 Request
Next

With this improved code, you make 4 + (100 x 1), a total of 114 interface requests. When you compare 114 to 500 interface requests, you recognize a significant improvement and a very important one because it can greatly increase the performance of your Automation code.

Windows 95/98 limitation on interface requests

There is another attraction to minimizing your interface requests. When automating any ActiveX® component, there is a limit to the number of interface requests you can make within a tight loop on Windows® 95 and Windows 98. This limit is roughly 64 kilobytes (KB). If you write code that comes close to or exceeds this limit, you might experience any of the following:

• Your Automation server hangs.
• You receive "Out of memory" errors.
• Your Automation server remains in memory even after your application ends.

To avoid this limit, use these techniques whenever possible:

• Use arrays and call methods and properties on multiple objects at once rather than on individual objects.
• Set variables for Automation objects that you frequently reference or reference in a loop.
• Use the features of the Automation server to your advantage.

For more details on this Windows 95 and Windows 98 limitation, refer to the following article in the Microsoft Knowledge Base:

Q216400 PRB: Cross-Process COM Automation Hangs Client App on Win95/Win98

Handling Events in the Automation Server

Up to this point, your communication has pretty much been one way; your Automation clients have completely directed the Automation server and the server has not communicated anything back to the client. You can use events to allow the Automation server to notify the client when certain actions occur. By trapping these events, you enable two-way communication between the client and the server.

The Microsoft Visual Basic and MFC Automation clients you create in this section trap an event in Microsoft Excel that occurs when data in a cell is changed. Your client examines the data in the changed cell and applies a format to the cell based on the new data that is entered.

Using WithEvents in Visual Basic

An object that raises events is called an event source. To handle the events raised by an event source, you can declare a variable of the object's class using the WithEvents keyword. In this example, WithEvents specifies that the variable xlSheet is used to handle a Worksheet object's events:

Dim WithEvents xlSheet As Excel.Worksheet

There are some limitations to WithEvents:

• You cannot declare a WithEvents variable as type Object. You must specify the class name when you declare the variable; therefore, you must use early binding to trap events for Automation servers.
• You cannot declare a WithEvents variable as New.
• You cannot declare WithEvents variables in a standard module. You can declare them only in class modules, form modules, and other modules that define classes.

When you declare a variable using WithEvents, the variable name appears in the Object list of the module's code window. When you select the variable in the list, the events exposed by that class appear in the Procedure list of the module's code window. Figure 10 illustrates.

Figure 10. When you declare a variable WithEvents, the object appears in the Object list and the object's exposed events appear in the Procedure list.

Exercise 8: Create a Visual Basic Automation client for Microsoft Excel that traps Excel's Change event

1. Start a new Standard Exe project in Visual Basic. Form1 is created by default.

2. Add a reference to the Microsoft Excel 8.0 or 9.0 object library.

3. Add a CommandButton to Form1.

4. Add the following declarations to Form1:

   Dim xlApp As Excel.Application
   Dim xlBook As Excel.Workbook
   Dim WithEvents xlSheet As Excel.Worksheet
   

5. Add code to the click event of the CommandButton that creates a new instance of Excel, start a new workbook with a single sheet, and then give the user control of Excel:

   Private Sub Command1_Click()
       'Start a new workbook in Excel and give the user control to
       'add/change data on the worksheet
       Set xlApp = New Excel.Application
       Set xlBook = xlApp.Workbooks.Add(xlWBATWorksheet)
       Set xlSheet = xlBook.Worksheets.Add
       xlApp.Visible = True
       xlApp.UserControl = True
   End Sub
   

6. Click the Object drop-down box in the code window and select xlSheet from the list. Then, click the Procedure drop-down box in the code window and select Change from the list. This displays the skeleton of the Change event for the Worksheet object. Add the following code for the event:

   Private Sub xlSheet_Change(ByVal Target As Excel.Range)
       Dim xlRng As Excel.Range
       'Examine the contents of a range when the contents
       'change
       For Each xlRng In Target
           If xlApp.WorksheetFunction.IsNumber(xlRng) Then
               'Format numbers < 0 with a red font and
               'numbers >=0 with a green font
               If xlRng < 0 Then
                   xlRng.Font.Color = RGB(255, 0, 0)
               Else
                   xlRng.Font.Color = RGB(0, 255, 0)
               End If
           Else
               'Format non-numeric cells with a blue font
               xlRng.Font.Color = RGB(0, 0, 255)
           End If
       Next
   End Sub
   

7. Press F5 to run the application.

8. Click Command. Excel starts with a new workbook that contains a single sheet. Add different types of data to cells on the worksheet. Note that when you enter data, the data is formatted according to the value you entered.

Using Connection Points with C++ and MFC

To this point in our discussion, all of the communication from your C++ and MFC Automation clients has been one-way communication. In other words, the COM object interfaces that you have used thus far have all been incoming interfaces. Some COM objects also support outgoing interfaces; it is these outgoing interfaces that allow the object to notify clients when certain events occur. These event notifications are handled through connection points.

Event notifications from the Automation server to the Automation client are supported by two interfaces: IConnectionPointContainer and IConnectionPoint. An Automation object that is "connectable" implements both of these interfaces for each outgoing interface that it supports.

To set up event notifications in your Automation client, you can:

• Implement the connectable object's IDispatch interface in your client.
• Call QueryInterface on the connectable object to get a pointer to its IConnectionPointContainer interface.
• Call IConnectionPointContainer::FindConnectionPoint() to obtain a pointer to the IConnectionPoint interface for the events you want to sink.
• Call IConnectionPoint::Advise() to establish a connection with the connectable object by providing a pointer to your implementation of its interface.

Exercise 9: Create an MFC Automation client for Microsoft Excel that traps Excel's Change event

To set up event notifications, you need to examine the server's type library to find the GUID for the outgoing interface as well as the DISPIDs for the events you want to catch. In the following exercise, you create an Automation client for Excel that traps a Worksheet object's Change event. By examining Excel's type library in the OLE/COM Object Viewer, you find that the outgoing interface, IDocEvents, has the GUID {00024411-0001-0000-C000-000000000046} and the Change event has the DISPID 0x609.

Figure 11. OLE/COM Object Viewer provides you with the information you need to connect to an outgoing interface and trap specific events.

1. Create a new MFC AppWizard .exe that is dialog-based and named "Exercise9."

2. Using ClassWizard, add all of the classes in the Excel 8.0 (or 9.0) type library.

3. Add AfxOleInit() to CExercise9App::InitInstance() to initialize the COM library:

   if(!AfxOleInit())
   {
       AfxMessageBox("Could not initialize COM services.");
       return FALSE;
   }
   

4. Add a new C/C++ header file to the project. Name the header file ExcelEventSink.h. This file contains the IDispatch implementation for the Worksheet events sink. Add the following code to the header file:

   #include "excel8.h"  //or "excel9.h" for Excel 2000
   
   // IDispatch implementation to catch Excel's events.
   class CExcelEventSink : public IDispatch
   {
   public:
   ULONG refCount;
   
   CExcelEventSink::CExcelEventSink() {
      refCount = 1;
   }
   CExcelEventSink::~CExcelEventSink() {
   }
   
   // IUnknown methods.
   virtual HRESULT __stdcall QueryInterface(
         REFIID riid, void **ppvObject) {
      if(
         IsEqualGUID(riid, IID_IDispatch) ||
         IsEqualGUID(riid, IID_IUnknown)
      ) {
         this->AddRef();
         *ppvObject = this;
         return S_OK;
      }
      *ppvObject = NULL;
      return E_NOINTERFACE;
   }
   
   virtual ULONG _stdcall AddRef(void) {
      return ++refCount;
   }
   
   virtual ULONG _stdcall Release(void) {
      if(--refCount <= 0) {
         //Delete this;
         return 0;
      }
      return refCount;
   }
   
   // IDispatch methods.
   virtual HRESULT _stdcall GetTypeInfoCount(UINT *pctinfo) {
      if(pctinfo) *pctinfo = 0;
      return E_NOTIMPL;
   }
   
   virtual HRESULT _stdcall GetTypeInfo(
         UINT iTInfo, LCID lcid, ITypeInfo **ppTInfo) {
      return E_NOTIMPL;
   }
   
   virtual HRESULT _stdcall GetIDsOfNames(
         REFIID riid, LPOLESTR *rgszNames, UINT cNames, LCID lcid,
         DISPID *rgDispId) {
      return E_NOTIMPL;
   }
   
   virtual HRESULT _stdcall Invoke(
         DISPID dispIdMember, REFIID riid, LCID lcid, WORD wFlags,
         DISPPARAMS *pDispParams, VARIANT *pVarResult,
         EXCEPINFO *pExcepInfo, UINT *puArgErr) 
   {
   
      switch(dispIdMember) 
      {
         case 0x609: // Worksheet Change Event
           {          
               //Get the target range and the font for that range
               Range oRange;
               oRange.AttachDispatch(pDispParams->rgvarg[0].pdispVal);
               Font oFont = oRange.GetFont();
   
               //Determine if the range is numeric
               _Application oApp = oRange.GetApplication();
               WorksheetFunction oFunc = oApp.GetWorksheetFunction();
               BOOL bIsNumber = oFunc.IsNumber(pDispParams->rgvarg[0]);
   
               //If it is a numeric format it with green if >=0 and
               //red if <= 0
               if (bIsNumber)
               {
                 COleVariant vValue = oRange.GetValue();
                 if (vValue.dblVal>=0)
                     oFont.SetColor(COleVariant((long)0xFF00));
                 else
                     oFont.SetColor(COleVariant((long)0xFF));
               }
               //Else, set the font color to blue
               else
               {
                 oFont.SetColor(COleVariant((long)0xFF0000));
               }
   
               break;
           }         
   
      }
      return S_OK;
   }
   
   };
   

5. Add two Commands to the dialog resource IDD_EXERCISE9_DIALOG and create member variables for both buttons.

   ID	Caption	Member Variable
   IDC_START_EXCEL_EVENTS	Start	m_StartExcelEvents
   IDC_END_EXCEL_EVENTS	End	m_EndExcelEvents

6. Add the following variables and button handlers to Exercise9Dlg.cpp:

   IConnectionPoint *pCPSheetEvents;
   CExcelEventSink Sink;         
   DWORD dwSheetEventsCookie;
   BOOL bSinkInPlace = FALSE;
   
   void CExercise9Dlg::OnStartExcelEvents() 
   {
       _Application oApp;
   
       if(!oApp.CreateDispatch("Excel.Application", NULL))
       {
           AfxMessageBox("Cannot start Excel");
           return;
       }
   
       //Start a new workbook with exactly one sheet
       Workbooks oBooks = oApp.GetWorkbooks();
       _Workbook oBook = oBooks.Add(COleVariant((short)-4167));
       Worksheets oSheets = oBook.GetWorksheets();
       _Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
   
       //Make Excel visible and give the user control
       oApp.SetVisible(TRUE);
       oApp.SetUserControl(TRUE);
   
       // Declare the events you want to catch.-- in this case, you
       // are catching the Worksheet object events to determine
       // trap when the user changes data in a cell.
       //
       // IDocEvents = {00024411-0001-0000-C000-000000000046}
   
       static const GUID IID_IDocEvents =
           {0x00024411,0x0000,0x0000,
           {0xc0,0x00,0x0,0x00,0x00,0x00,0x00,0x46 } };
   
       // Get the Worksheet object's IConnectionPointContainer 
       // interface to set up the advisory connection
       HRESULT hr;
       IConnectionPointContainer *pCPCtrSheet = NULL;
       hr = oSheet.m_lpDispatch->QueryInterface(
                        IID_IConnectionPointContainer,
                        (void **)&pCPCtrSheet);
       hr = pCPCtrSheet->FindConnectionPoint(IID_IDocEvents, 
                        &pCPSheetEvents);
       hr = pCPSheetEvents->Advise(&Sink, &dwSheetEventsCookie);
       if (SUCCEEDED(hr))
           bSinkInPlace=TRUE;
       if (pCPCtrSheet!=NULL) pCPCtrSheet->Release();
   
       m_StartExcelEvents.EnableWindow(FALSE);
       m_EndExcelEvents.EnableWindow(TRUE);
   }
   
   void CExercise9Dlg::OnEndExcelEvents() 
   {
       if (bSinkInPlace)
       {
           //Terminate the advisory connections
           pCPSheetEvents->Unadvise(dwSheetEventsCookie);
           pCPSheetEvents->Release();
           bSinkInPlace = FALSE;
       }
       m_StartExcelEvents.EnableWindow(TRUE);
       m_EndExcelEvents.EnableWindow(FALSE);
   
   }
   

7. Add the precompiler directive to include ExcelEventSink.h in Exercise9Dlg.cpp:

       #include "ExcelEventSink.h"
   

8. Build and run the application.

9. Click Start in the dialog box. Excel starts with a new workbook. Add data to cells on the sheet. Note that as you add data to cells, the sink traps the Change event and the changed cell is formatted based on its contents.

10. Click End in the dialog box. The connection is dropped and any changes you make later to the worksheet are unaffected by the sink.

Note   In this exercise, you provided your own implementation of IDispatch in the class CExcelEventSink. The default implementation in MFC of IDispatch::Invoke() does not support named arguments, so you must provide your own implementation of IDispatch.

Automating Embedded and Linked Office Documents

Office documents can be inserted into other containers. Containers that allow you to insert objects from other applications can create documents that contain information and data for each object they hosts. These documents are called compound documents.

The mechanism by which an application's document is inserted into a container of a different application is called OLE (Object Linking and Embedding). An OLE object that you insert into a container can be either linked or embedded. The primary difference between the two types lies in how and where the object's source data is stored.

When you add a link to an object, the source data for that object continues to reside wherever it was initially created. With a linked object, the compound document contains only a reference to the actual data along with information about how to present that data. In contrast, all the data in an embedded object is physically stored in the compound document.

Both linked and embedded objects have their advantages:

• The storage required in a compound document is less for a linked object when compared to an embedded object.
• When you make changes to an embedded object, the changes are applied only to the object contained within the compound document. In contrast, when you make changes to a linked object in a compound document, you are actually changing the source document's data, and the information about that link is updated in the compound document.
• When you change the source data for a linked object, the linked object in the compound document can be updated to reflect the changes in the source. On the other hand, objects that are embedded from a source are unaffected by changes to the source.
• Compound documents that contain embedded objects can be transferred to other locations without concern for breaking links because no link information is stored for embedded objects.
• Embedded objects can be in-place activated in the container. Linked objects cannot be in-place activated; instead, the document must be opened with the server in a separate window.

Many applications that can host OLE objects, including the Office applications, provide you with a standard dialog box for inserting OLE objects (Figure ** 12). When presented with this dialog box, you can choose Create New and select an object type from the list to insert an object that is embedded. Or, you can choose Create From File and browse to a file. When you insert an object based on a file, you have the option of creating a link.

Figure 12. The Insert Object dialog box is commonly used to embed or link an object.

MFC and Visual Basic applications can support containers that host linked and embedded objects. Provided that the object's OLE server supports Automation, the application hosting the object can access the object's OLE server at run time to manipulate the object.

Using the OLE Container in Visual Basic

The OLE Container control provides Visual Basic applications with the ability to display OLE objects on a form. With the OLE Container, you can insert linked or embedded objects, activate and edit objects, and even automate OLE objects. To automate an object contained in an OLE Container control, you use its Object property. For example, if you have an OLE Container (named OLE1) that contains a Microsoft Word document, you can automate Word to make changes to the document using the following code:

Dim oDoc as Word.Document
Set oDoc = OLE1.Object
oDoc.TypeText "Hello World"

As another example, consider an OLE Container that contains a Microsoft Excel workbook. To automate that workbook, you could use the following code:

Dim oBook as Excel.Workbook
Dim oApp as Excel.Application
Set oBook = OLE1.Object
Set oApp = oBook.Application
oApp.ActiveSheet.Range("A1").Value = "Hello World"

For more sample code and information about using the OLE Container in Visual Basic to embed and automate Office documents, please see the following article in the Microsoft Knowledge Base:

Q242243 HOWTO: Embed and Automate Office Documents with Visual Basic

Exercise 10: Embed and automate an Excel Chart with Visual Basic

In this exercise, you use the OLE Container control on a Form in Visual Basic to embed a new Microsoft Excel Chart. Once the object is embedded, you begin to automate it by calling the Object property of the OLE Container to obtain a reference to the Workbook that is contained.

Note   Although the object that you embed has the PROGID "Excel.Chart," the document that is actually embedded is a workbook that contains both a chart sheet and a worksheet. Therefore, the document that is returned from the Object property is of the type Workbook. Once Automation is complete, the OLE Container displays an Excel XY-Scatter chart that resembles the one illustrated in Figure 13.

Figure 13. An OLE Container on a Visual Basic form that contains a Microsoft Excel chart

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a reference to the Microsoft Excel object library.

3. Add an OLE Container control to Form1. When prompted to select an object type, click Cancel because, in this sample, you set the object type at run time.

4. Set the OLE Container control's SizeMode property to 3-Zoom and its Visible property to False.

5. Add two CommandButtons to Form1. Set the Caption property for Command1 to Create Chart. Set the Caption property for Command2 to Update Chart. Also set the Enabled property of Command2 to False.

6. Add the following code to the module for Form1:

   Private Sub Command1_Click()
   
       'Embed a new Excel chart
       OLE1.CreateEmbed "", "excel.chart"
   
       'Get an object reference to the chart object and the worksheet 
       'object so that you can change the chart and the data
       Dim oBook As Excel.Workbook
       Dim oSheet As Excel.Worksheet
       Dim oChart As Excel.Chart
       Set oBook = OLE1.object
       Set oChart = oBook.Charts(1)
       Set oSheet = oBook.Worksheets(1)
   
       'Clear all the cells on the worksheet and
       'then add your data to the worksheet
       oSheet.Cells.Clear
       oSheet.Range("A1:C1").Value = Array("X", "Y1", "Y2")
       oSheet.Range("A2:C10").Formula = "=RAND()"
   
       'Set the sourcedata for the chart to range "A1:C10"
       'and change the chart type to xy-scatter
       oChart.ChartType = xlXYScatterLines
       oChart.SetSourceData oSheet.Range("A1:C10"), xlColumns
   
       'Add the chart title and remove the legend
       oChart.HasTitle = True
       oChart.ChartTitle.Text = "Sample XY Scatter Chart"
       oChart.HasLegend = False
   
       'For the axes, change the 1) major units so that the scale
       'for the chart appears in increments of 0.25, 2) change
       'the maximum of the scale and 3) format the axis labels to
       'show two decimal places, 4) add major gridlines
       With oChart.Axes(xlValue)
           .MajorUnit = 0.25
           .TickLabels.NumberFormat = "0.00"
           .TickLabels.Font.Size = 9
           .MaximumScale = 1
           .HasMajorGridlines = True
       End With
       With oChart.Axes(xlCategory)
           .MajorUnit = 0.25
           .TickLabels.NumberFormat = "0.00"
           .TickLabels.Font.Size = 9
           .MaximumScale = 1
           .HasMajorGridlines = True
       End With
   
       'Change the color of the plot area and the series
       oChart.PlotArea.Interior.ColorIndex = 19
       With oChart.SeriesCollection(1)
           .Border.ColorIndex = 21
           .Border.Weight = xlMedium
           .MarkerStyle = xlNone
       End With
       With oChart.SeriesCollection(2)
           .Border.ColorIndex = 10
           .Border.Weight = xlMedium
           .MarkerStyle = xlNone
       End With
   
       OLE1.Visible = True
       OLE1.Close  'Deactivate the OLE container
       Command1.Enabled = False
       Command2.Enabled = True
   
   End Sub
   
   Private Sub Command2_Click()
       'Recalculate the worksheet, and thus update the chart
       OLE1.object.Application.Calculate
   End Sub
   

7. Run the application. Click Create Chart to embed the Excel chart object and automate it. Once the chart is created, you can click Update Chart to recalculate the cells on the worksheet and update the chart.

Use COleClientItem with MFC

With MFC, the COleClientItem class defines the container interface to OLE objects. If you use the AppWizard to build your application, you can choose to provide your application with container support for compound documents and the COleClientItem classed is wrapped for you.

COleClientItem does not provide a member function for retrieving the IDispatch pointer to a contained OLE object. However, the means for doing so are documented in the technical note "TN039: MFC/OLE Automation Implementation." This technical note describes a function, COleClientItem::GetIDispatch(), that you can use in your MFC applications to acquire the IDispatch pointer to an OLE object for the purpose of automating it. Exercise 11 below demonstrates use of this function to automate an embedded Excel chart.

For additional code samples of embedding and automating Office documents in your MFC applications, please see the following articles in the Microsoft Knowledge Base:

Q184663 HOWTO: Embed and Automate a Microsoft Excel Worksheet with MFC

Q238611 HOWTO: Embed and Automate a Word Document with MFC

Exercise 11: Create an MFC container that embeds and automates a Microsoft Excel Chart

Figure 14. An embedded Microsoft Excel Chart in an MFC application

1. Create a new MFC Appwizard (EXE) named "Exercise11."

2. In step 1 of the AppWizard, select Single document as the application type.

3. In step 3 of the AppWizard, select Container for compound document support, and then click Finish.

4. Using ClassWizard, add the wrapper classes for the Excel type library.

5. Add the GetIDispatch() function to CntrItem.cpp. This is the function you use to return the IDispatch pointer for the embedded object.

   LPDISPATCH CExercise11CntrItem::GetIDispatch()
   {
        //The this and m_lpObject pointers must be valid for this 
        //function to work correctly. The m_lpObject is the IUnknown 
        //pointer to this object.
        ASSERT_VALID(this);
        ASSERT(m_lpObject != NULL);
   
        LPUNKNOWN lpUnk = m_lpObject;
   
        //The embedded application must be running in order for the 
        //rest of the function to work.
        Run();
   
        //QI for the IOleLink interface of m_lpObject.
        LPOLELINK lpOleLink = NULL;
        if (m_lpObject->QueryInterface(IID_IOleLink,
           (LPVOID FAR*)&lpOleLink) == NOERROR)
        {
           ASSERT(lpOleLink != NULL);
           lpUnk = NULL;
   
           //Retrieve the IUnknown interface to the linked 
           //application.
           if (lpOleLink->GetBoundSource(&lpUnk) != NOERROR)
           {
              TRACE0("Warning: Link is not connected!\n");
              lpOleLink->Release();
              return NULL;
           }
           ASSERT(lpUnk != NULL);
        }
   
        //QI for the IDispatch interface of the linked application.
        LPDISPATCH lpDispatch = NULL;
        if (lpUnk->QueryInterface(IID_IDispatch, 
           (LPVOID FAR*)&lpDispatch)!=NOERROR)
        {
           TRACE0("Warning: does not support IDispatch!\n");
           return NULL;
        }
   
        //After assuring ourselves it is valid, return the IDispatch
        //interface to the caller.
        ASSERT(lpDispatch != NULL);
        return lpDispatch;
   }
   

   Also, update CntrItem.h to accommodate the new function:

   LPDISPATCH GetIDispatch();
   

6. Add a member variable that contains the bounding rectangle of the embedded OLE object. Add the following to CntrItem.h:

   CRect m_rectChart;
   

7. Select the ResourceView tab and activate the menu resource IDR_MAINFRAME. Add a new item to the Edit menu with the caption Insert Excel Chart. Also add a new item to the Edit menu with the caption Update Chart. Note that the default IDs given to the new commands are ID_EDIT_INSERTEXCELCHART and ID_EDIT_UPDATECHART, respectively.

8. Include the Excel wrapper classes in CExercise11View.cpp:

   #include "excel8.h"   //or "Excel9.h" for Excel 2000
   

9. Create a handler in CExercise11View for the Insert Excel Chart command using ClassWizard, and then add the following code to the handler:

   void CExercise11View::OnEditInsertexcelchart() 
   {
       CExercise11CntrItem* pItem = NULL;
   
       //Get the document associated with this view
       CExercise11Doc* pDoc = GetDocument();
   
       //Create a new item associated with this document
       pItem = new CExercise11CntrItem(pDoc);
   
       //Embed a new Excel chart
       CLSID clsid;
       ::CLSIDFromProgID(L"Excel.Chart",&clsid);
       pItem->CreateNewItem(clsid);
   
       //Specify the dimensions for the embedded object based
       //on this view and store them in our member variable
       CRect rectView;
       this->GetClientRect(&rectView);
       pItem->m_rectChart.SetRect(10, 10, rectView.right-10, 
                                  rectView.bottom-10);
   
       //Get the IDispatch pointer for the embedded object so
       //that you can automate it to control the chart and
       //the worksheet
       LPDISPATCH lpDisp;
       lpDisp = pItem->GetIDispatch();
   
       COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
   
       _Workbook oBook;
       oBook.AttachDispatch(lpDisp);
   
       //Get IDispatch* for the chart sheet and the worksheet
       Worksheets oSheets = oBook.GetWorksheets();
       _Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
       Charts oCharts = oBook.GetCharts();
       _Chart oChart = oCharts.GetItem(COleVariant((short)1));
   
       //Clear all the cells on the worksheet and then add the new
       //data to it
       Range oRng = oSheet.GetCells();
       oRng.Clear();
       oRng = oSheet.GetRange(COleVariant("A1"), vOpt);
       oRng.SetValue(COleVariant("X"));
       oRng = oSheet.GetRange(COleVariant("B1"), vOpt);
       oRng.SetValue(COleVariant("Y1"));
       oRng = oSheet.GetRange(COleVariant("C1"), vOpt);
       oRng.SetValue(COleVariant("Y2"));
       oRng = oSheet.GetRange(COleVariant("A2:C10"), vOpt);
       oRng.SetFormula(COleVariant("=RAND()"));
   
       //Set the sourcedata for the chart to range "A1:C10" and
       //change the chart type to xy-scatter lines
       oChart.SetChartType((long)74); //xlXYScatterLines = 74
       oRng = oSheet.GetRange(COleVariant("A1:C10"), vOpt);
       oChart.SetSourceData(oRng, 
                            COleVariant((short)2)); //xlColumns = 2
   
       //Add the chart title and remove the legend
       oChart.SetHasTitle(TRUE);
       ChartTitle oChartTtl = oChart.GetChartTitle();
       oChartTtl.SetText("Sample XY Scatter Chart");
       oChart.SetHasLegend(FALSE);
   
       //For the axes, change the 1) major units so that the scale
       //for the chart appears in increments of 0.25, 2) change
       //the maximum of the scale and 3) format the axis labels to
       //show two decimal places, 4) add major gridlines
       Axis oValAxis = oChart.Axes(COleVariant((short)2), 
                                   (long)1);  //xlValue =2
       Axis oCatAxis = oChart.Axes(COleVariant((short)1), 
                                   (long)1);  //xlCategory = 1
   
       //The Value Axis...
       oValAxis.SetMajorUnit((double)0.25);
       TickLabels oTickLbls = oValAxis.GetTickLabels();
       oTickLbls.SetNumberFormat("0.00");
       Font oFont = oTickLbls.GetFont();
       oFont.SetSize(COleVariant((short)9));
       oValAxis.SetMaximumScale((double)1);
       oValAxis.SetHasMajorGridlines(TRUE);
   
       //The Category Axis...
       oCatAxis.SetMajorUnit((double)0.25);
       oTickLbls = oCatAxis.GetTickLabels();
       oTickLbls.SetNumberFormat("0.00");
       oFont = oTickLbls.GetFont();
       oFont.SetSize(COleVariant((short)9));
       oCatAxis.SetMaximumScale((double)1);
       oCatAxis.SetHasMajorGridlines(TRUE);
   
       //Change the color of the plot area
       PlotArea oPlot = oChart.GetPlotArea();
       Interior oInt = oPlot.GetInterior();
       oInt.SetColorIndex(COleVariant((short)19));
   
       //Change the color and marker styles for the series
       Series oSeries = 
              oChart.SeriesCollection(COleVariant((short)1));
       Border oBorder = oSeries.GetBorder();
       oBorder.SetColorIndex(COleVariant((short)21));
       oBorder.SetWeight(COleVariant((long)-4138));//xlMedium = -4138 
       oSeries.SetMarkerStyle((long)-4142);        //xlNone = -4142
       oSeries = oChart.SeriesCollection(COleVariant((short)2));
       oBorder = oSeries.GetBorder();
       oBorder.SetColorIndex(COleVariant((short)10));
       oBorder.SetWeight(COleVariant((long)-4138));//xlMedium = -4138 
       oSeries.SetMarkerStyle((long)-4142);        //xlNone = -4142
   }
   

10. To update the member variable m_rectChart with the bounding rectangle of the embedded object, you should modify CExercise11View::OnDraw and CExercise11CntrItem::OnGetItemPosition.

    Replace the following line in CExercise11View::OnDraw:

    m_pSelection->Draw(pDC, CRect(10, 10, 210, 210));
    

    with this line:

    m_pSelection->Draw(pDC, m_pSelection->m_rectChart);
    

    And replace the following line in CExercise11CntrItem::OnGetItemPosition:

    rPosition.SetRect(10, 10, 210, 210);
    
    

    with this line:

    rPosition = m_rectChart;
    

11. Create a handler in CExercise11View for the Update Chart command using ClassWizard, and then add the following code to the handler:

    void CExercise11View::OnEditUpdatechart() 
    {
        if (m_pSelection != NULL)
        {
           LPDISPATCH lpDisp;
            lpDisp = m_pSelection->GetIDispatch();
            _Workbook oBook;
            oBook.AttachDispatch(lpDisp);
            _Application oApp = oBook.GetApplication();
            oApp.Calculate();
        }
    }
    

12. Build and run the sample application. Once the application loads, on the Edit menu, select Insert Excel Chart. A new Excel chart resembling the one in Figure 14 is embedded. Once the chart is embedded, on the Edit menu, select Update Chart. Note that the data recalculates and the chart updates accordingly.

Sample Code for Automating Microsoft Office Applications

This section contains Visual Basic and MFC sample code for automating Office applications for common tasks in an effort to illustrate how to use the different applications as Automation servers. The code has been tested against Microsoft Office 97 and Microsoft Office 2000 applications and can be used for both versions.

You might be referencing this section to learn how to automate a particular Office application. Keep in mind that each sample is unique and illustrates different Automation techniques. You can benefit from reviewing the discussion and sample code for servers other than the one or more that you intend to automate.

Note   For brevity, these samples do not implement error handling.

Automate Excel to Create and Format a New Workbook

The first examples automate Microsoft Excel to create a new workbook and transfer data to a worksheet in the new workbook using arrays. The number of rows in the resulting data is determined at run time. Figure 15 represents the workbook's appearance after running the Automation code in this section.

Figure 15. Results of the sample Automation code to create and format a new Excel workbook

Visual Basic example

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a reference to the Excel object library.

3. Add a CommandButton to Form1.

4. Add the following code to the module for Form1:

   Const NUMROWS = 20
   
   Private Sub Command1_Click()
   
       'Start a new workbook in Excel
       Dim oExcel As Excel.Application
       Dim oBook As Excel.Workbook
       Set oExcel = New Excel.Application
       Set oBook = oExcel.Workbooks.Add
   
       'Get the first worksheet in the workbook so that you can
       'make changes to it
       Dim oSheet As Excel.Worksheet
       Set oSheet = oBook.Worksheets(1)
   
       'Add headers to Row 1 of the worksheet
       oSheet.Range("A1:D1").Value = Array("Date", "Order #", _
                                           "Amount", "Tax")
   
       'Create an array that is NUMROWS x 3 columns.
       'Column 1 will contain dates, column 2 will contain strings
       'and column 3 will contain numbers
       ReDim vArray(1 To NUMROWS, 1 To 3) As Variant
       Dim i As Integer
       For i = 1 To NUMROWS
           vArray(i, 1) = Format(DateSerial(1999, _
              (Rnd * 100) Mod 12, (Rnd * 100) Mod 28), "m/d/yy")
           vArray(i, 2) = "ORDR" & i + 1000
           vArray(i, 3) = Format(Rnd * 100, "#0.00")
       Next
   
       'Fill a range, starting at cell A2 with the data from the array
       oSheet.Range("A2").Resize(NUMROWS, 3).Value = vArray
   
       'Fill the fourth column with a formula to compute the sales tax.
       'Note that the formula uses a "relative" cell reference so that 
       'it fills properly
       oSheet.Range("D2").Resize(NUMROWS, 1).Formula = "=C2*0.07"
   
       'Format the worksheet
       With oSheet.Range("A1:D1")
           .Font.Bold = True
           .EntireColumn.AutoFit
       End With
   
       'Make Excel visible and give the user control
       oExcel.Visible = True
       oExcel.UserControl = True
   
   End Sub
   

5. Run the application and click the CommandButton. When the code finishes running, a new workbook in Excel appears containing data resembling the data in Figure 15.

MFC example

1. Start a new dialog-based MFC AppWizard EXE project named "ExcelArrays."

2. Using ClassWizard, add the wrapper classes for the Excel type library.

3. Add a button to the dialog resource IDD_EXCELARRAYS_DIALOG and add the following code to the button's handler in ExcelDataDlg.cpp:

   #define NUMROWS 20
   
   void CExcelArraysDlg::OnRun() 
   {
   
         // For optional arguments
         COleVariant vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   
         // Instantiate Excel
         _Application oApp;
         oApp.CreateDispatch("Excel.Application");
         if (!oApp)
         {
             AfxMessageBox("Cannot start Excel.");
             return;
         }
   
         //Get the Workbooks collection so that you can add a new
         //workbook
         Workbooks oBooks = oApp.GetWorkbooks();
         _Workbook oBook = oBooks.Add(vOpt);
   
         //Get the Worksheets collection of the new Workbook so that
         //you can get the IDispatch for the first WorkSheet
         Worksheets oSheets = oBook.GetWorksheets();
         _Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
   
         //*** ADD DATA TO THE WORKSHEET
   
         //Add Headers to Row 1 of the worksheet
         Range oRange;
         oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
         oRange.SetValue(COleVariant("Date"));
         oRange = oSheet.GetRange(COleVariant("B1"), vOpt);
         oRange.SetValue(COleVariant("Order #"));
         oRange = oSheet.GetRange(COleVariant("C1"), vOpt);
         oRange.SetValue(COleVariant("Amount"));
         oRange = oSheet.GetRange(COleVariant("D1"), vOpt);
         oRange.SetValue(COleVariant("Tax"));
   
         //Create a safe array that is NUMROWS x 3 --
         //column 1 will contain dates column 2 will contain strings
         //and column 2 will contain numbers
         COleSafeArray sa;
         DWORD dwElements[2];
         dwElements[0]= NUMROWS;    //Number of rows
         dwElements[1]= 3;          //Number of columns
         sa.Create(VT_VARIANT, 2, dwElements);
   
         //Populate the safe array with the data
         long index[2];
         long lRow;
         COleVariant vTemp;
         COleDateTime vDateTime;
         CString s;
   
         for(lRow=0;lRow<=NUMROWS-1;lRow++)
         {
            index[0] = lRow;   
   
            //Fill the first column with dates
            index[1] = 0;
            vDateTime.SetDate(1999, rand()%12, rand()%28);
            sa.PutElement(index, (COleVariant)vDateTime);
   
            //Fill the second column with strings
            index[1] = 1;
            s.Format("ORDR%d", lRow+1000);
            vTemp = s;
            sa.PutElement(index, vTemp);
   
            //Fill the third column with numbers
            index[1] = 2;
            vTemp = (long)rand();
            sa.PutElement(index, vTemp);
         }
   
         //Fill a range, starting at A2 with the data in
         //the safe array
         oRange = oSheet.GetRange(COleVariant("A2"), vOpt);
         oRange = oRange.GetResize(COleVariant((short)NUMROWS),
                                     COleVariant((short)3));
         oRange.SetValue(sa);
         sa.Detach();
   
         //*** ADD FORMULAS TO THE WORKSHEET
   
         //Fill the fourth column with a formula to compute the
         //sales tax. Note that the formula uses a "relative"
         //cell reference so that it fills properly.
         oRange = oSheet.GetRange(COleVariant("D2"), vOpt);
         oRange = oRange.GetResize(COleVariant((long)NUMROWS), 
                                   COleVariant((long)1));
         oRange.SetFormula(COleVariant("=C2*0.07"));
   
         //*** FORMAT THE WORKSHEET
         oRange = oSheet.GetRange(COleVariant("A1"), COleVariant("D1"));
         Font oFont = oRange.GetFont();
         oFont.SetBold(COleVariant((short)TRUE));//Apply Bold to Headers
         oRange = oRange.GetEntireColumn();
         oRange.AutoFit();                    //AutoFit the columns 1:4
   
         //Make Excel visible and give the user control
         oApp.SetVisible(TRUE);
         oApp.SetUserControl(TRUE);
   
   }
   

4. Add the following includes to ExcelArraysDlg.cpp:

   #include "Excel8.h"  // or "Excel9.h" for Excel 2000
   

5. Modify CExcelArraysApp::InitInstance() in ExcelArrays.cpp to start COM services:

   if(!AfxOleInit())
   {
         AfxMessageBox("Cannot initialize COM services.");
         return FALSE;
   }
   

6. Build and run the application. Click the button you added to the dialog box. When the Automation code finishes running, you see a new workbook in Microsoft Excel containing data similar to that of Figure 15.

Additional notes

For performance, using arrays to transfer data to Microsoft Excel is preferred whenever possible. However, you should be aware that there are limitations to the size of an array that you can transfer to Excel with Automation. If you have a rather large set of data to transfer to Excel, transfer the data in increments to avoid hitting Excel's limits.

For details on Excel's limitations with arrays, refer to the following article in the Microsoft Knowledge Base:

Q177991 XL: Limitations of Passing Arrays to Excel Using Automation

Automate Excel to Add Data from a DAO Recordset to a Workbook

This section demonstrates how you can transfer data from a DAO (Data Access Objects) recordset to a worksheet in Microsoft Excel using Automation. This example returns data from the Microsoft Access Northwind Sample Database. You query the Northwind database for all of the records in the Products table and then transfer that data to a new Excel worksheet by using Excel's CopyFromRecordset method for a Range object. Note that although this sample was written for use with a sample Access database, you can use CopyFromRecordset with any DAO recordset generated from an ODBC data source connection, so you are not limited to using just Access data. Figure 16 below illustrates how the data appears in the Excel workbook after running the sample Automation code In this section.

Figure 16. The Automation code in the examples transfer data from the Products table in the Access Northwind Sample Database to a new worksheet in Excel.

Visual Basic example

1. Create a new project in Visual Basic. Form1 is created by default.

2. Add a reference to both the Microsoft Excel type library and the Microsoft Data Access Objects (DAO) type library.

3. Add a CommandButton to Form1 and add the following code to the Click event of that CommandButton.

   **Note   **The default path for the Northwind Sample Database is used in this code; the default path is "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb." Check your location of Northwind.mdb and modify the path in the code if necessary.

       'Get the recordset from the Customer's table in Northwind
       Dim db As DAO.Database
       Dim rs As DAO.Recordset
       Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
       "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
       Set rs = db.OpenRecordset("Select * from Products", _
           dbOpenSnapshot)
   
       'Start a new workbook in Excel
       Dim oApp As New Excel.Application
       Dim oBook As Excel.Workbook
       Dim oSheet As Excel.Worksheet
   
       Set oBook = oApp.Workbooks.Add
       Set oSheet = oBook.Worksheets(1)
   
       'Add the field names in row 1
       Dim i As Integer
       Dim iNumCols As Integer
       iNumCols = rs.Fields.Count
       For i = 1 To iNumCols
           oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
       Next
   
       'Add the data starting at cell A2
       oSheet.Range("A2").CopyFromRecordset rs
   
       'Format the header row as bold and autofit the columns
       With oSheet.Range("a1").Resize(1, iNumCols)
           .Font.Bold = True
           .EntireColumn.AutoFit
       End With
   
       oApp.Visible = True
       oApp.UserControl = True
   
       'Close the Database and Recordset
       rs.Close
       db.Close
   

4. Run the application and click the CommandButton. When the Automation code finishes running, you see the contents of the Products table in a new worksheet in Microsoft Excel.

MFC example

1. Start a new dialog-based MFC AppWizard EXE project named "ExcelData."

2. Using ClassWizard, add the wrapper classes for the Excel type library.

3. Add a button to the dialog resource IDD_EXCELDATA_DIALOG and add the following code to the button's handler in ExcelDataDlg.cpp:

   //For optional arguments
       COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
   
       CDaoDatabase db;
       CDaoRecordset rs;
       long lNumCols;
   
       //Get a recordset that represents all the records in the Products 
       //table of the sample Northwind database
       db.Open("C:\\Program Files\\Microsoft Office\\Office" \
               "\\Samples\\Northwind.mdb", FALSE, FALSE);
       rs.m_pDatabase = &db;    
       rs.Open(AFX_DAO_USE_DEFAULT_TYPE, "Select * From Products", 0);
       lNumCols = rs.GetFieldCount();
   
       //Start a new workbook in Excel
       _Application oApp;
       oApp.CreateDispatch("Excel.Application");
       if (!oApp)
       {
           AfxMessageBox("Cannot start Excel");
           return;
       }
       Workbooks oBooks = oApp.GetWorkbooks();
       _Workbook oBook = oBooks.Add(vOpt);
       Worksheets oSheets = oBook.GetWorksheets();
       _Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
       Range oRange;
   
       //Transfer the data in the recordset to the worksheet
       COleDispatchDriver rs2;
       rs2.AttachDispatch((LPDISPATCH) rs.m_pDAORecordset);
       oRange = oSheet.GetRange(COleVariant("A2"), vOpt);
       oRange.CopyFromRecordset((LPUNKNOWN) rs2.m_lpDispatch,
                                vOpt, vOpt);
       rs2.DetachDispatch();
       rs2.ReleaseDispatch();
   
       //Add the field names to row 1
       CDaoFieldInfo FieldInfo;
       for(long i=0; i<=lNumCols-1;i++)
       {
           oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
           oRange = oRange.GetOffset(vOpt, COleVariant(i));
           rs.GetFieldInfo(i, FieldInfo, AFX_DAO_PRIMARY_INFO);
           oRange.SetValue(COleVariant(FieldInfo.m_strName));
       }
   
       //Format the worksheet
       oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
       oRange = oRange.GetResize(COleVariant((short)1), 
                                 COleVariant(lNumCols));
       Font oFont = oRange.GetFont();
       oFont.SetBold(COleVariant((short)TRUE));
       oRange = oRange.GetEntireColumn();
       oRange.AutoFit();
   
       //Make Excel visible and give the user control
       oApp.SetVisible(TRUE);
       oApp.SetUserControl(TRUE);
   
   

4. Add the following includes to ExcelDataDlg.cpp:

   #include "Excel8.h" //or "Excel9.h" for Excel 2000
   #include <afxdao.h>
   

5. Modify CExcelDataApp::InitInstance() in ExcelData.cpp to start COM services:

       if(!AfxOleInit())
       {
           AfxMessageBox("Cannot initialize COM services.");
           return FALSE;
       }
   

6. Build and run the application. Click the button you added to the dialog box. When the Automation code finishes running, you see the contents of the Products table in a new worksheet in Microsoft Excel.

Additional notes

If you are using Microsoft Excel 2000, you can use either a DAO or ADO recordset with the CopyFromRecordset method. The Excel 97 CopyFromRecordset method supports only DAO recordsets.

For additional information on using an ADO recordset with the CopyFromRecordset method, please see the following Microsoft Knowledge Base article:

Q246335: HOWTO:Transfer Data from ADO Recordset to Excel with Automation

Automate Word to Perform a Mail Merge with Access Data

This section illustrates how you can create form letters in Microsoft Word using data from a database. This sample uses the Northwind Sample Database but you can create a mail merge in Word with data from any database for which you have an ODBC driver installed.

The following code samples create form letters based on a query of the "Customers" table in Northwind. The samples create a form letter for each record in the resulting query. The results resemble those in Figure 17.

Figure 17. Automating Word to create a mail merge from data in an ODBC database.

To create a mail merge with external data in Microsoft Word, you must create a User DSN for the OpenDataSource method of Word's MailMerge object. The sample code uses a DSN named "Northwind" that refers to Northwind.mdb. Before using the sample code, you must first create the DSN:

1. In Control Panel in Windows, double-click the ODBC Data Sources (32-bit) icon. The ODBC Data Source Administrator dialog box appears.
2. Click Add on the User DSN tab.
3. Select the Microsoft Access Driver, and click Finish.
4. When prompted for the new Data Source information, type Northwind for the name of the Data Source, and click Select to browse to Northwind.mdb.
5. Click OK to save the DSN information and then exit the ODBC Data Source Administrator.

Visual Basic example

1. Start a new project in Visual Basic. Form1 is created by default.

2. Reference the Microsoft Word object library.

3. Add a CommandButton to Form1 and add the following code to the Click event of that new CommandButton:

       Dim oApp As New Word.Application
       Dim oMainDoc As Word.Document
       Dim oSel As Word.Selection
   
       'Start a new main document for the mail merge
       Set oMainDoc = oApp.Documents.Add
   
       With oMainDoc.MailMerge
           .MainDocumentType = wdFormLetters
   
           'Set up the mail merge data source to the DSN "Northwind"
           .OpenDataSource Name:="", Connection:= "DSN=Northwind", _
                  SQLStatement:= "SELECT CompanyName, Address, " & _
                  "ContactName, City, Country, Region FROM Customers"
   
           'Add the field codes to the document to create 
           'the form letter
           With .Fields
               Set oSel = oApp.Selection  'Reduces interface requests
               .Add oSel.Range, "CompanyName"
               oSel.TypeParagraph
               .Add oSel.Range, "Address"
               oSel.TypeParagraph
               .Add oSel.Range, "City"
               oSel.TypeText ", "
               .Add oSel.Range, "Country"
               oSel.TypeParagraph
               oSel.TypeParagraph
               oSel.TypeText "Dear "
               .Add oSel.Range, "ContactName"
               oSel.TypeText ","
               oSel.TypeParagraph
               oSel.TypeParagraph
               oSel.TypeText " This letter is to inform you..."
               oSel.TypeParagraph
               oSel.TypeParagraph
               oSel.TypeText "Sincerely, John Smith"
           End With
       End With
   
       'Perform the mail merge to a new document
       With oMainDoc
           .MailMerge.Destination = wdSendToNewDocument
           .MailMerge.Execute Pause:=False
       End With
   
       'Make Word visible so that the user can see the new
       'mail merge document
       oApp.Visible = True
   

4. Run the application and click the CommandButton. When the Automation code finishes running, you see a new document in Microsoft Word that consists of one form letter for each record in the Customers table in the Northwind Sample Database.

MFC example

1. Start a new dialog-based MFC AppWizard EXE project named "MailMerge."

2. Using ClassWizard, add all of the wrapper classes for the Word type library.

3. Add a button to the dialog resource IDD_MAILMERGE_DIALOG and add the following code to the button's handler in MailMergeDlg.cpp:

       //For optional arguments
       COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
   
   
       //Start Word
       _Application oApp;
       oApp.CreateDispatch("Word.Application");
       if(!oApp)
       {
           AfxMessageBox("Cannot start Word.");
           return;
       }
   
       //Get the Documents collection so that you can add
       //a new Document for the mail merge "main document"
       Documents oDocs = oApp.GetDocuments();
       _Document oDoc = oDocs.Add(vOpt, vOpt);
       //Note for Word 2000: The Add method has 4 arguments in Word 2000. If
       //you wrapped the classes from the Word type library (msword9.olb),
       //modify the Add method to provide 4 optional arguments:
       //    oDoc = oDocs.Add(vOpt, vOpt, vOpt, vOpt);  
   
       //Get the MailMerge property of the new document
       MailMerge oMMerge = oDoc.GetMailMerge();
   
       //Set the document type as Form Letters
       oMMerge.SetMainDocumentType((long)0);   //wdFormLetters = 0
   
       //Set up the mail merge data source to the DSN "Northwind" and
       //an SQL statement
       CString sSQL;
       sSQL ="SELECT CompanyName, Address, ContactName, City, Country," \
             "Region FROM Customers";
       oMMerge.OpenDataSource("", vOpt, vOpt, vOpt, vOpt,
                              vOpt, vOpt, vOpt, vOpt, vOpt, vOpt,
                              COleVariant("DSN=Northwind"), 
                              COleVariant(sSQL), vOpt);
   
       //Add the field codes and text to the document
       Selection oSel = oApp.GetSelection();
       Range oRange;
   
       MailMergeFields oMMFlds = oMMerge.GetFields();
       MailMergeField oMMFld;
   
       oRange = oSel.GetRange();
       oMMFld = oMMFlds.Add(oRange, "CompanyName");
       oSel.TypeParagraph();
       oRange = oSel.GetRange();
       oMMFld = oMMFlds.Add(oRange, "Address");
       oSel.TypeParagraph();
       oRange = oSel.GetRange();
       oMMFld = oMMFlds.Add(oRange, "City");
       oSel.TypeText(", ");
       oRange = oSel.GetRange();
       oMMFld = oMMFlds.Add(oRange, "Country");
       oSel.TypeParagraph();
       oSel.TypeParagraph();
       oSel.TypeText("Dear ");
       oRange = oSel.GetRange();
       oMMFld = oMMFlds.Add(oRange, "ContactName");
       oSel.TypeText(",");
       oSel.TypeParagraph();
       oSel.TypeParagraph();
       oSel.TypeText("This letter is to inform you...");
       oSel.TypeParagraph();
       oSel.TypeParagraph();
       oSel.TypeText("Sincerely, John Smith");
   
       //Execute the mail merge and then close the
       //main document without saving changes
       oMMerge.SetDestination(0); //wdSendToNewDocument = 0
       oMMerge.Execute(COleVariant((short)FALSE));
       oDoc.Close(COleVariant((short)FALSE), vOpt, vOpt);
   
       //Make Word visible
       oApp.SetVisible(TRUE);
   
   

4. Add the following include to MailMergeDlg.cpp:

   #include "MSWord8.h"  //or "msword9.h" for Word 2000
   

5. Modify CMailMergeApp::InitInstance() in MailMerge.cpp to start COM services:

       if(!AfxOleInit())
       {
           AfxMessageBox("Cannot initialize COM services.");
           return FALSE;
       }
   

6. Build and run the application. Click the button you added to the dialog box. When the Automation code finishes running, you will see a new document in Microsoft Word that consists of one form letter for each record in the Customers table.

Automate PowerPoint to Create and Run a Slide Show

The next examples demonstrate how you can create a PowerPoint presentation and then view the slide show at run time. Throughout this process, the PowerPoint application itself remains hidden. The sample creates three slides as shown in Figure 18. The first slide contains two shapes with text, the second slide contains an embedded Microsoft Graph chart, and the third slide contains WordArt.

Figure 18. Automate PowerPoint to create and view a slide show at run time.

In this example, you modify the embedded Microsoft Graph object on the second slide; in other words, you automate Microsoft Graph as well as PowerPoint. To automate both, you need to use both the type library for Microsoft Graph and the type library for PowerPoint.

**Note to MFC Programmers   **Microsoft Graph and Microsoft PowerPoint contain classes with the same name. Use ClassWizard to generate wrapper classes from both type libraries. To avoid compile errors due to name conflicts, use a namespace for the wrapper classes for Microsoft Graph.

Visual Basic example

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add references to both the Microsoft Graph and the Microsoft PowerPoint type libraries.

3. Add a CommandButton to Form1.

4. Add the following code to the module for Form1:

   Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds _
       As Long)
   
   Private Sub Command1_Click()
   
       Dim oPPT As PowerPoint.Application
       Dim oPres As PowerPoint.Presentation
       Dim oSlide As PowerPoint.Slide
   
       'Create a new presentation in PowerPoint
       Set oPPT = New PowerPoint.Application
       Set oPres = oPPT.Presentations.Add(True)
   
       'SLIDE #1
       'Add a slide with a title
       Set oSlide = oPres.Slides.Add(1, ppLayoutTitle)
       oSlide.Shapes("Rectangle 2").TextFrame.TextRange.Text = _
           "Automating PowerPoint"
       oSlide.Shapes("Rectangle 3").TextFrame.TextRange.Text = _
           "To Create and View a Slideshow!"
   
       'SLIDE #2
       'Add a slide with an MSGraph chart.  Add data for the chart 
       'and format it with the Pie Chart style
       Set oSlide = oPres.Slides.Add(2, ppLayoutBlank)
       Dim oShape As PowerPoint.Shape
       Set oShape = oSlide.Shapes.AddOLEObject(20, 20, 660, 500, _
           "MSGraph.Chart")
   
       Dim oGraph As Graph.Chart
       Set oGraph = oShape.OLEFormat.Object
       With oGraph.Application.DataSheet
           .Cells.Delete
           .Cells(1, 2).Value = "John":    .Cells(2, 2).Value = 520
           .Cells(1, 3).Value = "Sue":     .Cells(2, 3).Value = 660
           .Cells(1, 4).Value = "Bill":    .Cells(2, 4).Value = 690
       End With
       oGraph.ChartType = xlPie
       oGraph.HasTitle = True
       oGraph.ChartTitle.Text = "Acme Corporation"
       oGraph.PlotArea.Border.LineStyle = xlLineStyleNone
       oGraph.Legend.Position = xlLegendPositionBottom
       oGraph.Application.Update
       oGraph.Application.Quit
   
       'SLIDE #3
       'Add another slide with Text Effects
       Set oSlide = oPres.Slides.Add(3, ppLayoutBlank)
       oSlide.Shapes.AddTextEffect 27, "The End", "Impact", _
              100, False, False, 200, 200
       'Note: msoTextEffect28 = 27
   
       'Apply a color scheme to all slides and apply slideshow 
       'settings to all slides
       With oPres.Slides.Range
           .ColorScheme = oPres.ColorSchemes(3)
           With .SlideShowTransition
              .EntryEffect = ppEffectBlindsVertical
               .AdvanceOnTime = True
               .AdvanceTime = 3
           End With
       End With
   
       'View the slide show
       Sleep 500
       With oPres.SlideShowSettings
           .AdvanceMode = ppSlideShowUseSlideTimings
           .Run
       End With
   
       'Wait until there are no more slide show windows and then quit 
       'PowerPoint
       Do
           Sleep 1000
       Loop While oPPT.SlideShowWindows.Count > 0
       oPPT.Quit
   
   End Sub
   

5. Run the application and click the CommandButton on Form1. The presentation is created on the fly while PowerPoint is hidden and then the slide show runs. When the slide show ends, the presentation is closed and PowerPoint quits.

MFC example

1. Start a new dialog-based MFC AppWizard EXE project named "SlideShow."

2. Using ClassWizard, add all of the wrapper classes for both the Microsoft Graph and Microsoft PowerPoint type libraries.

3. Because the wrapper classes for Microsoft Graph have the same name as wrapper classes for PowerPoint, encapsulate the wrapper classes for Microsoft Graph in a namespace in both Msgraph8.h and Msgraph8.cpp:

   namespace MSGraph
   {
   
   . . . 
   
   }
   

4. Add a button to the dialog resource IDD_SLIDESHOW_DIALOG and add the following code to the button's handler in SlideShowDlg.cpp:

   COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR); //For optional args
   
   _Application oApp;
   
   if(!oApp.CreateDispatch("Powerpoint.Application", NULL))
   {
       AfxMessageBox("Cannot start PowerPoint", MB_SETFOREGROUND);
       return;
   }
   
   Presentations oPresSet = oApp.GetPresentations();
   _Presentation oPres = oPresSet.Add(true);
   
   //SLIDE #1 ======================================================
   
   //Add a slide with a title
   Slides oSlides = oPres.GetSlides();
   _Slide oSlide = oSlides.Add(1, 1);  //ppLayoutTitle = 1
   
   //Add text to the shape "Rectangle 2"
   Shapes oShapes = oSlide.GetShapes();
   Shape oShape = oShapes.Item(COleVariant("Rectangle 2"));
   TextFrame oTxtFrm = oShape.GetTextFrame();
   TextRange oTxtRng = oTxtFrm.GetTextRange();
   oTxtRng.SetText("Automating PowerPoint");
   
   //Add text to the shape "Rectangle 3"
   oShape = oShapes.Item(COleVariant("Rectangle 3"));
   oTxtFrm = oShape.GetTextFrame();
   oTxtRng = oTxtFrm.GetTextRange();
   oTxtRng.SetText("To Create and View a SlideShow");
   
   //SLIDE #2 ======================================================
   
   //Add a blank slide
   oSlide = oSlides.Add(2, 12); //ppLayoutBlank = 12
   
   //Add a chart to the slide
   oShapes = oSlide.GetShapes();
   oShape = oShapes.AddOLEObject(20, 20, 660, 500, "MSGraph.Chart", 
                                 "", 0, "", 0, "", 0);
   
   //Get the Chart object so that you can automate MSGraph
   OLEFormat oOLEFmt = oShape.GetOLEFormat();
   MSGraph::Chart oChart = oOLEFmt.GetObject();
   MSGraph::Application oGraphApp = oChart.GetApplication();
   
   //Modify the chart's datasheet
   MSGraph::DataSheet oData = oGraphApp.GetDataSheet();
   MSGraph::Range oCells = oData.GetCells();
   MSGraph::Range oCell;
   COleVariant vCell;
   oCells.Delete(vOpt);
   
   vCell = oCells.GetItem(COleVariant((short)1), COleVariant((short)2));
   oCell.AttachDispatch(vCell.pdispVal);
   oCell.SetValue(COleVariant("John"));
   vCell = oCells.GetItem(COleVariant((short)2), COleVariant((short)2));
   oCell.AttachDispatch(vCell.pdispVal);
   oCell.SetValue(COleVariant((short)520));
   vCell = oCells.GetItem(COleVariant((short)1), COleVariant((short)3));
   oCell.AttachDispatch(vCell.pdispVal);
   oCell.SetValue(COleVariant("Sue"));
   vCell = oCells.GetItem(COleVariant((short)2), COleVariant((short)3));
   oCell.AttachDispatch(vCell.pdispVal);
   oCell.SetValue(COleVariant((short)660));
   vCell = oCells.GetItem(COleVariant((short)1), COleVariant((short)4));
   oCell.AttachDispatch(vCell.pdispVal);
   oCell.SetValue(COleVariant("Bill"));
   vCell = oCells.GetItem(COleVariant((short)2), COleVariant((short)4));
   oCell.AttachDispatch(vCell.pdispVal);
   oCell.SetValue(COleVariant((short)690));
   
   //Set the chart's type
   oChart.SetChartType(5); //xlPie = 5
   
   //Remove the border from the plot area
   MSGraph::PlotArea oPlot = oChart.GetPlotArea();
   MSGraph::Border oBrdr = oPlot.GetBorder();
   oBrdr.SetLineStyle(COleVariant((long)-4142));//xlLineStyleNone = 4142
   
   //Position the legend
   MSGraph::Legend oLegend = oChart.GetLegend();
   oLegend.SetPosition((long)(-4107)); //xlLegendPositionBottom = -4107
   
   //Modify the chart's title
   oChart.SetHasTitle(TRUE);
   MSGraph::ChartTitle oChartTtl = oChart.GetChartTitle();
   oChartTtl.SetText("ACME Corporation");
   
   //Save changes to the chart and close MSGraph
   oGraphApp.Update();
   oGraphApp.Quit();
   
   
   
   //SLIDE #3 ======================================================
   
   //Add a blank slide
   oSlide = oSlides.Add(3, 12); //ppLayoutBlank = 12
   
   //Add Text Effects to the slide
   oShapes = oSlide.GetShapes();
   oShapes.AddTextEffect(27,   //msoTextEffect28 = 27
                         "The End","Impact", 100, 0, 0, 200, 200);
   
   
   //Apply a color scheme to all slides
   SlideRange oSlideRng = oSlides.Range(vOpt);
   ColorSchemes oSchemes = oPres.GetColorSchemes();
   ColorScheme oScheme = oSchemes.Item(3);
   oSlideRng.SetColorScheme(oScheme);
   
   //Set up slide show settings
   SlideShowTransition oSST = oSlideRng.GetSlideShowTransition();
   oSST.SetEntryEffect(770);   //ppEffectBlindsVertical = 770
   oSST.SetAdvanceOnTime(TRUE);
   oSST.SetAdvanceTime(3);
   
   //View the show
   ::Sleep(500);
   SlideShowSettings oSSS = oPres.GetSlideShowSettings();
   oSSS.SetAdvanceMode(2);     //ppSlideShowUseSlideTimings = 2
   SlideShowWindow oShowWindow = oSSS.Run();
   
   //Wait until there are no more slide show windows and then
   //quit PowerPoint
   SlideShowWindows oShowWindows = oApp.GetSlideShowWindows();
   do
   {
       ::Sleep(500);
   }
   while (oShowWindows.GetCount()>0);
   oApp.Quit();
   ::Sleep(100);
   

5. Add the following includes to SlideShowDlg.cpp:

   #include "MSPPT8.h"  // or "MSPPT9.h" for PowerPoint 2000
   #include "Graph8.h"  //or "Graph9.h" for Graph 2000
   

6. Modify CSlideShowApp::InitInstance() in SlideShow.cpp to start COM services:

       if(!AfxOleInit())
       {
           AfxMessageBox("Cannot initialize COM services.");
           return FALSE;
       }
   

7. Build and run the application. Click the button you added to the dialog box. The presentation is created on the fly while PowerPoint is hidden and then the slide show runs. When the slide show ends, the presentation closes and PowerPoint quits.

Automate an Office Application to Obtain a Document's Properties

Microsoft Office applications create OLE compound documents that store document properties, such as title, subject, author, creation date, and so forth. Each application has its own set of built-in document properties but you can also add your own custom document properties. In an Office application's user interface, you can change and view these document properties. To display a document's properties while in an Office application, on the File menu, click the Properties command and the Properties dialog box appears. You can also access these document properties by automating a Microsoft Office application. The next samples illustrate how to automate Word to retrieve built-in document properties from a Word document. In both samples, you create a dialog that resembles the one illustrated in Figure 19.

Figure 19. This illustration represents the suggested layout of the form (or dialog box) you create for your sample application to retrieve document properties.

Visual Basic sample

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a reference to the Microsoft Word 8.0 (or 9.0) object library.

3. On the Project menu, click Components. Check the Microsoft Common Dialog control and click OK.

4. Add controls to Form1 and set properties for the controls as outlined in the following table. Note that you can use the form illustrated in Figure 19 for a suggested layout.

   Control Type	Property
   Label	Name	lblFilename
   	Caption	Select a Word Document
   Label	Name	lblProperty
   	Caption	Property
   Label	Name	lblValue
   	Caption	Value
   Combo Box	Name	cboProperty
   	Enabled	False
   	Style	2—Drop-down List
   TextBox	Name	txtValue
   	Locked	True
   	Multiline	True
   CommandButton	Name	cmdSelectFile
   	Caption	Select a Document…
   CommandButton	Name	cmdClose
   	Caption	Close
   CommonDialog	Name	CommonDialog

   5. In this sample application, you have variables with form-level scope that reference a Word Application object that you reuse for the lifetime of the form, a Word Document object for which you want to retrieve document properties, and a string for the filename of the document you select.

      Add the following to the General Declarations section of Form1:

      Dim oWordApp As Word.Application
      Dim oWordDoc As Word.Document
      Dim sFile As String 'Filename of selected file
      

   6. When the form loads, it starts an instance of Word that it will use for the lifetime of the form. To obtain a list of the built-in document properties that Word documents support and add them to the Combo Box, you can start a new document in Word, iterate its BuiltInDocumentProperties collection, and then close the document because it is no longer needed.

      Add the following event handling code to Form1:

      Private Sub cmdClose_Click()
          Unload Me
      End Sub
      
      
      Private Sub Form_Load()
          'Start up a new hidden instance of Word
          Set oWordApp = New Word.Application
      
          'Create a new document just so that you can retrieve the list 
          ' of built-in document property names and then close the 
          'document since it is no longer needed
          Set oWordDoc = oWordApp.Documents.Add
          Dim oProp As Object
          For Each oProp In oWordDoc.BuiltInDocumentProperties
              cboProperty.AddItem oProp.Name
          Next
          oWordDoc.Close SaveChanges:=False
          Set oWordDoc = Nothing
      End Sub
      
      Private Sub Form_Unload(Cancel As Integer)
          'Close the open document and quit the instance of Word
          If Not (oWordDoc Is Nothing) Then
             oWordDoc.Close SaveChanges:=False
          End If
          oWordApp.Quit
          Set oWordDoc = Nothing
          Set oWordApp = Nothing
      End Sub
      

   7. When you click cmdSelectFile at run time, you are prompted to select a .doc file. Once a file is selected, you then open that document in the instance of Word you started in the Form's Load event.

      Add the following code to the click event of cmdSelectFile:

      Private Sub cmdSelectFile_Click()
      
          'Use the CommonDialog to allow the user to select a file
          With CommonDialog
              .FileName = ""
              .Filter = "Microsoft Word Document (*.doc)|*.doc"
              .ShowOpen
              sFile = .FileName
          End With
      
          'If a file was selected, then set the caption for lblFilename
          'Close the previously opened Word document (if one was open)
          'and then open the newly selected Word document as read-only
          If sFile <> "" Then
              lblFilename.Caption = sFile
              If Not (oWordDoc Is Nothing) Then
                  oWordDoc.Close SaveChanges:=False
                  Set oWordDoc = Nothing
              End If
              Set oWordDoc = oWordApp.Documents.Open(FileName:=sFile, _
                                                     ReadOnly:=True)
              cboProperty.Enabled = True
              cboProperty.ListIndex = -1
              txtValue.Text = ""
          End If
      
      End Sub
      

   8. When you select a document property in the Combo Box, you query the open document in Word for the value of that property.

      **Note   **If Microsoft Word does not define a value for a BuiltinDocumentProperty, reading the Value for that property generates a run-time error, so you must trap for that error and handle it accordingly.

      Add the following code to the Click event of the Combo Box cboProperty:

      Private Sub cboProperty_Click()
          'Retrieve the selected document property and add it to 
          'the textbox txtValue
          On Error Resume Next
          txtValue.Text = oWordDoc.BuiltInDocumentProperties( _
                                  cboProperty.Text).Value
          If Err <> 0 Then
              txtValue.Text = _
                   "<The property you selected is not available>"
          End If
          On Error GoTo 0
      End Sub
      

   9. Run the application. Click the button cmdSelectFile and select a Word document. Once you have selected the document, the list of built-in document properties for that document appears in the Combo Box. Select any property in the list and its value appears in the text box. If you select a document property that does not have an associated value, the TextBox displays the message:

      "<The property you selected is not available>."

   MFC sample

   1. Create a new MFC Appwizard EXE that is dialog-based and name it "DocProps."

   2. Using ClassWizard, add all of the classes in the Microsoft Word 8.0 (or 9.0) type Library.

   3. Select the dialog resource IDD_DOCPROPS_DIALOG. Change the ID of the IDOK button to IDC_SELECT_DOC, change its caption to Select a Document and deselect the Default button style property. Change the caption of the IDCANCEL button to Close.

   4. Add the following controls to the dialog as well. Use Figure 19 as a suggested layout for your controls.

      Control Type	Property
      Static Text	ID	IDC_FILENAME
      	Caption	Select a Word Document
      Static Text	Name	IDC_PROPERTY_LABEL
      	Caption	Property
      Static Text	ID	IDC_VALUE_LABEL
      	Caption	Value
      Combo Box	ID	IDC_PROPERTY_LIST
      	Disabled	Checked
      Edit Box	ID	IDC_PROPERTY_VALUE
      	Read-only	Checked
      	Auto HScroll	Unchecked
      	Multiline	Checked

      > **Note   **The default size of a Combo Box does not provide space for the drop-down portion containing the list. You can resize it by selecting it, then clicking directly on the drop-down arrow at the right side of the box. This provides a vertical resize handle in the center of the edit portion so that you can resize the drop-down portion for the list.

      5. In DocPropsDlg.h, include the Word wrapper classes:

         
             #include "msword8.h"  //or "msword9.h" for Word 2000
         
         and add the following data members to the CDocPropsDlg class:
         
             _Application m_oApp;
             Documents m_oDocs;
             _Document m_oDoc;
         

      6. When the dialog loads, it starts a new Word instance with which you examine document properties. Once Word is started, it creates a new Word document for the sole purpose of extracting a list of built-in document properties to populate the Combo Box.

         Add the following code to the CDocPropsDlg::OnInitDialog handler:

             COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
             DISPPARAMS dpNoArgs = {NULL, NULL, 0, 0};
             HRESULT hr;
             VARIANT vResult;
         
             //Start Word
             AfxOleInit();
             if(!m_oApp.CreateDispatch("Word.Application", NULL))
             {
                 AfxMessageBox("Unable to start Word");
                 return false;
             }
         
             //Create a new document in Word
             m_oDocs = m_oApp.GetDocuments();
             m_oDoc = m_oDocs.Add(vOpt, vOpt);
         
             //Note for Word 2000: The Add method has 4 arguments in Word 
             //2000.  If you wrapped the classes from the Word type library 
             //(msword9.olb), modify the Add method to provide 4 optional 
             //arguments:
             //    oDoc = oDocs.Add(vOpt, vOpt, vOpt, vOpt);  
         
         
             //Get the IDispatch pointer to the BuiltInDocumentProperties 
             //collection object
             LPDISPATCH lpdispProps;
             lpdispProps = m_oDoc.GetBuiltInDocumentProperties();
         
             //Retrieve a count of the number of BuiltinDocumentProperties
             //NOTE: The DISPID of the "Count" property of a 
             //      DocumentProperties collection is 0x4
             hr = lpdispProps->Invoke(0x4, IID_NULL, LOCALE_USER_DEFAULT,
                     DISPATCH_PROPERTYGET, &dpNoArgs, &vResult, NULL, NULL);
             long lPropCount = vResult.lVal;
         
             CComboBox* pcboPropList = 
                          (CComboBox*) GetDlgItem(IDC_PROPERTY_LIST);
         
             char szPropName[255];
         
             DISPPARAMS dpItem;
             VARIANT vArgs[1];
             vArgs[0].vt = VT_I4;
             vArgs[0].lVal = 0;
             dpItem.cArgs=1;
             dpItem.cNamedArgs=0;
             dpItem.rgvarg = vArgs;
         
             for(long i=1; i<=lPropCount; i++)
             {
                 //Retrieve a DocumentProperty
                 //NOTE: The DISPID of the "Item" property of a 
                 //      DocumentProperties object is 0x0
                 dpItem.rgvarg[0].lVal = i;
                 hr = lpdispProps->Invoke(0x0, IID_NULL,
                         LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &dpItem,
                         &vResult, NULL, NULL);
         
                 //Get the name of the DocumentProperty
                 //NOTE: The DISPID of the "Name" property of a 
                 //      DocumentProperty object is 0x3
                 LPDISPATCH lpdispProp = vResult.pdispVal;
                 hr = lpdispProp->Invoke(0x3, IID_NULL, LOCALE_USER_DEFAULT,
                         DISPATCH_PROPERTYGET, &dpNoArgs, &vResult, NULL, 
                         NULL);
         
                 //Add the property name to the Combo Box
                 wcstombs(szPropName, vResult.bstrVal, 255);
                 pcboPropList->InsertString(-1, szPropName);
         
                 //Release the IDispatch interface for the 
                 //BuiltinDocumentProperty object
                 lpdispProp->Release();
             }
         
             //Release the IDispatch interface for the 
             //BuiltinDocumentProperties collection
             lpdispProps->Release();
         
             //Close the no longer needed document
             m_oDoc.Close(COleVariant((short)false), vOpt, vOpt);
             m_oDoc.ReleaseDispatch();
             m_oDoc.m_lpDispatch = NULL;
         

      7. When the dialog box appears at run time, you can click Select a Document to select the Word document for which you want to examine document properties. Therefore, the handler for this button displays a Windows Open dialog box to retrieve the full path and filename of the selected Word document, opens the document in the instance of Word you started, closes the previously opened Word document (if any), and enables the Combo Box.

         Add the following code to the BN_CLICKED handler of the button IDC_SELECT_DOC:

         void CDocPropsDlg::OnSelectDoc() 
         {
             char szFilter[] = "Microsoft Word Document(.doc)|*.doc||";
         
             CFileDialog dlg(TRUE, NULL, NULL, OFN_HIDEREADONLY |
                                 OFN_OVERWRITEPROMPT, szFilter);
         
             if(dlg.DoModal()==IDOK)
             {
                 CString sFile = dlg.GetPathName();
                 SetDlgItemText(IDC_FILENAME, sFile);
                 CComboBox* pcboPropList = 
                       (CComboBox*)GetDlgItem(IDC_PROPERTY_LIST);
                 pcboPropList->EnableWindow(TRUE);
         
                 //Close the previously opened document if one was open and 
                 //then open the newly selected document as read-only
                 COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
                 if(m_oDoc.m_lpDispatch!=NULL)
                 {
                     m_oDoc.Close(COleVariant((short)false), vOpt, vOpt);
                     m_oDoc.ReleaseDispatch();
                     m_oDoc.m_lpDispatch = NULL;
                 }
                 m_oDoc = m_oDocs.Open(COleVariant(sFile), vOpt, 
                              COleVariant((short)true), vOpt, vOpt, vOpt, 
                              vOpt, vOpt, vOpt, vOpt);
         
                 //Note for Word 2000: The Open method has 12 arguments in 
                 //Word 2000.  If you wrapped the classes from the Word 
                 //type library (msword9.olb), modify the Open method above
                 //so that you are passing two additional optional 
                 //arguments.
         
             }
         }
         

      8. When you select a property name in the combo box, the value corresponding to that property for the open Word document is displayed in the edit box. Add the following code to the CBN_SELCHANGE handler for the IDC_PROPERTY_LIST combo box:

         void CDocPropsDlg::OnSelchangePropertyList() 
         {
             //When the Selection changes, retrieve the value of the 
             //selected document property
             CString sProperty;
             CComboBox* pcboPropList = 
                        (CComboBox*)GetDlgItem(IDC_PROPERTY_LIST);
             pcboPropList->GetLBText(pcboPropList->GetCurSel(), sProperty);
         
             //Get the BuiltinDocumentProperties collection for the 
             //document
             LPDISPATCH lpdispProps;
             lpdispProps = m_oDoc.GetBuiltInDocumentProperties();
         
             //Get the requested Item from the BuiltinDocumentProperties 
             //collection
             //NOTE:  The DISPID of the "Item" property of a 
             //       DocumentProperties object is 0x0
             VARIANT vResult;
             DISPPARAMS dpItem;
             VARIANT vArgs[1];
             vArgs[0].vt = VT_BSTR;
             vArgs[0].bstrVal = sProperty.AllocSysString();
             dpItem.cArgs=1;
             dpItem.cNamedArgs=0;
             dpItem.rgvarg = vArgs;
             HRESULT hr = lpdispProps->Invoke(0x0, IID_NULL, 
                               LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, 
                                &dpItem, &vResult, NULL, NULL);
             ::SysFreeString(vArgs[0].bstrVal);
         
             //Get the Value property of the BuiltinDocumentProperty
             //NOTE:  The DISPID of the "Value" property of a 
             //       DocumentProperty object is 0x0
             DISPPARAMS dpNoArgs = {NULL, NULL, 0, 0};
             LPDISPATCH lpdispProp;
             lpdispProp = vResult.pdispVal;
             hr = lpdispProp->Invoke(0x0, IID_NULL, LOCALE_USER_DEFAULT, 
                            DISPATCH_PROPERTYGET, &dpNoArgs, &vResult, 
                            NULL, NULL);
         
             //Set the text in the Edit Box to the property's value
             CString sPropValue = "";
             switch (vResult.vt)
             {
             case VT_BSTR:
                 sPropValue = vResult.bstrVal;
                 break;
             case VT_I4:
                 sPropValue.Format("%d",vResult.lVal);
                 break;
             case VT_DATE:
                 {
                     COleDateTime dt (vResult);
                     sPropValue = dt.Format(0, LANG_USER_DEFAULT);
                     break;
                 }
             default:
                 sPropValue = "<Information for the property you selected \
                              is not available>";
                 break;
             }
             SetDlgItemText(IDC_PROPERTY_VALUE, sPropValue);
         
             //Release the no longer needed IDispatch pointers
             lpdispProp->Release();
             lpdispProps->Release();
         }
         

      9. When you click Close in the dialog box, clean up by quitting the instance of Word that you started. Add the following code to the handler for the IDCANCEL button:

         void CDocPropsDlg::OnCancel() 
         {
             //Quit Microsoft Word without saving changes to
             //any open documents
             COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
             m_oApp.Quit(COleVariant((short)false), vOpt, vOpt);
         
            CDialog::OnCancel();
         }
         

      10. Build and run the sample application. When the dialog is loaded, click Select a Document and choose any Word document. The document is opened in the hidden instance of Word. Choose a property from the Combo Box, and note that the property's value appears in the Edit Box.

      Additional notes for working with Office document properties

      If you want to see an additional sample for automating an Office application to retrieve document properties (both built-in and custom), please see the following article in the Microsoft Knowledge Base:

      Q238393 HOWTO: Use Visual C++ to Access DocumentProperties with Automation

      Note that you can also retrieve document property information without Automation and even without the need for the Office application that created the file. Office documents are OLE compound documents that store document properties in persistent property sets. These property sets are managed by COM/OLE and can be retrieved using the IPropertySetStorage and IPropertyStorage interfaces. For details, see:

      Q186898 HOWTO: Read Compound Document Properties Directly with VC++

      Q224351 FILE: DSOFILE.EXE Lets You Read Document Properties w/o Office

      Troubleshooting Common Problems

      PROBLEM:
      Why does the Automation server remain in memory even after my Automation code ends?

      There are several potential causes of this problem:

      • With C/C++ and MFC Automation clients, an unreleased interface pointer is commonly the culprit. Verify that you release acquired interfaces properly before your Automation code ends. Also, check the methods and properties that you are invoking; if you ignore the return value of an invoked method or property that returns an LPDISPATCH, the reference count for the object might not be decremented when your code ends.

      • If you are making the Automation server visible so that the user can interact with the server, insure that you are properly giving the user control of the application before your Automation code ends. Some Automation clients, such as Microsoft Excel, require that you give the user control of the application by setting a property, the UserControl property. When automating Microsoft Excel, if you make the application visible without setting the UserControl property to True, the reference count for the application is not properly decremented.

      • With Visual Basic Automation clients, make sure that you have properly qualified all methods and properties with an object variable that you set at run time. For more information on properly qualifying methods and properties, see the section "Creating an Automation Client with Visual Basic" in this article. Also see the following articles in the Microsoft Knowledge Base:

        Q189618 PRB: Automation Error Calling Unqualified Method or Property

        Q178510 PRB: Excel Automation Fails Second Time Code

      • If you have Automation code in a tight loop and are requesting close to or more than 64-KB interfaces, you might be running into a limitation with Windows 95 or Windows 98. To correct this problem, reduce the number of interface requests to avoid hitting the 64-KB limit for interface requests. For more details, see the section "Improving Your Automation Code: Tip #5—Minimize Interface Requests" in this article.

      PROBLEM:
      In my Visual Basic Automation client, I receive the run-time error 429 while attempting to start and attach to the Automation server. What could be wrong?

      This error is usually caused by a problem with the Automation server installation or a problem with the way that the Automation server is registered. For tips on troubleshooting run-time error 429, see the following article in the Microsoft Knowledge Base:

      Q244264 INFO: Troubleshooting Error 429 When Automating Office Applications

      PROBLEM:
      My Automation code works as expected the first time but fails on the second attempt. What could the problem be?

      This problem typically occurs in Visual Basic Automation clients in which you have not properly qualified a method or property with an object variable that you have set at run time. For more information on properly qualifying methods and properties, see the section "Creating an Automation Client with Visual Basic" in this article. Also see the following articles in the Microsoft Knowledge Base:

      Q189618 PRB: Automation Error Calling Unqualified Method or Property

      Q178510 PRB: Excel Automation Fails Second Time Code

      PROBLEM:
      I am receiving long Automation errors, such as "–2147418094 (80010012)" in my Visual Basic Automation client. What does this error mean?

      When automating another application with Visual Basic, you might receive an error similar to the following:

      Run-time error '-2147418094 (80010012)':
      Automation Error.

      The value -2147418094 is the decimal representation of the error code; 80010012 is the hexadecimal representation of the same code. Depending on the source of the error (it is either a COM error or an error generated by the Automation server), it can be interpreted in one of two ways:

      1. If it is a COM error, you can use the Error Lookup utility or the FormatMessage API function to retrieve a textual description of the error message.
      2. If it is an Automation error returned from the server, it cannot be translated using Error Lookup or FormatMessage. Instead, you need to refer to the documentation for the Automation server. Typically, with application-specific errors, the last four digits of the hexadecimal representation of the error code refer to the application-specific error. For example, if you are automating Microsoft Word and you receive an Automation error -2146823136 (0x800a1420), if you convert the last four digits of the hexadecimal value (1420) to decimal, you get 5152. You can then examine the documentation for the server to determine the meaning of run-time error 5152.

      For more information on translating Automation errors, see the following articles in the Microsoft Knowledge Base:

      Q186063 INFO: Translating Automation Errors for VB/VBA (Long)

      Q238986 INFO: Translating Large Office Automation Error Values

      Q2244491: WRD97ERR.DOC Contains a List of Word 97 Automation Errors

      PROBLEM:
      When I run my Automation code, the server displays a dialog that interrupts code execution and requires user input. How can I avoid this?

      Most Microsoft Office applications have a DisplayAlerts property that prevents "alert" messages from appearing while your Automation code runs. Set the DisplayAlerts property of the Application object to False to avoid alert messages.

      An example of an alert is a prompt you might receive when you programmatically call a method to save a document to a file that already exists. In this scenario, the prompt you receive is a confirmation that you want to overwrite the file. You can avoid this type of message so that no user intervention is required by setting DisplayAlerts to False so that the file is automatically overwritten.

      PROBLEM:
      My Automation client worked fine with the Office 97 version of my application. However, I rebuilt my project and it works fine with Office 2000 but now fails with Office 97. What could be wrong?

      New versions of Office include new features and enhance some of the existing ones. To provide clients with programmatic access to these new and enhanced features, the object models must be updated. Because of this update, a method may have more arguments for Office 2000 than it did with Office 97.

      The new arguments to existing methods are usually optional. If you use late binding to the Office Automation server, your code should work successfully with either Office 97 or Office 2000. However, if you use early binding, the differences between the 97 and 2000 type libraries could cause you problems in the following situations:

      • If you create an Automation client in Visual Basic and reference the Office 2000 type library, your code might fail when using an Office 97 server if you call a method or property that has changed.
      • If you create an MFC Automation client and use the ClassWizard to wrap classes from the Office 2000 type library, your code might fail when using an Office 97 server if you call a method or property that has changed.

      To avoid this problem, you should develop your Automation client against the lowest version of the Office server you intend to support. For the best results in maintaining compatibility with multiple versions of Office, you should use late binding. However, if you choose to use early binding, bind to the type library for the earliest version of the Office server you want to support. To illustrate, if you are writing an Automation client with Visual Basic and want that client to work with Excel 97 and Excel 2000, you should reference the Excel 97 type library in your Visual Basic project. Likewise, if you are writing an Automation client using MFC, you should use the ClassWizard to wrap the Excel 97 type library.

      For more information, please see the following article in the Microsoft Knowledge Base:

      Q224925 INFO: Type Libraries for Office 2000 Have Changed

      PROBLEM:
      When I attempt to automate a running instance of an Office application, it fails. What could be wrong?

      Microsoft Office applications do not immediately register their running objects in the Running Object Table (ROT) as soon as they are started. Instead, a shelled Office application waits to register itself until it has lost focus. It does so to optimize the application's launch process. If you programmatically execute an Office application and then immediately call ::GetActiveObject in C/C++ (or GetObject in Visual Basic) to attach to the running instance you just started, the call might fail because the application has not registered. To successfully attach to an instance of an Office application you just executed, force the Office application to lose focus so that it registers its running objects in the ROT.

      For more information and sample code, please see the following article in the Microsoft Knowledge Base:

      Q238610 PRB: GetObject or GetActiveObject Can't Find a Running Office Application

      For More Information

      For information and sample code for integrating Office with Visual Basic, Visual C++, Internet Scripts, and other programming languages, please see the following Web sites:

      https://support.microsoft.com/ph/8753#tab0

      https://msdn.microsoft.com/office

      --------------------------------------------

      © 1999 Microsoft Corporation. All rights reserved.

      The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

      This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

      Microsoft, Visual Basic, Visual C++, Visual J++, Outlook, and PowerPoint are either registered trademarks registered trademarks or trademarks of Microsoft Corporation in the United States and other countries.

      Other product or company names mentioned herein may be the trademarks of their respective owners.

      Microsoft Corporation · One Microsoft Way · Redmond, WA 98052-6399 · USA

      0X97Part no. 098-XXXXX