body { margin:0px; padding:0px; background:#f6f6f6; color:#000000; font-size: small; } #outer-wrapper { font:normal normal 100% 'Trebuchet MS',Trebuchet,Verdana,Sans-Serif; } a { color:#DE7008; } a:hover { color:#9E5205; } a img { border-width: 0; } #content-wrapper { padding-top: 0; padding-right: 1em; padding-bottom: 0; padding-left: 1em; } @media all { div#main { float:right; width:66%; padding-top:30px; padding-right:0; padding-bottom:10px; padding-left:1em; border-left:dotted 1px #e0ad12; word-wrap: break-word; /* fix for long text breaking sidebar float in IE */ overflow: hidden; /* fix for long non-text content breaking IE sidebar float */ } div#sidebar { margin-top:20px; margin-right:0px; margin-bottom:0px; margin-left:0; padding:0px; text-align:left; float: left; width: 31%; word-wrap: break-word; /* fix for long text breaking sidebar float in IE */ overflow: hidden; /* fix for long non-text content breaking IE sidebar float */ } } @media handheld { div#main { float:none; width:90%; } div#sidebar { padding-top:30px; padding-right:7%; padding-bottom:10px; padding-left:3%; } } #header { padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; border-bottom:dotted 1px #e0ad12; background:#F5E39e; } h1 a:link { text-decoration:none; color:#F5DEB3 } h1 a:visited { text-decoration:none; color:#F5DEB3 } h1,h2,h3 { margin: 0; } h1 { padding-top:25px; padding-right:0px; padding-bottom:10px; padding-left:5%; color:#F5DEB3; background:#DE7008; font:normal bold 300% Verdana,Sans-Serif; letter-spacing:-2px; } h3.post-title { color:#9E5205; font:normal bold 160% Verdana,Sans-Serif; letter-spacing:-1px; } h3.post-title a, h3.post-title a:visited { color: #9E5205; } h2.date-header { margin-top:10px; margin-right:0px; margin-bottom:0px; margin-left:0px; color:#777777; font: normal bold 105% 'Trebuchet MS',Trebuchet,Verdana,Sans-serif; } h4 { color:#aa0033; } #sidebar h2 { color:#B8A80D; margin:0px; padding:0px; font:normal bold 150% Verdana,Sans-serif; } #sidebar .widget { margin-top:0px; margin-right:0px; margin-bottom:33px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; font-size:95%; } #sidebar ul { list-style-type:none; padding-left: 0; margin-top: 0; } #sidebar li { margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; list-style-type:none; font-size:95%; } .description { padding:0px; margin-top:7px; margin-right:12%; margin-bottom:7px; margin-left:5%; color:#9E5205; background:transparent; font:bold 100% Verdana,Sans-Serif; } .post { margin-top:0px; margin-right:0px; margin-bottom:30px; margin-left:0px; } .post strong { color:#000000; font-weight:bold; } pre,code { color:#999999; } strike { color:#999999; } .post-footer { padding:0px; margin:0px; color:#444444; font-size:80%; } .post-footer a { border:none; color:#968a0a; text-decoration:none; } .post-footer a:hover { text-decoration:underline; } #comments { padding:0px; font-size:110%; font-weight:bold; } .comment-author { margin-top: 10px; } .comment-body { font-size:100%; font-weight:normal; color:black; } .comment-footer { padding-bottom:20px; color:#444444; font-size:80%; font-weight:normal; display:inline; margin-right:10px } .deleted-comment { font-style:italic; color:gray; } .comment-link { margin-left:.6em; } .profile-textblock { clear: both; margin-left: 0; } .profile-img { float: left; margin-top: 0; margin-right: 5px; margin-bottom: 5px; margin-left: 0; border: 2px solid #DE7008; } #sidebar a:link { color:#999999; text-decoration:none; } #sidebar a:active { color:#ff0000; text-decoration:none; } #sidebar a:visited { color:sidebarlinkcolor; text-decoration:none; } #sidebar a:hover { color:#B8A80D; text-decoration:none; } .feed-links { clear: both; line-height: 2.5em; } #blog-pager-newer-link { float: left; } #blog-pager-older-link { float: right; } #blog-pager { text-align: center; } .clear { clear: both; } .widget-content { margin-top: 0.5em; } /** Tweaks for layout editor preview */ body#layout #outer-wrapper { margin-top: 0; } body#layout #main, body#layout #sidebar { margin-top: 10px; padding-top: 0; } -->

Monday, March 3, 2008

Office Automation Using Visual C++

Office Automation Using Visual C++
View products that this article applies to.
function loadTOCNode(){}
Article ID : 196776
Last Review : January 23, 2007
Revision : 6.2
This article was previously published under Q196776
On This Page
SUMMARY
MORE INFORMATION
Table of Contents
Questions and Answers

SUMMARY
loadTOCNode(1, 'summary');
This article answers common questions concerning Automation to Microsoft Office from Visual C++.
Back to the top
MORE INFORMATION
loadTOCNode(1, 'moreinformation');
Table of Contents
loadTOCNode(2, 'moreinformation');
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?
Back to the top
Questions and Answers
loadTOCNode(2, 'moreinformation');
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 (http://support.microsoft.com/kb/152023/EN-US/) Locating Resources to Study OLE Automation If you prefer learning by example, please see the following article in the Microsoft Knowledge Base:
179706 (http://support.microsoft.com/kb/179706/EN-US/) 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 (http://support.microsoft.com/kb/181473/EN-US/) 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 (http://support.microsoft.com/kb/190985/EN-US/) 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 (http://support.microsoft.com/kb/183599/EN-US/) 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 (http://support.microsoft.com/kb/186120/EN-US/) HOWTO: Use MFC to Automate Excel and Fill a Range with an Array
186122 (http://support.microsoft.com/kb/186122/EN-US/) HOWTO: Use MFC to Automate Excel & Obtain an Array from a Range
179706 (http://support.microsoft.com/kb/179706/EN-US/) 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 (http://support.microsoft.com/kb/186063/EN-US/) INFO: Translating Automation Errors for VB/VBA
122957 (http://support.microsoft.com/kb/122957/EN-US/) SAMPLE: Decode32 and Decode16 OLE Error Code Decoder ToolsNOTE: 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 (http://support.microsoft.com/kb/172108/EN-US/) 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 (http://support.microsoft.com/kb/184663/EN-US/) 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 (http://support.microsoft.com/kb/179494/EN-US/) HOWTO: Use Automation to Retrieve Built-In Document Properties
186898 (http://support.microsoft.com/kb/186898/EN-US/) HOWTO: Read Compound Document Properties Directly with VC++

Origin Articles : http://support.microsoft.com/kb/196776

No comments: