Export (0) Print
Expand All
Expand Minimize

Microsoft Office Development with Visual Studio

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.
 

Developer Support
Microsoft Corporation

Created: May 2001
Revised: September 2001

Applies to:
     Microsoft® Office 97
     Microsoft® Office 2000
     Microsoft® Office XP
     Microsoft® Visual Studio 97

Summary: This article provides a resource for Microsoft Visual Studio developers who want information, sample code, and answers to common questions about Microsoft Office development. (34 printed pages)

Contents

Overview
Office Automation
   Visual Basic - Frequently Asked Questions
   Visual Basic - Information and Sample Code
   Visual C++/MFC - Frequently Asked Questions
   Visual C++/MFC - Information and Sample Code
   Visual FoxPro - Information and Sample Code
   Visual J++ - Sample Code
   Troubleshooting Office Automation
Office Web Solutions
   Office on the Web Server - Frequently Asked Questions
   ASP - Server-Side Solutions
   Client-Side Solutions
   Office Web Components
   Office HTML/XML Documents
Office Add-ins and Components
   COM Add-ins
   Smart Tags
   Excel RealTimeData (RTD) Servers
   Excel Add-ins (XLLs)
   Word Add-ins (WLLs)
   ActiveX Controls
Office Document Format Information
   Office Binary File Formats
   Office Document Properties
Office OLE Objects and ActiveX Documents

Overview

This article provides a resource for Microsoft® Visual Studio® developers who want information, sample code, and answers to common questions about Microsoft® Office development. Much of the information presented is not language specific. Instead, it provides concepts that are applicable to Office development issues regardless of the programming language you choose for your solution.

This article also provides links to code samples that do target specific languages, including Microsoft® Visual Basic®, Visual C++® and MFC, Active Server Pages (ASP), Visual Basic Scripting (VBScript), Visual FoxPro®, and Visual J++®.

For additional information about Office development, visit the Microsoft Office Home Page and FAQs & Highlights for Office Development.

Please let us know if the information contained in this article helps you. We would also appreciate any other comments or suggestions. E-mail us your feedback! (Please note that this e-mail link is for feedback only.)

If you have a question or problem that requires assistance, visit Microsoft Support for information about support options.

Office Automation

Productivity is the biggest challenge for any company or small business. Increasing productivity is Microsoft Office's goal. In that spirit, Microsoft has sought to include features that allow corporate and small business developers to produce quality in-house applications that harness the power and versatility of Office in order to quickly build customized solutions. Automation is the key to this strategy.

Automation (formerly known as OLE Automation) is the programmatic manipulation of any program or component based on certain rules following the Component Object Model (COM). Automation was first pioneered and developed by Visual Basic and Office as a way to allow developers to extend and control the Office environment both internally and externally.

While it is not necessary to know all the details about how Automation works, you should familiarize yourself with some of the key terms and concepts. Namely, an Automation client is any piece of code that creates and calls an instance of a COM server that exposes an "object model." Object models are an arrangement of classes that expose functionality through various properties and methods and enable programmers to control a product. A particular instance of one of these classes is an object and the properties and methods comprise its interface. The object model for each Office application is different, and must be learned before Automation code can be written.

The following list contains references for Office Object Model documentation. For information about how you can use this documentation when developing an Automation client, see Find and Use Office Object Model Documentation (Q222101).

Microsoft Office 97 and Office 2000 Object Models

Microsoft Office 97 Object Model Guide

Microsoft Office 2000 Developer Object Model Guide

Microsoft Office XP Object Models

Microsoft Access 2002 Object Model Map

Microsoft Excel 2002 Object Model Map

Microsoft FrontPage 2002 Web Object Model Map

Microsoft Outlook 2002 Object Model Map

Microsoft PowerPoint 2002 Object Model Map

Microsoft Word 2002 Object Model Map

Many developers first become familiar with Office Automation by using the integrated Visual Basic for Applications (VBA) environment that is hosted by each Office application. VBA allows developers to add run-time capabilities to specific documents in order to automate common tasks (a true "macro") or to make the document more interactive by handling certain events and performing special processing (like field validation or auto-formatting). VBA provides a document-oriented approach to customizing any Office application. Many of the skills, lessons and approaches used in VBA are valuable parts to Automation from any client.

Yet, VBA is only one aspect of Office Automation. Many solutions require an application-level or system-level architecture that involves stepping outside of VBA and pulling one or more Office applications together using a custom program. Instead of automating a specific task or handling events in a document, the program can control the entire process, providing an organization and structure to Office document creation, editing, or management that suits your particular business need. To do this, you can use a Visual Studio language like Visual Basic or Visual C++, or a third-party utility capable of using COM. Regardless of the programming language or tool you choose, Automation from an external client can often give your solution more control and structure than VBA alone.

Automation of Office from an external client is not much different than using it from VBA. There are only two real differences:

  • The syntax of the programming language you use to make COM calls (which might differ depending on whether you use early binding or late binding)
  • The important but subtle difference in coding practice that occurs because VBA is in-process and can assume certain things (for example, state, selection, thread synchronization, speed) that an out-of-process client cannot

The first difference has little to do with Office directly and can quickly be learned from the information below. The second is a bit more important.

For developers new to Office Automation, or developers who don't know what object to use for a certain task, one of the best ways to find out how to do something is to use the built-in VBA macro recorder to record the process manually, and see what code it generates to do it automatically. The code it generates will run in VBA (and often in VB, too), but to be production quality for an out-of-process client, the code needs to be modified to suit cross-context calls. This translation is needed to overcome the assumptions that VBA code can safely make, but out-of-process clients cannot.

There are at least four important items that need to be taken into account when doing a translation:

  • Implicit Instances/Unqualified References. Because VBA code runs in-process, in a specific document, it can safely assume the code is always running in a particular instance of the Office application, and working on a particular document. As such, VBA code is highly "unqualified" when it comes to accessing certain properties or methods. For example, using the "Cells" method in Excel to get a range assumes you mean the instance of Excel the VBA code is running in and the active worksheet that has focus, both of which are assumptions that are bad to make if you are not in-process and do not control the active sheet.
  • Assuming State. VBA code also assumes a certain state or focus that may not always be valid when an Office application is shared with a user or another Automation or OLE client. For example, the Word "Selection" object assumes an active selection in the visible window. If two applications talk to the same instance of Word (or if a user is allowed to interact with Word at the same time) this assumption can no longer be made. VBA can make it because it runs in-process on the same thread as Word's UI, and cannot be interrupted while the macro runs unless the macro explicitly yields execution (i.e., DoEvents). However, out-of-process clients run on separate threads, and in between each method call Word's thread is free to handle requests from any other internal or external clients. If one of these clients changes the process state, any call you make that assumes that state may fail.
  • Assumed User Identity. VBA runs under the context of the user who started the Office application, and the code can rightly be assumed to run under their security permissions and user rights. Out-of-process clients can (and may) run under different contexts (or even WinStations) than the Automation server they are calling, and therefore cannot make such assumptions about security or user identity. For most clients using Automation to Office under default conditions, this is not much of an issue, but for clients running as batch jobs or from a service, this is very important.
  • Optimizing the Code for Cross-Context. Code generated by the macro recorder, and some VBA code in general, is highly un-optimized but yields sufficient speed because it runs in-process. When working out-of-process, remember to cache sub-objects you use often in a routine rather than re-acquiring them each time, and avoid named argument syntax whenever possible. Both of these require more work at run-time and cause code to run much slower when out-of-process because of the overhead of marshalling and cross context task switches.

For more information about these issues and how to work around them, read the Automation whitepaper below for a detailed step-by-step accounting of Office Automation with sample source code.

Whitepaper: Automating Office 97 and Office 2000

Offautmn.exe is a self-extracting executable that contains a white paper supplemented with sample projects that provide the fundamentals for understanding how to Automate Microsoft Office 97 and Microsoft Office 2000 applications. The white paper is designed to guide you from the ground up to create a variety of fully functional Automation clients. It is structured as a tutorial with a large assortment of sample code and provides tips throughout that facilitate development of your Automation clients. Exercises and sample code are presented for Microsoft Visual Basic, Microsoft C/C++, and Microsoft Foundation Classes (MFC) developers. For information on downloading Offautmn.exe, visit:

OFFAUTMN.EXE Discusses Office 97 and 2000 Automation and Provides Sample Code (Q253235)

Additional Resources

In addition to the whitepaper described above, there are a number of valuable resources for any Office Developer doing Automation. While some of the documentation was written explicitly for the VBA developer, the methods and examples used will often apply to external clients as well.

Automation Programmer's Reference

Microsoft Office 2000 Automation Help File Available (Q260410)

Microsoft Office XP Automation Help File Available (Q302460)

The remainder of our Automation discussion includes frequently asked questions, code samples, and resources that will help you with specific Visual Studio languages or Office applications. Because Visual Basic is so closely related to VBA, it is natural that there be more code samples for Visual Basic than for the other Visual Studio languages. If you are using a language other than Visual Basic and cannot find the information you need in that language, we encourage you to consult the Visual Basic information and samples. Many of the same principles and concepts will still apply.

Visual Basic—Frequently Asked Questions

What are the differences between early binding and late binding?

Binding refers to how your application connects to an Automation server. There are two common methods: early binding and late binding. The method you should use depends on your application design and objectives. For additional information, see INFO: Using Early Binding and Late Binding in Automation (Q245115).

Should I package and distribute the Office application's object library (.olb) file with my application?

You should not distribute the object library of the Office application you are automating. Redistribution of the object library is not necessary since it is automatically installed with the Office application on the client machine. For more information, see PRB: Visual Basic Package & Deployment Wizard - Includes Office OLB Files in Setup List (Q249843).

I get an Automation error while running my code. How can I find out what the error number means?

Automation errors can be difficult to diagnose. They are often raised with no meaningful text description. To find a matching description for common errors, check INFO: Translating Automation Errors for VB/VBA (Q186063). If the error is returned while automating Microsoft Word, you can also use FILE: WRD97ERR.DOC Contains a List of Word 97 Automation Errors (Q244491).

When I try to create a new instance of an Office application from Visual Basic, I get a run-time error 429. Why?

An error 429 means that an Automation server could not be started or your client could not create a connection. There are several factors that may cause this error. To resolve it, look at the suggestions presented in INFO: Troubleshooting Error 429 When Automating Office Applications (Q244264). If the problem only occurs on a computer running Windows NT, you should also check BUG: COM/OLE Server Fails to Start on Windows NT 4.0 (Q185126).

I have code that automates Office. It works the first time the code is run, but fails on subsequent calls. Why?

When you are automating Office and have a reference set to the type library for the Office application, you may unintentionally be using something called an "unqualified reference" in your code. This occurs when you call a method or property in the type library without qualifying the function with an explicit object. For more information, see PRB: Automation Error Calling Unqualified Method or Property (Q189618) and PRB: Excel Automation Fails Second Time Code Runs (Q178510).

I want to insert an Office document directly into my Visual Basic form. How can I do this?

Microsoft Excel, Microsoft PowerPoint® and Microsoft Word support both Object Linking and Embedding (OLE) and ActiveX Document containment. If you want to "host" an Office document in Visual Basic, you can use the OLE Container control and OLE as described in HOWTO: Embed and Automate Office Documents with Visual Basic (Q242243). You can also use the WebBrowser control included with Microsoft Internet Explorer to provide basic ActiveX Document containment. For an example, see HOWTO: Use the WebBrowser Control to Open an Office Document (Q243058).

My Automation client for an Office application runs without error except when I run my client from a service. Why?

Microsoft Office is designed for use on client systems as end-user products and has not been optimized to run from Windows NT or Windows 2000 Services. Automating an Office application from a service is neither recommended nor supported. For details on problems that you might encounter automating an Office application from a service and common causes of those problems, please see INFO: Considerations for Server-Side Automation of Office (Q257757).

How can I determine the window handle for the Office application I am automating?

The object models for most Microsoft Office applications do not expose properties for retrieving the application window handles. To determine the window handle of an Office application that you are automating, use the FindWindow API function with the class name for the application's top-most window. If the application can have multiple instances running simultaneously, you may need to code for this scenario so that you retrieve the correct window handle. The following article describes techniques for retrieving the window handle for both single and multiple instance applications: HOWTO: Obtain the Window Handle for an Office Automation Server (Q258511).

Visual Basic—Information and Sample Code

This section contains information and sample code for Automation of Office applications using Visual Basic. Throughout this section, there are links to a wide assortment of sample code that illustrate commonly automated Office tasks, such as Word mail merges or transferring data to Excel worksheets. Most articles provide step-by-step instructions for creating the samples and little, if any, modification is needed to get the sample up and running.

This information is organized into several categories:

  • Visual Basic concepts for the Automation developer
  • Concepts that are common to all Office Automation servers
  • Information and code for each Office application

Visual Basic Concepts

Using Early Binding and Late Binding in Automation (Q245115)

Variable Not Defined Error Message When Using a Constant (Q179027)

Description of VB 6.0 Run Time and OLE Automation Files (Q190130)

Handle OLE Automation Server Timeout and Synchronization (Q138066)

Concepts That Apply to All Office Automation Servers

GetObject and CreateObject Behavior of Office Automation Servers (Q288902)

Built-in Constants in Visual Basic for Applications (Q112671)

Obtain Built-In Constant Values for an Office Application (Q239930)

How to Run Macros in Other Office Programs (Q177760)

Determine the Path for an Office Application (Q240794)

Dismiss a Dialog Box Displayed by an Office Application with Visual Basic (Q259971)

Microsoft Excel Automation

Automate Excel from Visual Basic (Q219151)

Methods for Transferring Data to Excel from Visual Basic (Q247412)

EXCELlent Office Adventures (MSDN Online Article)

EXCELlent Office Adventures Part 2 (MSDN Online Article)

ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks (Q278973)

Use ADO and ADOX with Excel Data from Visual Basic or VBA (Q257819)

Transfer Data from an ADO Recordset to Excel with Automation (Q246335)

Create Excel Chart w/OLE Automation from Visual Basic (Q142387)

Convert (row,col) Indices into Excel-Style A1:C1 Strings (Q198144)

Call Microsoft Excel Macros That Take Parameters (Q153307)

Install an Excel Add-in (XLA or XLL) with Automation (Q280290)

Dynamically Add and Run a VBA Macro from Visual Basic (Q219905)

Create an Excel Histogram by Using Automation and Analysis ToolPak (Q270844)

Microsoft Word Automation

How to Use (OLE) Automation with Word (Q184974)

Word in the Office (MSDN Online Article)

One More Word (MSDN Online Article)

Automate Word 2002 with Visual Basic to Create a Mail Merge (Q285332)

Word 2002 MailMerge Event Code Demonstration (Q285333)

Automate Microsoft Word to Perform Mail Merge from Visual Basic (Q220607)

Automate Word to Perform a Client-Side Mail Merge Using XML From SQL Server (Q285176)

Determine the Number of Merged Records Before Executing a Mail Merge (Q258523)

Automate Word from Visual Basic to Create a Mail Merge for Mailing Labels (Q258512)

Prompted To Select a Data Source Appears While Automating Word 2002 Mail Merge (Q279462)

Prompt to Select Table with Word 2002 Mail Merge Code for Excel or Access Data Source (Q289830)

Prompt to Save Normal.dot When Using Word as an Automation Server (Q285885)

Transfer an ADO Recordset to a Word Table with Automation (Q261999)

Use a VB ActiveX Component For Word Automation From Internet Explorer (Q286023)

Automate Word From Visual Basic or Visual Basic for Applications For Spell Checking (Q243844)

Find the End of a Page in a Word Document By Using Automation (Q285599)

Run a Word 97 Macro That Requires Arguments (Q172483)

Automate Word to Set and Retrieve Section Header/Footer Information (Q269565)

Set Duplex Printing for Word Automation (Q230743)

ActivePrinter Property in Word Sets System Default Printer (Q216026)

Paste RichText Formatted String into Word with Visual Basic Automation (Q258513)

How to Find the Word Startup-Path Using an External Solution (Q210860)

General Questions and Answers About the Location of Word 2000 Templates (Q210884)

Limited Programmatic Access to Word 2002 Discontiguous Selections (Q288424)

WRD97ERR.DOC Contains a List of Word 97 Automation Errors (Q244491)

Microsoft PowerPoint Automation

Automate PowerPoint Using Visual Basic (Q222929)

Automation of PowerPoint 97 and PowerPoint 2000 Viewers (Q265385)

Microsoft Access Automation

Using Microsoft Access as an Automation Server (Q147816)

How to Use Automation to Print Microsoft Access Reports (Q296586)

ImportText.exe Importing Text into Access with ADO/RDO/DAO/Filesys/Automation (Q230265)

Automate a Secured Access Database Using Visual Basic (Q192919)

How to Open a Password-Protected Database Through Automation (Q235422)

Access 2000 Object Model Breaks Binary Compatibility (Q246237)

Error 2046 Calling OpenForm or OpenReport Automating Access (Q244695)

Microsoft Access Is Visible When Started Through Automation (Q167659)

Microsoft Outlook Automation

Questions About Custom Forms and Outlook Solutions (Q146636)

Automate Outlook using Visual Basic (Q220595)

