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.
Created: May 2001
Revised: September 2001
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)
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
Office Web Components
Office HTML/XML Documents
Office Add-ins and Components
Excel RealTimeData (RTD) Servers
Excel Add-ins (XLLs)
Word Add-ins (WLLs)
Office Document Format Information
Office Binary File Formats
Office Document Properties
Office OLE Objects and ActiveX Documents
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++®.
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.
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 XP Object Models
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:
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
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
Concepts That Apply to All Office Automation Servers
Microsoft Excel Automation
Microsoft Word Automation
Microsoft PowerPoint Automation
Microsoft Access Automation
Microsoft Outlook Automation
Automation to Shared Office Components and Other Office Applications
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).
Do 16-Bit Automation in C++ Using VC 1.52 (Q194656)
Microsoft Excel Automation
COMEXCEL: Demonstrates an Automation Client Application with Compiler COM Support
Microsoft Word Automation
Microsoft PowerPoint Automation
Microsoft Outlook Automation
Automation to Shared Office Components and Other Office Applications
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.
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.
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.
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).
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:
The following list contains known problems you may encounter when automating an Office application on a Web server:
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.
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.
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 Components—Resources and References
For an introduction to the Office 2000 Web components and references for the object model, the following resources are most helpful:
Microsoft Office 2000 Web Components Object Models
Office 2000 Web Components—Script 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.
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 Components—Licensing and Deployment
Office 2000 Web Components—The Chart Component
Office 2000 Web Components—The Spreadsheet Component
Office 2000 Web Components—The PivotTable Component
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.
Microsoft Office XP Web Components Object Models
Microsoft Office XP Web Components Samples
Office XP Web Components—The Chart Component
Office XP Web Components—The Spreadsheet Component
Office XP Web Components—The PivotTable Component
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:
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)
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.
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.
Working with Microsoft Office Commandbars
Microsoft Excel COM and Automation Add-ins
Microsoft Word COM Add-ins
Microsoft Outlook COM Add-ins
COM Add-ins for Other Microsoft Office Applications
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:
For additional information and updates for Smart Tag development, see:
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:
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.
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.
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.
Introduction to ActiveX Controls
MFC ActiveX Controls
ATL ActiveX Controls
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.
Microsoft Excel 97 Developer's Kit
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:
The following articles provide information about reading and editing Office document properties. A utility to make the job easier (dsofile.exe) is also included:
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
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.