Office Automation Using Visual C++ (196776)

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:

   152023 Locating Resources to Study OLE Automation

   If you prefer learning by example, please see the following article in the Microsoft Knowledge Base:

   179706 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++:
   
   
   • With MFC, use the Visual C++ ClassWizard to generate "wrapper classes" from the Microsoft Office type libraries. These classes, as well as other MFC classes, such as COleVariant, COleSafeArray, COleException, simplify the tasks of Automation. This method is usually recommended over the others, and most of the Microsoft Knowledge Base examples use MFC.
   • #import, a new directive that became available with Visual C++ 5.0, creates VC++ "smart pointers" from a specified type library. It is very powerful, but often not recommended because of reference- counting problems that typically occur when used with the Microsoft Office applications.
   • C/C++ Automation is much more difficult, but sometimes necessary to avoid overhead with MFC, or problems with #import. Basically, you work with such APIs as CoCreateInstance(), and COM interfaces such as IDispatch and IUnknown.
   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:

   181473 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:

   190985 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 article gives you step-by-step examples for Microsoft Word:

   183599 HOWTO: Catch Microsoft Word97 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:

   186120 HOWTO: Use MFC to Automate Excel and Fill a Range with an Array

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

   179706 HOWTO: Use MFC to Automate Excel and 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():

   186063 INFO: Translating Automation Errors for VB/VBA

   122957 SAMPLE: Decode32 and Decode16 OLE Error Code Decoder Tools

   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
           Word 2002               | msword.olb
           Excel 2002              | excel.exe
           Powerpoint 2002         | msppt.olb
           Access 2002             | msacc.olb
           Outlook 2002            | msoutl.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:

   172108 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:
   
   
   • If you're using #import, it is very likely you could be running into one of the reference-counting bugs associated with it. Often times the bugs can be worked around, but usually it is preferred to use one of the other Automation methods. #import doesn't work very well with the Office applications, because its type libraries and use are fairly complex. Also, such reference counting problems are hard to track down because a lot of the interface-level COM calls are behind-the-scenes when using #import.
   • Check to see if you are calling any methods, such as Open, or New, that return an IDispatch * (LPDISPATCH), and ignoring the return value. If you are, then you are abandoning this returned interface and will need to change your code so that you release it when no longer needed.
   • Gradually comment out sections of your code until the problem disappears, then add it back judiciously to track down where the problem starts.
   • Note that some applications will stay running if the user has "touched" the application. If this occurs while you are automating, then the application will probably stay running afterwards. The Office applications have a "UserControl" property on the Application object that you can read/write to change this behavior.
   • Also, some applications will decide to stay running if enough user-interface "action" has occurred. If you are intending the application to exit, then call its Quit() method on the Application object. Word will shutdown regardless of its reference count when Quit is called. This isn't expected COM behavior. Excel, however, will properly just hide itself but stay running until all outstanding interfaces are released. In general, you should release all outstanding references, and only call Quit() if you intend the application to quit.
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:

   184663 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:

   179494 HOWTO: Use Automation to Retrieve Built-In Document Properties

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