Use OLE Automation with Microsoft Outlook 97 (Q170262)

How to Automate Outlook from Another Program (Q168095)

Get the Currently Selected Item in an Outlook Folder from Visual Basic (Q240935)

Automation to Shared Office Components and Other Office Applications

Automate An Embedded MSGraph Object with Visual Basic (Q244589)

Manipulating Command Bars and Command Bar Controls with VBA Code (Office Developer Documentation)

Create a Transparent Picture For Office CommandBar Buttons (Q288771)

Manipulate Office Assistants from Visual Basic (Q173690)

Automate Frontpage to Create a New Web and Set a Navigation Structure (Q262987)

Automate MapPoint 2000 from Visual Basic (Q236615)

Set a Pushpin in MapPoint 2001 Using Automation (Q277749)

Use the MapPoint 2002 Control and Automation With Visual Basic to Save a Map as HTML (Q302885)

Cannot Automate Word, Excel, or PowerPoint Viewers (Q189609)

Automation of PowerPoint 97 and PowerPoint 2000 Viewers (Q265385)

Visual C++/MFC—Frequently Asked Questions

How do I automate a Microsoft Office application from Visual C++?

There are several ways you can control Office applications through automation using Visual C++:

  • Use MFC and the Visual C++ ClassWizard to generate "wrapper classes" from the Microsoft Office type libraries.
  • Use "smart pointers" created with the #import directive from an Office type library.
  • Use C/C++ to make direct calls to COM services without the overhead of MFC or #import.

For more details, see INFO: Using Visual C++ to Automate Office (Q238972).

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

To automate an Office application that is already running, you can use the GetActiveObject() API function to obtain the IDispatch pointer. Once you have the IDispatch pointer for the running instance, you can then call its methods and properties. For additional information, see HOWTO: Attach to a Running Instance of an Office Application (Q238975).

How do I pass optional parameters to methods and properties?

When you call a method that has optional parameters from Visual C++, you cannot omit the optional parameters. Instead, if the parameter type is a VARIANT, you can pass a special variant for arguments you intend to omit. This VARIANT has the type VT_ERROR and a code member of DISP_E_PARAMNOTFOUND. For more details, see HOWTO: Passing Optional Parameters When Calling a Function (Q238981).

How do I pass a COleDispatchDriver as an argument for a method expecting a VARIANT?

Some methods require that you pass a VARIANT that represents an Automation object. With MFC, these objects are typically handled by COleDispatchDriver-derived classes. To pass one of these to a method expecting a VARIANT, you can create a new VARIANT with its vt member set to VT_DISPATCH and its pdispVal member set to the COleDispatchDriver class' m_lpDispatch. For more details and a sample, please see HOWTO: Pass a COleDispatchDriver as an Argument for a Method Expecting a VARIANT (Q253501).

How do I catch events?

Automation objects that can raise events implement a connection point interface. Automation controllers can create "sinks" to "connect" with an Automation object's connection point so that it receives event notifications. Note that MFC's default implementation of IDispatch::Invoke does not support named arguments. Therefore, with some Automation servers—Microsoft Excel, for example—you must provide your own implementation of IDispatch in the sink. For examples, see HOWTO: Catch Microsoft Word97 Application Events Using VC++ (Q183599) and HOWTO: Catch Microsoft Excel Application Events Using VC++ (Q186427).

How do I improve the performance of my automation code?

You can improve the performance of your Automation code in Visual C++ by using a two-dimensional variant SAFEARRAY to read and write data all at once, and then using the clipboard to copy and paste data. For additional information, see HOWTO: Improving the Performance of Office Automation Code (Q238984).

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

While automating an Office application, you might receive a large error value, such as -2147221494. Troubleshooting the error is greatly facilitated by determining the exact description of the error. To obtain a description of the error, you can use the Error Lookup utility provided with Visual C++, call the FormatMessage() API at run-time, or use the watch window to display the error message. These approaches are described in INFO: Translating Large Office Automation Error Values (Q238986).

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

If the Office application you are automating continues to reside in memory after your Visual C++ program finishes executing, the most likely cause is that you have not released an acquired interface. For more information, see PRB: Office Application Remains in Memory After Program Finishes (Q238987)

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

You can automate Microsoft Word, Excel or PowerPoint with Visual C++ to retrieve and manipulate both built-in and custom document properties. For more information and an example, see HOWTO: Use Visual C++ to Access DocumentProperties with Automation (Q238393).

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 HOWTO: Read Compound Document Properties Directly with VC++ (Q186898) and Dsofile.exe Lets You Edit Office Document Properties from Visual Basic and Active Server Pages (Q224351).

How can I automate an embedded Microsoft Office document?

To automate an embedded Office document, you must first obtain the IDispatch pointer for the document object. Once you've obtained the IDispatch pointer to the document object, you can automate the server by calling the appropriate methods and properties for that server. For some examples, see HOWTO: Embed and Automate a Microsoft Excel Worksheet with MFC (Q184663) and HOWTO: Embed and Automate a Word Document with MFC (Q238611).

My Automation client for an Office application runs without error except when I run my client from an NT service.

Microsoft Office is designed for use on client systems as end-user products and has not been optimized to run from Windows NT Services. Automating an Office application from a service is not recommended or supported. For details on problems that you might encounter automating an Office application from a service and common causes of those problems, please see INFO: Considerations for Server-Side Automation of Office (Q257757).

Visual C++/MFC—Information and Sample Code

This section contains information and links to code samples for Office Automation using Visual C++ and MFC. Any one of the articles listed below may illustrate Automation with a specific version of Microsoft Office. In most cases, you can follow the step-by-step instructions in the article and run the sample without needing to modify the code. However, if you are using one of the code samples created with a version of Office earlier than the one you are automating, you may need to modify the code to account for new arguments of updated methods. For more information, see the Knowledge Base article Type Libraries for Office May Change with New Release (Q224925).

General Concepts

Create Automation Project Using MFC and a Type Library (Q178749)

Locating Resources to Study Automation (Q152023)

Find the Path and Version of an Office Application from Visual C++ (Q247985)

Use OLE Automation from a C Application Rather Than C++ (Q181473)

Create an Automation Client Project Using ATL (Q196135)

Do 16-Bit Automation in C++ Using VC 1.52 (Q194656)

OLE/COM Concepts

Descriptions and Workings of OLE Threading Models (Q150777)

OLE Threads Must Dispatch Messages (Q136885)

MARSHAL.EXE: How To Marshal Interfaces Across Apartments (Q206076)

COM Security Frequently Asked Questions (Q158508)

TN039: MFC/OLE Automation Implementation (MFC Technical Note)

Microsoft Excel Automation

Use MFC to Automate Excel and Create/Format a New Workbook (Q179706)

Embed and Automate a Microsoft Excel Worksheet with MFC (Q184663)

Use MFC to Automate Excel and Fill a Range with an Array (Q186120)

Use MFC to Automate Excel and Obtain an Array from a Range (Q186122)

Automate Excel Using MFC and Worksheet Functions (Q178781)

Use MFC to Copy a DAO Recordset to Excel With Automation (Q243394)

Use MFC to Create a Microsoft Excel Chart (Q178783)

Catch Microsoft Excel Application Events Using VC++ (Q186427)

Automate Excel and Then Know the User Closed It (Q192348)

Use MFC to Automate Excel and Navigate Worksheets (Q178782)

Automate Using VC++ to Save Excel Worksheet as HTML File (Q199691)

Convert Indices (row,col) into Excel-Style A1:C1 Strings (Q198112)

Automate Excel From C++ Without Using MFC or #import (Q216686)

COMEXCEL: Demonstrates an Automation Client Application with Compiler COM Support

Microsoft Word Automation

Automate Microsoft Word to Perform a Mail Merge Using Visual C++ and MFC (Q220911)

Use MFC to Automate Word and Create a Mail Merge for Mailing Labels (Q278260)

Use Automation to Run a Word Macro with Arguments (Q183369)

Use Automation to Open and Print a Word Document (Q178784)

Catch Microsoft Word 97 Application Events Using VC++ (Q183599)

Automate Word to Find the Page Count in Document Sections (Q293861)

ActivePrinter Property in Word Sets System Default Printer (Q216026)

Add Custom Table to the Clipboard to be Pasted into Word (Q216676)

Embed and Automate a Word Document with MFC (Q238611)

Use WordBasic Functions in an MFC Automation Client for Word 97, Word 2000, or Word 2002 (Q252719)

How to Find the Word Startup-Path Using an External Solution (Q210860)

General Questions and Answers About the Location of Word 2000 Templates (Q210884)

Limited Programmatic Access to Word 2002 Discontiguous Selections (Q288424)

WRD97ERR.DOC Contains a List of Word 97 Automation Errors (Q244491)

