FAQ: Office Automation Using Visual C++

ID: Q196776


The information in this article applies to:


SUMMARY

This article answers common questions concerning Automation to Microsoft Office from Visual C++.


MORE INFORMATION

Table of Contents

  1. What is Automation?


  2. I'm new to Automation, where can I find good resources to learn more?


  3. Are there different ways I can use Automation?


  4. What is COM?


  5. How do I attach to the running instance of an Office application?


  6. How do I pass optional parameters?


  7. How do I catch events exposed by the Office applications?


  8. My automation code is too slow. How can I speed things up?


  9. What do these huge error values, like -2147352573 or 0x80030002, mean?


  10. What is a type library?


  11. My automation code worked with Microsoft Excel 95, but fails with Microsoft Excel 97. Why?


  12. Why does the application I'm automating stay in memory after my program is finished?


  13. I know what I want to do as a Microsoft Office application user, but how do I do this programmatically using Automation?


  14. Can I automate an embedded Microsoft Office application?


  15. How do I access my document properties in a Microsoft Office document?


Questions and Answers

  1. What is Automation?

    Automation (formerly OLE Automation) is a technology that allows you to take advantage of an existing program's functionality and incorporate it into your own applications. For instance, you can utilize the Microsoft Word spelling and grammar checking capabilities into your application without Microsoft Word visible to your users. You can even use all of the Microsoft Excel charting, printing, and data analysis tools. This technology can greatly simplify and speed up your development.


  2. I'm new to Automation, where can I find good resources to learn more?

    Chapter 24 of David Kruglinski's "Inside Visual C++" (ISBN:1-57231-565- 2) supplies a general overview as well as some great examples. Also, the Microsoft Knowledge Base is a good source of information. This article itself is a good start, and you can find more specific references in the following article in the Microsoft Knowledge Base:
    Q152023 Locating Resources to Study OLE Automation
    If you prefer learning by example, please see the following article in the Microsoft Knowledge Base:
    Q179706 HOWTO Use MFC to Automate Excel & Create/Format a New Workbook


  3. Are there different ways I can use Automation?

    There are three basic ways you can use Automation: MFC, #import, and C/C++. It is important to note that there are some slight differences between Automation from C++ compared to plain C, because COM was designed around the C++ class. For more information, please see the following article in the Microsoft Knowledge Base for a C example:
    Q181473 HOWTO: Use OLE Automation from a C Application


  4. What is COM?

    Automation is based on the Component Object Model (COM). COM is a standard software architecture based on interfaces, and designed to have code separated into self-contained objects. Think of it as an extension of the Object Oriented Programming (OOP) paradigm, but applicable to separate applications. Each object exposes a set of interfaces, and all communication to an object, such as initialization, notifications, and data transfer, happens through these interfaces.

    COM is also a set of services provided by dynamic-link libraries (DLLs) installed with the operating system. Automation uses many of those services. One example is the "Marshalling" service, which packages the client application's calls to the member functions of the server application's interfaces and passes those, with their arguments, to the server application. It makes it appear that the server's interfaces are exposed in the client's memory space, which is not the case when the client is an .exe running in its own process space. Marshalling also gets the return values from the server's methods back across the process boundaries and safely into the hands of the client's call. There are many other services essential to Automation that are provided by the various COM libraries. Sources of information about those include "Inside Ole - Second Edition" by Kraig Brockschmidt, ISBN 1-55615-843-2, "Inside COM" by Dale Rogerson - ISBN 1-57231-349-8, and "Automation Programmer's Reference," ISBN 1-57231-584-9.


  5. How do I attach to the running instance of an Office application?

    Use the GetActiveObject() API. Automation servers register themselves in the ROT (Running Object Table), via the RegisterActiveObject() API. Automation clients can get at the running instance with code such as:
    
          // Translate server ProgID into a CLSID. ClsidFromProgID
          // gets this information from the registry.
          CLSID clsid;
          CLSIDFromProgID(L"Excel.Application", &clsid);  
    
          // Get an interface to the running instance, if any..
          IUnknown *pUnk;
          HRESULT hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);
          ASSERT(!FAILED(hr));
    
          // Get IDispatch interface for Automation...
          IDispatch *pDisp;
          hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pDisp);
          ASSERT(!FAILED(hr));
    
          // Release the no-longer-needed IUnknown...
          pUnk->Release();
     
    NOTE: If there are multiple instances running of the Office application you want to attach, you will only be able to attach to the first instance that was launched using the GetActiveObject() API.

    Theoretically, you can iterate the ROT for each individual instance, but the Office apps don't register themselves if another instance is already in the ROT because the moniker for itself is always the same (it couldn't be distinguished anyway). This means that you can't attach to any instance except for the first. However, because the Office apps also register their documents in the ROT, you can successfully attach to other instances by iterating the ROT looking for a specific document, attaching to it, then getting the Application object from it. There is some code in the following Microsoft Knowledge Base article for iterating the ROT and looking for a document name:
    Q190985 HOWTO: Get IDispatch of an Excel or Word Document From an OCX
    You won't need to do this for PowerPoint, because it is a single- instance application; you can only have one instance of it running.


  6. How do I pass optional parameters?

    Some methods have "optional" parameters. In Visual Basic, you can casually omit them when calling the method. However, when calling with Visual C++ you have to pass a special VARIANT whose .vt field is VT_ERROR, and .scode field is DISP_E_PARAMNOTFOUND. That is:
    
          // VARIANT used in place of optional-parameters.
          VARIANT varOpt;
          varOpt.vt = VT_ERROR;
          varOpt.scode = DISP_E_PARAMNOTFOUND; 
    This is really what Visual Basic is doing behind-the-scenes.


  7. How do I catch events exposed by the Office applications?

    Basically you implement the event interface you want to catch (the "sink"), and setup an advisory connection with the application (the "source"). The following two articles give you step-by-step examples for Microsoft Word and Excel:
    Q183599 HOWTO: Catch Microsoft Word97 Application Events Using VC++

    Q186427 HOWTO: Catch Microsoft Excel 97 Application Events Using VC++
    In general, to setup the advisory connection, you get the server's IConnectionPointContainer and call FindConnectionPoint() with the IID of the event interface. This gives you an IConnectionPoint interface and all that's left is to call Advise() with an instance of your event interface. The server will then call back through this interface when these events occur.


  8. My automation code is too slow. How can I speed things up?

    A common cause of speed problems with Automation is with repetitive reading and writing of data. This is typical for Excel Automation clients. However, most people aren't aware that this data can usually be written or read all at once using SAFEARRAY. See the following Microsoft Knowledge Base articles for more information and informative examples:
    Q186120 HOWTO: Use MFC to Automate Excel and Fill a Range with an Array

    Q186122 HOWTO: Use MFC to Automate Excel & Obtain an Array from a Range

    Q179706 HOWTO: Use MFC to Automate Excel & Create/Format a New Workbook
    Also, it is important to point out that using the clipboard can sometimes improve performance. For instance, you can copy your data to the clipboard, then use automation to tell the server to paste. Or vice- versa; tell the server to copy-to-clipboard, and paste into your application.


  9. What do these huge error values, such as -2147352573, or 0x80030002 mean?

    These values are known as HRESULTs and are defined in winerror.h. The numbers are so large because the first bit represents whether or not it is an error result. You can use the ErrLook.Exe utility that comes with Visual C++ to translate these numbers into meaningful descriptions.

    If you want to programmatically obtain a description for the errors, you can use the FormatMessage() API. See the following Microsoft Knowledge Base articles for more information and examples on the use of FormatMessage():
    Q186063 INFO: Translating Automation Errors for VB/VBA

    Q122957 SAMPLE: Decode32 and Decode16 OLE Error Code Decoder Tools

    Q94999 FormatMessage() Converts GetLastError() Codes
    NOTE: If you are using Visual C++ 6.0 and have a variable containing this value in the debug watch window, append ", hr" (without the quotes) to it to have Visual C++ translate it for you!


  10. What is a type library?

    A type library is similar to a C/C++ header file. It contains the interfaces, methods, and properties that a server is publishing. You can view the type library with the OLE/COM Object Viewer (Oleview.exe) that comes with Visual C++. Here is a list of the type library filenames for Microsoft Office 95, 97, and 2000:
    
    
           Office Application      | Type library
           ------------------------+----------------
           Word 95 and prior       | wb70en32.tlb
           Excel 95 and prior      | xl5en32.olb
           Powerpoint 95 and prior | Powerpoint.tlb
           Access 95 and prior     | msaccess.tlb
           Binder 95               | binder.tlb
           Schedule+               | sp7en32.olb
           Project                 | pj4en32.olb
           Team Manager            | mstmgr1.olb
           Word 97                 | msword8.olb
           Excel 97                | excel8.olb
           Powerpoint 97           | msppt8.olb
           Access 97               | msacc8.olb
           Binder 97               | msbdr8.olb
           Graph 97                | graph8.olb
           Outlook 97              | msoutl8.olb
           Outlook 98              | msoutl85.olb
           Word 2000               | msword9.olb
           Excel 2000              | excel9.olb
           Powerpoint 2000         | msppt9.olb
           Access 2000             | msacc9.olb
           Outlook 2000            | msoutl9.olb 


  1. My automation code worked with Excel 95, but fails with Excel 97. What's happening?

    The object model for Excel made a significant change from version 95 to 97. Excel 95 implemented all its methods and properties in a single implementation of IDispatch. This meant that often you could call methods meant for object X, from object Y. This was not a good design, so in Office 97, each object has its own separate Idispatch implementation. This means that if you ask for a method or property from object X from a separate object Y, you get the error 0x80020003, -2147352573, "Member not found." To avoid this error, you need to make sure that the underlying IDispatch interface you are making calls from is the semantically correct one. See the following Microsoft Knowledge Base articles for more information:
    Q172108 HOWTO: Troubleshooting "Member not found", 0x80020003 Error


  2. The application I'm automating stays in memory after my program is finished. What's happening?

    Most likely, this is because you have forgotten to release an acquired interface and you'll need to track it down. Here are some general suggestions, and things to looks for:



  3. I know what I want to do as a Office application user, but how do I do this programmatically through Automation?

    What you are interested in is what objects, methods, and properties you need to use. The best way to learn how to navigate the object models of Word, Excel, and Powerpoint, based on what you want to do as a user, is to use the Macro Recorder. Just choose Macro\'Record New Macro' from the Tools menu, execute the task you're interested in, then choose Macro\'Stop Recording.' Once you're done recording, choose Macro\Macros from the Tools menu, select the macro you recorded, then click Edit. This will take you to the generated VBA code that will accomplish the task you recorded. Keep in mind the recorded macro won't be the best possible code in most cases, but it's does very well for a quick example.


  4. Can I automate an embedded Office application?

    Absolutely. The trick is getting the IDispatch pointer: this is given in the Visual C++ Technical Note 39 (TN039). See the following Microsoft Knowledge Base article for a step-by-step example:
    Q184663 HOWTO: Embed and Automate a Microsoft Excel Worksheet With MFC


  5. How do I access my document properties in an Office document?

    The document properties are accessible through Automation, or directly through IPropertyStorage. The following Microsoft Knowledge Base articles demonstrate each method:
    Q179494 HOWTO: Use Automation to Retrieve Built-In Document Properties

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


Additional query words: coledispatchdriver colesafearray colevariant _com_ptr_t _com_variant_t IPropertySetStorage


Keywords          : kbole kbAccess kbAutomation kbCOMt kbExcel kbVC420 kbVC500 kbVC600 kbWord kbOffice2000 
Version           : WINDOWS:7.0,97; winnt:4.0,5.0,6.0; :
Platform          : WINDOWS winnt 
Issue type        : 

Last Reviewed: June 17, 1999