Microsoft PowerPoint Automation

Automate PowerPoint Using Visual C++ w/MFC (Q222960)

Use MFC to Create and Show a PowerPoint Presentation (Q180616)

Creating an MFC Automation Controller for PowerPoint (Q169505)

Automate PowerPoint using MFC and Run a Macro (Q237554)

PowerPoint 2000 Event Demonstration Available for Download (Q254009)

Microsoft Outlook Automation

Automate Outlook Using Visual C++/MFC (Q220600)

Send a Message by Outlook Object Model with VC++ (Q199870)

Get the Currently Selected Item in an Outlook Folder from Visual C++ (Q241287)

Questions About Custom Forms and Outlook Solutions (Q146636)

Automation to Shared Office Components and Other Office Applications

Use Automation to Modify the Office Menu (Q180625)

Place A Custom Bitmap on an Office Commandbar Button (Q198522)

Manipulating Command Bars and Command Bar Controls with VBA Code (Office Developer Documentation)

Use Visual C++ to Access DocumentProperties with Automation (Q238393)

Use Automation to Retrieve Built-in Document Properties (Q179494)

Add and Run a VBA Macro Using Automation from MFC (Q194906)

Use MFC to Retrieve a List of Macro Names in an Office Document (Q274680)

Find and List Access VBA Procedures By Using MFC (Q266387)

Cannot Automate Word, Excel, or PowerPoint Viewers (Q189609)

Automation of PowerPoint 97 and PowerPoint 2000 Viewers (Q265385)

Visual FoxPro—Information and Sample Code

Visual FoxPro applications can use Office object models in a manner similar to applications written in C++, MFC or Visual Basic. The following articles demonstrate how to perform specific Office tasks using Automation from Visual FoxPro. If the list below does not provide a Visual FoxPro code sample you need, then you might wish to consult the Visual Basic and C++/MFC sections presented earlier in this article. Although the languages are syntactically different, many of the same principles and concepts still apply.

Convert VBA to FoxPro for OLE Automation (Q160064)

Automate PowerPoint using Visual FoxPro (Q230154)

Create Form Letter Programmatically in Word with OLE (Q155751)

Use OLE Automation with Microsoft Word (Q138205)

Change the Data Series in MSGraph Using the PlotBy Prop (Q186094)

Automate Mail Merge to Word97 SR-1 Using OLE and ODBC (Q181926)

Using OLE Automation to Display Microsoft Outlook from Visual FoxPro (Q173582)

Cut and Paste from General Field into a Word Document (Q172847)

Pass Parameters to a Word 97 Macro (Q171988)

Print a Range of Pages in Word Using OLE Automation (Q176069)

Insert and Format a Picture in Word 97 w/ OLE Automation (Q198508)

Use OLE Automation to Add Data to Excel Sheet (Q142193)

OLE Examples for Taking Control of Excel from Visual FoxPro (Q252615)

Automate Word from Visual FoxPro and Add a Table to a Document (Q250501)

Prevent Switch To or Application Busy Message Using OLE Automation (Q240809)

FOR EACH Reference Fails To Release Automation Server (Q279777)

Automate Excel 2000 Subtotals Function in Visual FoxPro (Q265731)

Automatically Add Contacts to Outlook (Q269617)

Obtain Office Constants from .OLB Files with VFP (Q285396)

Create Categorized Table in Word 97 w/ OLE Automation (Q180901)

Visual J++—Sample Code

Visual J++ applications can use Office object models in a manner similar to applications written in C++, MFC or Visual Basic. The following articles demonstrate how to perform specific Office tasks using Automation from Visual J++. If the list below does not provide a Visual J++ sample you need, then you might wish to consult the Visual Basic and C++/MFC sections presented earlier in this article. Although the languages are syntactically different, many of the same principles and concepts will still apply.

Create/Format an Excel Workbook Using Visual J++ Automation (Q219430)

Automate MailMerge in Word 2000 Using Visual J++ (Java) (Q244219)

Automate PowerPoint Using Visual J++ (Q215484)

Troubleshooting Office Automation

As we discussed earlier, Office applications can be automated from any client that is written in a language that can call COM objects. An Automation call originates from an Automation client and then passes through the COM layer before getting executed on the Automation server. Troubleshooting a problem with Office Automation may require troubleshooting in any one or all of these three layers:

  • On the Automation client, this typically involves debugging client code using that specific language debugger. For information and tips on testing and debugging your applications, see Divide and Conquer.
  • In the COM layer, you should ensure that the Office Automation server in question is properly registered and that no registry entries are missing or corrupt.
  • For the Automation server, you should make certain that the calls you make are valid given the state of the server. A good test is to try VBA code similar to your Automation code in the application; if the equivalent VBA code sequence does not work when run in the application, then there is a high probability that it will not work from an Automation client either.

The articles listed below describe some of the common problems and known issues you might encounter with Office Automation. The first list of articles provides general information about troubleshooting errors or problems that might occur with one or more Office application. The remaining articles are categorized by the specific Office application with which the error or problem is known to occur.

General Troubleshooting

Troubleshooting Error 429 When Automating Office Applications (Q244264)

Translating Automation Errors for VB/VBA (Q186063)

Translating Large Office Automation Error Values (Q238986)

GetObject or GetActiveObject Cannot Find a Running Office Application (Q238610)

Cross-Process COM Automation Can Hang Client Application on Win95/98 (Q216400)

Considerations for Server-Side Automation of Office (Q257757)

Office Application Remains in Memory After Program Finishes (Q238987)

Office 97 Automation Client Fails After Re-compilation With Office 2000 or Office XP Type Library (Q242375)

Error 0x80020004 (-2147352572) When Setting a Property (Q175618)

COM/OLE Server Fails to Start on Windows NT 4.0 (Q185126)

Programmatic Access to Office XP VBA Project is Denied (Q282830)

Automating Word or Outlook Fails on Second Attempt (Q303037)

Troubleshoot "Member Not Found" 0x80020003 Error (Q172108)

Adding Property in ClassWizard Changes DISPIDS for Methods (Q179691)

Microsoft Excel

Excel Automation Fails Second Time Code Runs (Q178510)

Limitations of Passing Arrays to Excel Using Automation (Q177991)

Microsoft Excel Does Not Repaint Properly With Automation (Q187745)

Changes Made to Excel CommandBars Through Automation Are Not Saved (Q241652)

Microsoft Word

WRD97ERR.DOC Contains a List of Word 97 Automation Errors (Q244491)

Automation Error 8001010A when Automating Word 2000 (Q246018)

Automation Error Calling Unqualified Method or Property (Q189618)

Automation Client Receives Error or Crashes Calling Microsoft Word's Find Object (Q292744)

Corrupt Normal.dot Causes Errors When Automating Word (Q247028)

Error Message Using WordMail: "This method or property is not available" (Q237338)

Starting Word Manually Uses Same Instance as Automation (Q188546)

MoveDown Method Behaves Incorrectly When Word is Hidden (Q235876)

Word Fails to Save Custom Document Properties (Q195425)

Microsoft Access

Access 2000 Object Model Breaks Binary Compatibility (Q246237)

Office Web Solutions

Integrating Microsoft Office with the Web is quickly becoming a necessity. As illustrated in this section, there are a variety of approaches you can use to incorporate Office with the Web. You can have client-side solutions that use a scripting language like VBScript or JScript or server-side solutions that use Active Server Pages (ASP). And, in many situations, you might even use both. A common scenario is to generate data server-side and present the data client-side using Office as the display mechanism.

You can use client-side script to automate an Office application to generate documents on the fly for users to view and edit. While client-side Automation of Office is often a good solution, Automation of Office on the server is not recommended. Office applications are not lightweight Automation servers and, when used in a server-side solution, can present issues involving scalability and problems with permissions. For the server, this section presents some alternatives to Automation for document creation and manipulation. We recommend you consider all alternatives to Automation for your server solutions whenever possible.

The Office Web components included with Microsoft Office 2000 and Microsoft Office XP can provide an alternative to Automation and deliver a lightweight solution tailored for the Web. The Office Web Components enable you to incorporate many of Office's rich data analysis and reporting features in your Web solutions.

The following section provides information for integrating Office with your Web solutions. For some additional resources and references, see Web Solutions Resources.

Office on the Web Server—Frequently Asked Questions

When I automate an Office application from ASP, I get the error "Cannot launch out of process component."

By default, Internet Information Server (IIS) version 4.0 does not allow you to start out of process servers from Active Server Pages (ASP). To enable this, you must change the value of the AspAllowOutOfProcComponents metabase property to True. For more information, please see Cannot Launch Out of Process Component Under IIS 4 (Q184682).

When I automate Microsoft Word from ASP, I get an error that states "Could Not Open Macro Storage."

You are running Word under the context of a user that does not have its registry hive loaded. Word requires that a "user hive" be loaded for it to run correctly. For more information, please see PRB: Error 800A175D - Could Not Open Macro Storage (Q224338).

When I navigate to an Office document with Office 2000 I get an authentication dialog. I do not have this problem with Office 97. What has changed?

Office 97 applications open documents from a Web server as read-only. In contrast, Office 2000 applications attempt to open documents as read-write. Therefore, if the client has Office 2000 installed and does not have administrative privileges on the Web server, the client may receive the authentication dialog when trying to open an Office document from a Web server. For more information, please see OFF2000: User Prompted for Password When Opening Office Documents in Browser (Q225234).

I'm automating an Office application from ASP, but I get an error when I try to print.

Depending on which user context the Office application is running under, different information is loaded into that user's hive. By default, Office applications run under the SYSTEM account on IIS4 when instantiated from ASP. The SYSTEM account does not have any printers set up in the registry; therefore, when the Office application prints, you receive an error. For more information, please see PRB: COM Objects Fail to Print When Called From ASP (Q184291).

When I automate Office applications from IIS or MTS and an error occurs, I cannot shut down those applications. When I try to end the process in Task Manager, I receive an "Access Denied" error.

Windows NT security does not allow a user to shut down applications that have been started under the context of another user. The only way to shut those programs down is to use the KILL utility that is included with the Windows NT 4.0 Resource Kit. For more information, please see Cannot End Service Processes with Task Manager (Q155075) and PRB: Excel Automation Fails Second Time Code Runs (Q178510).

When I use Response.Redirect to redirect the client to a Word document or Excel workbook, the application loads but then displays a blank document or workbook.

Office 97 applications open documents from a Web server as read-only. In contrast, Office 2000 applications attempt to open documents as read-write. Therefore, if the client has Office 2000 installed and does not have administrative privileges on the Web server, the Office 2000 application opens and displays the Active Server Page instead of redirecting the client to the Office document as expected. For more information, please see Word 2000 and Excel 2000 Do Not Redirect Correctly When Using Response.Redirect (Q247318).

ASP—Server-Side Solutions

Developers often request information about ways of manipulating existing documents or creating new documents on the Web server. This section describes the options available to you as well as information about the pros and cons of various approaches that you might consider.

Office Automation on the Web Server

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from Internet Information Services (IIS), Microsoft Transaction Server, a DCOM server, or a Microsoft Windows NT service because the applications may exhibit unstable behavior and/or deadlock when run under an unattended, non-interactive account. For details, please see Considerations for Server-Side Automation of Office (Q257757).

Despite this recommendation, if you have no other option but to automate an Office application on the server, there are several configuration choices for the server that you can consider:

Configure Office Applications To Run Under the Interactive User Account (Q288366)

Configure Office Applications To Run Under a Specific User Account (Q288367)

Configure Office Application for Automation From a COM+/MTS Package (Q288368)

The following list contains known problems you may encounter when automating an Office application on a Web server:

COM Objects Fail to Print When Called From ASP (Q184291)

Error 800A175D - Could Not Open Macro Storage (Q224338)

Cannot Launch Out of Process Component Under IIS 4 (Q184682)

Access 2000 Quit Method Leaves Access Running (Q246953)

Working with Office Documents on the Web Server

As previously discussed, you should consider alternatives to Automation for creating or manipulating Office documents on the Web server when alternatives exist. Server solutions that do not involve Automation will be more scalable, more robust and less problematic. Different solutions can be used for the different types of Office documents; for example, while working with Excel workbooks, you could use ADO or XML/HTML or while working with Word documents you might take advantage of the benefits that the text-based RTF format has to offer.

The following list provides resources for information and sample code that you can use to create Office documents on the server without Automation.

Display ASP Results Using Excel in IE with MIME Types (Q199841)

Query and Update Excel Data Using ADO From ASP (Q195951)

Format Cell Data When You Are Creating an Excel File With an Active Server Pages Page (Q260239)

Format an Excel Workbook While Streaming MIME Content (Q271572)

ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks (Q278973)

Use ADO and ADOX with Excel Data from Visual Basic or VBA (Q257819)

Use Visual Basic or ASP to Create an XML Spreadsheet for Excel 2002 (Q285891)

Use ASP to Build Spreadsheet XML for Client-Side Display (Q288130)

Use ASP to Generate a Rich Text Format (RTF) Document to Stream to Microsoft Word (Q270906)

Automate Word to Perform a Client-Side Mail Merge Using XML from SQL Server (Q285176)

Troubleshooting

User Prompted for Password When Opening Office Documents in Browser (Q225234)

Word 2000 and Excel 2000 Display ASP Source When Using MIME Type to Stream Data (Q266263)

Word 2000 and Excel 2000 Do Not Redirect Correctly When Using Response.Redirect (Q247318)

ASP Session Variables Empty When Office 2000 MIME Types Are Streamed with Internet Explorer (Q264143)

Additional Resources

Read and Display Binary Data in ASP (Q193998)

Use the ADODB.Stream Object to Send Binary Files to the Browser through ASP (Q276488)

Security Ramifications for IIS Applications (Q158229)

COM Security Frequently Asked Questions (Q158508)

COM Servers Activation and NT Windows Stations (Q169321)

Client-Side Solutions

Client-side script embedded in the HTML of your Web pages can be used to automate Office applications from a client's browser. Given the interactive nature of Office applications, Automation of Office from client-side script is preferable to server-side scripting.

Office Automation servers, like Excel and Word, are not marked as safe for scripting. Therefore, you should ensure that clients running the script on your Web page have security settings that allow Automation of Office applications from client side scripts.

Note   For security purposes, browser security settings cannot be changed with client-side script. Browser security settings are customizable by the user and can vary between clients.

The following articles provide information and code samples for using client-side script to automate an Office application. If the list below does not provide a specific code sample you need, then you might wish to consult the Visual Basic and C++/MFC sections presented earlier in this article. Although the languages are syntactically different, many of the same principles and concepts will still apply.

VBScript

Automating Excel From Client-Side VBScript (Q198703)

Excel Automation from VBScript Gives Type Library Error (Q234675)

CreateObject in VBScript (Q183284)

Launch Word from Internet Explorer (Q178222)

Use a VB ActiveX Component For Word Automation From Internet Explorer (Q286023)

Cannot Call GetObject from Script in IE to Access Running Object (Q239470)

CreateObject Fails from Client-Side Scripts (Q195826)

JScript

Automate Excel from an HTML Web Page Using Jscript (Q234774)

Automation Object Not Being Released (Q218595)

Excel Does Not Shut Down After Calling the Quit Method When Automating from JScript (Q266088)

Office Web Components

Office 2000 introduced the Office Web Components (OWC), a collection of COM controls designed to bring interactive spreadsheet modeling, database reporting, and data visualization to a number of control containers. The OWC library contains four principal components: Spreadsheet, Chart, PivotTable, and Data Source. The components sport many of the most popular and commonly used features found in Excel and Access:

  • The Spreadsheet component provides a recalculation engine, a full function library, and a simple worksheet user interface.
  • The Chart component, as its name implies, enables you to chart data from a variety of sources.
  • The PivotTable component allows users to sort, group, filter, outline, and manipulate data stored in either traditional or multidimensional data sources.
  • The Data Source component provides the other components with a way to get data. Of the four Office Web components, the Data Source component is the only one that does not have a UI.

While the Office Web Components can be hosted in a variety of containers such as Visual Basic forms or VBA Userforms, they are designed to work best on HTML Web pages in the browser. The Office Web Components are fully programmable and can be used as in-memory objects. Using a component in-memory can be particularly useful when you are developing solutions that run server-side to generate Office XML or static representations of charts or pivot tables.

Office 2000 Web ComponentsResources and References

For an introduction to the Office 2000 Web components and references for the object model, the following resources are most helpful:

Programming Microsoft Office 2000 Web Components Comments and Corrections (Q240265)

Microsoft Office 2000 Web Components Basics (Technical Article)

Developing Solutions with the Office 2000 Web Components (WebCast)

Working with the Office Web Components (Programmer's Guide)

Microsoft Office 2000 Web Components Object Models

Office 2000 Web ComponentsScript Samples Download

OWebComp.exe is a self-extracting executable file that contains a series of scripting samples that demonstrate the features and usage of the Microsoft Office 2000 Web Components. The samples use VBScript and Active Server Pages (ASP) and demonstrate basic programmability for the Spreadsheet, PivotTable and Chart components.

OWebComp.exe Contains Scripting Samples for the Office 2000 Web Components (Q258187)

The remaining sections for the Office 2000 Web Components reference articles about licensing and deployment. Links are also provided to more sample code for the Chart, PivotTable and Spreadsheet components.

Office 2000 Web ComponentsLicensing and Deployment

Licensing the Office 2000 Web Components and Office Server Extensions (Q243006)

How to Deploy the Web Components Over an Intranet (Q231419)

Office Web Components Cannot Be Installed Over the Internet (Q231420)

Web Components May Not Function Properly in Netscape Navigator with the NCompass ScriptActive Plugin (Q231094)

Office Web Components Display Cross-Domain Warning (Q258510)

Use the Office DataSource Component with RDS to Avoid Cross-Domain Warnings (Q268836)

Office 2000 Web ComponentsThe Chart Component

Use the Chart Web Component with Visual Basic (Q235885)

Create a Combination Chart with the Chart Web Component (Q240263)

Use VBScript to Bind a Chart to a Spreadsheet Component (Q243192)

Use Server-Side Charting to Generate Charts Dynamically (Q244049)

CHARTGIF.EXE Demonstrates Streaming an Office Web Chart GIF to the Browser (Q264096)

Use XML Data with the Chart Component (Q249805)

Chart MDX Results with the Office 2000 Chart Component (Q279254)

Create Null (or Omitted) Data Points with the Office Chart Component (Q286317)

"Already Exists" Error Message Occurs When You Use AddNew Method of RecordsetDef with DataSource Control (Q256629)

Axis Minimum and Maximum Properties Return Incorrect Values in OnLoad Event (Q256627)

Office 2000 Web Chart Prints Incorrectly from Internet Explorer 5.0 (Q250556)

XML as the API

Office 2000 Web ComponentsThe Spreadsheet Component

Use the Spreadsheet Web Component with Visual Basic (Q235883)

Calculation Differences Between the Office Spreadsheet and Excel (Q216578)

Techniques for Loading and Retrieving Data with the Spreadsheet Web Component (Q253913)

Setting ReturnValue to False Does Not Cancel Spreadsheet Event (Q240902)

Using Worksheet Functions in Web Page Scripts (Technical Article)

Use Custom Functions with the Spreadsheet Component (Q248822)

HTMLData Property Does Not Return Spreadsheet Values (Q248169)

Inconsistent Results When Calling FreezePanes in OnLoad Event of Window (Q256625)

Long Delay When You Navigate Away from a Web Page That Contains Office Spreadsheet Component (Q276249)

Office 2000 Web ComponentsThe PivotTable Component

Use the PivotTable Office Web Component with VB (Q235542)

How to Test for a Successful PivotTable Connection (Q222556)

OWCHELPR.EXE Demonstrates Automation to Excel for Printing a PivotTable Component on a Web Page (Q262827)

Extract Cell Aggregate Values From the Office 2000 PivotTable Component (Q286210)

MaxHeight/MaxWidth Properties of PivotTable Should Be Less Than 32000 (Q271368)

Office XP Web Components

With respect to the Office Web Components, Office XP provides many enhancements to both the interactive features and programmability. This list names just a handful of new Office XP features:

  • All the components have customizable toolbars, menus and tooltips.
  • The Chart component sports an improved Chart Wizard and now enables you to have custom drawings, special fill effects and timescales on your charts.
  • The Spreadsheet component now supports data binding, multiple worksheets and named ranges.
  • The PivotTable component offers more functions for calculating totals, the ability to update data, and hyperlink support.

The Office XP Web Component Toolpack provides a walkthrough of features and is an excellent resource packed with code samples to get you started.

The OWC licensing model and deployment has changed significantly for Office XP. With Office 2000, an Office license is required to install the OWC. Additionally, the OWC could only be deployed by way of a file share. Both of these requirements limited OWC use and deployment to an intranet environment. Office XP overcomes theses limitations. The Office XP Web Components can be deployed over the Internet and an Office XP license is not required at the target.

Enhancements to the features for the Office XP Web Components necessitated changes to the OWC object model. In some areas, the changes to the object model are significant. You may find that code that worked with an Office 2000 component no longer works with the Office XP version of that component or that methods/properties in the 2000 object model have been replaced with different methods/properties in the XP object model. Or, you may find that your code gives different results for Office XP than it did for Office 2000. For this reason, you should not assume that your Office 2000 Web Component code is forward compatible with Office XP without thorough testing.

The following links provide information and sample code for the Office XP Chart, Spreadsheet and PivotTable components.

General Information About Licensing and Using Office XP Web Components (Q288729)

How To Deploy the Office XP Web Components (Q288732)

Microsoft Office XP Web Components Object Models

Microsoft Office XP Web Components Samples

Customizing the Menus and Toolbars in the Microsoft Office Web Components

Office XP Web ComponentsThe Chart Component

Create a Combination Chart Using the Office XP Chart Component (Q286211)

Change Label Orientation with the Office XP Chart Component (Q287666)

Binding the Office XP Chart Component to a Data Source (Q288907)

Bind the Office XP Chart Component to a PivotTable (Q286320)

Use An XML Data Source with the Office XP Chart Component (Q286212)

Using Timescale Axes With The Office XP Chart Component (Q289288)

Office XP Chart Component May Not Automatically Timescale (Q286319)

Create Null (or Omitted) Data Points with the Office Chart Component (Q286317)

Use Custom Layout and Drawing with the Office XP Chart Component (Q290348)

Using Format Maps to Conditionally Format Data Points and Data Series (MSDN Technical Article)

Use the Office XP Chart Component to Create Static Charts with ASP (Q286277)

Create an Interactive Office XP Chart Server-Side Using ASP (Q286278)

Data Bound Office XP Chart Prints Incorrectly From Internet Explorer 5.0 (Q286323)

Custom Layout And Drawings in Office XP Chart Do Not Print In Internet Explorer 5 (Q291128)

Office XP Web ComponentsThe Spreadsheet Component

Script the Office XP Spreadsheet Component on a Web Page (Q286209)

Use ASP to Build Spreadsheet XML for Client-Side Display (Q288130)

Client-Side Spreadsheet Component Fails to Import Data From URL (Q286316)

Setting Range.Value of Office Spreadsheet to a JScript Array Ignores Blank Entries (Q286315)

Office XP Web ComponentsThe PivotTable Component

Extract Cell Aggregate Values From the Office XP PivotTable Component (Q294782)

Use ASP to Create an Office XP PivotTable and Display the Results as HTML (Q294798)

Retrieve A List of Filtered Members Using the Office XP PivotTable Component (Q302101)

Incorrect ChildMembers.Count for PivotMember Object (Q301582)

Unable to Display Visual Totals Using the Office XP PivotTable Component (Q306756)

How to Use OLAP Number Formats with Office Web Components (Q301456)

Office HTML/XML Documents

HTML: A Native Office File Format

Office 2000 Applications introduced support for HTML as a native file format. To illustrate, you can save a Word 2000 document as an HTML file in the same manner you might save it as a .doc file; there are no add-ins and no wizards needed for the conversion. The document can be viewed in the browser and later opened in Word just as if you had saved it in the .doc binary format. The ability for Office applications to transfer documents from the browser to the application, while maintaining the document's structure and features, is called round-tripping. With round-tripping, the data needed to work with documents in their native applications is not lost when the document is published to the Web. For details on the specifications Office applications use for publishing documents to the Web, see Microsoft Office 2000 HTML/XML Reference. The following articles provide additional information for using HTML in your Office solutions:

Add HTML Code To The Clipboard Using Visual Basic (Q274326)

Add HTML Code to the Clipboard by Using Visual C++ (Q274308)

Working with Office HTML (MSDN Online Article)

Office XP and XML

Office XP applications introduce support for XML. With Office XP, you can export or import data from Excel, Access or the Office Web components in an XML format. XML support in Office XP not only enhances interoperability of Office applications with other applications but also exposes the web developer with new opportunities for integrating Office with their Web solutions.

XML Tutorial (MSDN)

XML Developer's Guide (MSDN)

XML Reference (MSDN)

Microsoft Excel 2002 and XML (Q288215)

Transform XML Files with XSLT When Importing into Microsoft Excel 2002 (MSDN Technical Article)

Use ASP to Build Spreadsheet XML for Client-Side Display (Q288130)

Use XSL to Transform Excel XML Spreadsheet for Server-Side Use (Q278976)

XML as the API

Office Add-ins and Components

Add-ins enable you to extend the capabilities of Office applications by adding custom commands and specialized features that meet a specific business need or task. Office applications support VBA-style add-ins that you can create with the VBA Editor included in each application. For example, you can use the VBA Editor to create Excel add-ins (xla), Word add-ins (dot), or PowerPoint add-ins (ppa). While VBA add-ins are easier to develop, they are not compiled and may run at speeds that are often less than optimal for some solutions.

To build a robust Office add-in, developers should consider using a programming tool such as VB, VC++, or the Microsoft Office Developer edition of VBA (which can build compiled COM Add-ins). This will enable the component to run independently of VBA. For developers working with Office XP, this could be a major factor in your design consideration since XP now offers a VBA-less installation. When VBA is not installed, compiled add-ins are the only way to extend Office XP clients. For more details, see Considerations for Disabling VBA in Office XP (Q287567).

There are several ways for developers to extend the functionality of the Office environment. Office 2000 and Office XP offer a single, uniform architecture for non-VBA add-ins, called COM Add-ins, which can be tailored to a specific application, or be designed to work in multiple Office applications. Office XP builds on this approach and offers two new COM-style Add-ins:

  • Smart Tag recognizers capable of providing "smart tags" to text that allow the user to enhance in-place editing and productivity
  • Real Time Data servers for Excel 2002 that allow cell updates in "real time"

XLLs and WLLs are still widely used for backward compatibility with older clients, while ActiveX controls offer yet another avenue for developers to extend the Office UI with custom functionality.

COM Add-ins

The following section provides resources for developing COM Add-ins for Office 2000 and Office XP. Many of the examples are written in either Visual Basic or Visual C++. If you are new to COM Add-ins and would like to see pre-built samples or step-by-step instructions for creating an add-in, the General Resources section is a good place to start. The remaining sections cover topics that are specific to manipulating Office Commandbars or working with a certain Office application.

General Resources

COM Add-ins Part I: Introducing an Office 2000 Solution for the Entire (Office) Family (MSDN Technical Article)

Developing COM Add-ins for Microsoft Office 2000 (MSDN Technical Article)

Creating a Microsoft Office 2000 COM Add-in (MSDN Technical Article)

Add-ins, Templates, Wizards, and Libraries (Office Programmer's Guide)

Build an Office 2000 COM Add-in in Visual Basic (Q238228)

Comaddin.exe Office 2000 COM Add-in Written in Visual C++ (Q230689)

Configure an Office COM Add-in for All Users on System (Q290868)

Gantt Chart Sample Add-in Available for Download (Q254008)

Access Data Retrieval Sample Add-in Available for Download (Q254006)

Places COM Add-in for Office 2000 Available for Download (Q260190)

Working with Microsoft Office Commandbars

Manipulating Command Bars and Command Bar Controls with VBA Code (Office Developer Documentation)

Create a Transparent Picture For Office CommandBar Buttons (Q288771)

Set the Mask and Picture Properties for Office XP CommandBars (Q286460)

Microsoft Excel COM and Automation Add-ins

Excel COM Add-ins and Automation Add-ins (Q291392)

Use a COM Add-in Function As An Excel Worksheet Function (Q256624)

RefEdit Control Does Not Work on Form in Com Add-In (Q281542)

Create a Visual Basic Automation Add-in for Excel 2002 Worksheet Functions (Q285337)

Install an Excel Add-in (XLA or XLL) with Automation (Q280290)

How to Mark an Automation Add-in Function as Volatile (Q278328)

Automation Add-in Function Binds to Excel Built-in Function with the Same Name (Q286305)

Excel 2002 Fails When Automation Add-in Loads (Q284876)

Microsoft Word COM Add-ins

CommandBar Control Reference Becomes Invalid Before COM Add-in's OnDisconnection Event Fires (Q230876)

Microsoft Outlook COM Add-ins

Questions About Custom Forms and Outlook Solutions (Q146636)

Customizing Outlook Using COM Add-ins (Office Developer Documentation)

COM Add-ins Part II: Building a COM Add-in for Outlook 2000 (MSDN Technical Article)

How to Create a COM Add-in for Outlook (Q230225)

All COM Add-in Run-Time Errors Must Be Trapped (Q208316)

You Cannot Fully Quit Outlook When You Use a COM Add-in (Q208332)

COM Add-ins for Other Microsoft Office Applications

Create a COM Add-in for Microsoft Project (Project 2000 SDK)

How to Work With COM Add-ins in FrontPage 2000 (Q232680)

Smart Tags

The Smart Tag is a technology introduced with Office XP that provide Office users with more content interactivity in their Office documents and can increase their productivity. A Smart Tag is an element of text in an Office document that is recognized as having custom actions associated with it. If you are considering creating your own Smart Tags for Office XP, the Smart Tag SDK is an essential resource that provides documentation plus Visual Basic and Visual C++ samples.

If you're interested in a quick step-by-step example of creating your own Smart Tags for Office, see the following articles. These articles demonstrate how you can use either Visual Basic or ATL to create a Smart Tag DLL that uses Microsoft Instant Messenger:

Create a Smart Tag DLL in Visual Basic For Use in Office XP (Q286267)

Create a Smart Tag DLL in ATL For Use in Office XP (Q292596)

For additional information and updates for Smart Tag development, see:

Smart Tags Frequently Asked Questions (MSDN Technical Article)

How to Create a Microsoft Office Smart Tag List (Q287698)

Developing Simple Smart Tags (MSDN Technical Article)

Developing Smart Tag DLLs (MSDN Technical Article)

Advanced Smart Tag Tools

Deploying Smart Tag DLLs by Using the Visual Studio Installer (MSDN Technical Article)

Adding Smart Tags to Web Pages (MSDN Technical Article)

Status Flag Is Not Updated When You Enable or Disable Smart Tags (Q294422)

Smart Tag Actions Button Doesn't Appear If Using Arrow Keys (Q282561)

Smart tag DLL Receives Error when Calling CommitSmartTag (Q297704)

Excel RealTimeData (RTD) Servers

Microsoft Excel is commonly used to monitor data that changes in real-time, such as stock quotes. Traditionally, Dynamic Data Exchange (DDE) has been used to accomplish this task, but DDE is an old technology and can present some difficulties when developing a solution. To meet the need for monitoring data in real-time for the future, Excel 2002 introduces integration with Real-Time Data (RTD) Servers. An RTD server is a COM object that can be written by anyone interested in feeding real-time data to Excel.

If you are new to RTD, Create a RealTimeData Server for Excel 2002 (Q285339) provides an introduction and walks you step-by-step through the creation of your own RTD server for Excel. The following list of articles provide additional information about using and developing RealTimeData servers for Excel:

Use an Excel RTD Server with DCOM (Q285888)

Security Settings and Microsoft Excel RealTimeData Servers (Q286259)

RTD Server Does Not Send Update Notifications to Multiple Excel Instances (Q284883)

Excel RTD Function Cannot Return An Array (Q286258)

RTD Servers Used With Embedded Excel Workbooks May Be Problematic (Q284878)

Excel Add-ins (XLLs)

An XLL is a standard Windows DLL that implements and exports specific methods. XLLs can provide new functions to Excel, where they will operate just like those that are built into the product. While XLLs are still supported in Microsoft Excel 2000 and 2002, XLLs are an older technology. When developing a new Excel Add-in, you should consider the features presented with COM Add-ins before embarking on XLL development.

The MSDN library contains excerpts from the Microsoft Excel 97 Developer's Kit that provide documentation for XLL development.

Build an Add-in (XLL) for Excel Using Visual C++ (Q178474)

Macro97.exe File Available on Online Services (Q143466)

FRMWRK32.EXE: Updated Generic.xll Template for Excel (Q152152)

Existing XLL Not Recognized by Excel After Recompilation (Q194926)

"Unhandled Exception in Mso97.dll" When Using XlCoerce with Excel 97 (Q166287)

Automation Calls to Excel from an XLL May Fail or Return Unexpected Results (Q301443)

CALL Function Always Returns FALSE When Used in a Worksheet (Q238996)

Excel May Change the Precision Mode of the Floating-Point Control Word (Q263213)

Word Add-ins (WLLs)

A WLL is a standard Windows DLL that implements and exports specific methods to extend Word functionality. While WLLs are still supported in Microsoft Word 2000 and 2002, WLLs are an older technology. When developing a new Word Add-in, you should consider the features offered by COM Add-ins before embarking on WLL development.

Note   Appendix C of The Microsoft Word Developer's Kit (ISBN 1-55615-880-7) contains the documentation needed to create WLLs for Microsoft Word. The Microsoft Word Developer's Kit is no longer in print.

Build a Microsoft Word Add-in (WLL) Using Visual C++ (Q183758)

Use Microsoft Word's CAPI Messaging Interface (CMI) (Q190057)

What Do the cmiCommandDispatch() Errors Mean? (Q183165)

Win32Cmi Sample Fails or Gives Wait Timeout Error (Q216660)

ActiveX Controls

Since Office 97, all Office documents support hosting ActiveX Controls in some form. ActiveX is really an extension to OLE, and since Office supported (or more accurately, invented) OLE first, the control containment used by Office is largely based on OLE embedding. If you are designing a control for an Office container, keep in mind that Office assumes a rich (full) control that supports all the OLE interfaces for embedding. The better the control is at native OLE embedding, the better it will function in Office.

There are a number of limitations with controls in Office (as detailed below), some of which stem from the fact that Office does not support the more advanced features of the OCX96 specification for ActiveX controls. Namely, Office does not support:

  • Windowless and/or transparent controls
  • Certain extended interfaces (for example, IViewObjectEx, IAdviseSink2, etc.) introduced in the OCX96 specification

Office also requires the control to render itself in design-mode using a metafile (WMF) when printing or previewing, instead of higher quality formats like an enhanced metafile (EMF) or a JPEG/PNG image. While some of these items have been added to certain Office products in newer versions, they are not universal. Developers building controls for Office should try to stay within the OCX94 specification as much as possible in order to maintain compatibility across all versions and applications.

For information about building ActiveX controls, and to get a copy of the OCX specifications, check out Adam Denning's book, ActiveX Controls Inside Out (MS Press, ISBN: 1-57231-350-1).

When testing your controls in Office, be aware that Office creates control extenders for VBA when inserting a control for the first time. Office/VBA will reference the extender and not your control directly. Consequently, if you are developing a control, and add, remove, or edit methods, properties, or parameters to a function and re-build the control's type library, remember to delete the extender file (*.exd) every time you re-build to keep the extender and type library synchronized.

General Information

Introduction to ActiveX Controls

Limitations of ActiveX Control Support in Office Document (Q168392)

Custom ActiveX Control Features Supported in Microsoft Access 97 (Q164241)

Supported ActiveX Controls for Microsoft Access 97 (Q160126)

Changes to Custom ActiveX Control Are Not Used (Q185473)

.Exd Files Are Created When You Insert Controls (Q290537)

Some Properties of ActiveX Controls Not Supported (Q170248)

Error Message "Cannot insert object" in Excel (Q171280)

Office Application That Hosts a Custom ActiveX Control Fails on the Save or Save As Method (Q279408)

MFC ActiveX Controls

The ABCs of MFC ActiveX Controls

Get IDispatch of an Excel or Word Document from an OCX (Q190985)

MFC ActiveX Control Fails to Insert into PowerPoint 2000 (Q243240)

Retrieve the Name of an Office Document That Contains an MFC ActiveX Control (Q266318)

ATL ActiveX Controls

Add Excel LinkedCell Support to Your ATL ActiveX Control (Q271736)

ATL Control Appears Incorrect in Access Report (Q242002)

ATL Control Properties Do Not Persist when Embedded in Word (Q241936)

Error "Bound to Unknown Type" Inserting ATL Control in VBA UserForm (Q214462)

ATL Control in Office Document Prevents Another Control's Events from Working (Q241861)

Office Document Format Information

Microsoft Office documents are OLE structured storage files created and maintained using the OLE Storage APIs of the underlying operating system. OLE files support more advanced file writing capabilities including Unicode, document summary properties, document versioning, transaction edits, complex multi-application ("compound") documents, and portability (currently implemented by Macintosh and Windows).

When working with Office documents in their native format, developers must use the same OLE Storage APIs (Stg*) as Office to ensure compatibility. Please note that current versions of Visual Basic and VBA do not support these APIs, so developers are encouraged to use VC (or a C/C++ add-in for VB) to view or edit native Office documents.

Office Binary File Formats

Microsoft Excel Binary File Format

The Microsoft Excel Binary File Format (BIFF) information is documented in the Excel 97 Developer's Kit (ISBN 1-57231-498-2). Microsoft Developer Support does not provide support for the editing, interpretation, or creation of Excel BIFF other than that which may be accomplished through the use of the Excel UI or the Excel object model. Knowledge Base articles that discuss Excel BIFF editing, interpretation, or creation are provided "as-is."

Note   Portions of the Microsoft Excel 97 Developer's Kit are contained in the online MSDN library. However, the sections that involve Excel BIFF are not included in the online MSDN library; additionally, the Microsoft Excel 97 Developer's Kit is no longer in print.

Support for the Microsoft Office Binary File Formats (Q239653).

Microsoft Excel 97 Developer's Kit

How To Create a BIFF5 File (Q150447)

Records Needed to Make a BIFF5 File Microsoft Excel Can Use (Q147732)

Determine the Version of a Microsoft Excel Workbook (Q178605)

Determine Which Version of Excel Wrote a Workbook (Q225029)

Distinguish Between Excel 97 and Excel 2000/2002 Files (Q269168)

Determine If an Excel Workbook Contains Macros (Q224518)

Excel BIFF8 CONTINUE Record Information Is Incomplete (Q207475)

Excel BIFF8 Extended Rich String Formatting Run Is 4 Bytes (Q284436)

Excel BIFF8 FORMAT Record Documentation is Incomplete (Q284441)

STRING Record Description Omits grbit Field (Q197489)

Other Office Binary File Formats

The Excel binary file format is the only Office file format that Microsoft has published. Microsoft Developer Support does not provide support for the Microsoft Word, PowerPoint or Access binary file formats; additionally, these formats are not on the MSDN and are not available for download. For more information, see the Knowledge Base article INFO: Support for the Microsoft Office Binary File Formats (Q239653).

Office Document Properties

Document Summary Properties are a part of the OLE Structured Storage specification and are used by all Office applications to save additional document information—like the Author, Title, and Template Name—that helps identify it in a document management system or from an Index Server query. Since these properties are part of OLE and not specific to Office, developers can read and edit them without having to open the documents in Office (or have Office installed).

For more information about accessing OLE Summary Properties and its format, please see the documentation for Structured Storage in the MSDN Library:

Property Set Implementations in COM

The following articles provide information about reading and editing Office document properties. A utility to make the job easier (dsofile.exe) is also included:

Dsofile.exe Lets You Edit Office Document Properties from Visual Basic and Active Server Pages (Q224351)

Read Compound Document Properties Directly with VC++ (Q186898)

Use Visual C++ to Access DocumentProperties with Automation (Q238393)

Office OLE Objects and ActiveX Documents

Microsoft Office was the first product to offer Object Linking and Embedding (OLE), which allow applications to cooperatively share "objects" between documents.

OLE paved the way for many technologies now common to Windows programming, including Automation, COM/COM+, ActiveX controls, OLEDB, and DirectX. The original OLE functionality, however, has remained fairly unchanged since the introduction of version 2.0 in 1990. The only important change came in 1994 when Microsoft introduced ActiveX Documents. An ActiveX Document is an extended type of OLE object that allows an entire document to be embedded in a host rather than a single sheet, chart, or section. The ActiveX Document technology allows Office documents to appear directly in containers like Internet Explorer.

Developers can use Office OLE and ActiveX Documents for in-place editing and document viewing capabilities with Office documents, or may wish to provide such services for Office to use. OLE is one of the more difficult programming tasks a developer can face, but the results can often give an application a more "integrated" look and feel, and stronger abilities to work with other OLE clients/servers as they appear in the future.

OLE is still the primary means for Office applications to share resources on a rich client. However, the Office Web Components (OWC) could be used to replace some of the functionality served by Office OLE objects. But, for rich clients wanting full in-place editing and menu merging capabilities, OLE is still the most powerful and feature-rich technology available.

Office OLE Objects

TN039: MFC/OLE Automation Implementation (MFC Technical Note)

Embed and Automate Office Documents with Visual Basic (Q242243)

Embed and Automate a Microsoft Excel Worksheet with MFC (Q184663)

Embed and Automate a Word Document with MFC (Q238611)

Embedded Word Document Scrolls to Top While Editing In-Place (Q268278)

ScrollBars Disappear on Mouse Move in OLE Container (Q190520)

VB Hangs While Automating Excel Using OLE Control (Q181889)

ActiveX Documents

Automate Linked and Embedded ActiveX Documents (Q193039)

Use the WebBrowser Control to Open an Office Document (Q243058)

MFCBIND: Active Document Container (Sample)

Framer: Active Document Objects Hosting as a Container (Sample)

FramerEx.exe Is an MDI ActiveX Document Container Sample Written in Visual C++ (Q268470)

MFC ActiveX Document Container Hangs During Shutdown on Windows 2000 (Q272256)

This document contains resources compiled and reviewed by the Microsoft Developer Support Team that specializes in developing Office solutions with Visual Studio. Special thanks for their contributions to this document go to Joel Alley, Gerard Collop, Rob Dil, Mark Durrett, Greg Ellison, Chris Jensen, Ranjit Sawant, Richard Taylor, and Lori Turner.

Show:
© 2014 Microsoft