Jinks Technology Management, project management, software and website design and development

Contents


Home
Business Process Reengineering
Project Management
Application and OLE  Development
Website Design and Development
Products
Automation Support
Contact JTM
About JTM

Copyright 2011
Jinks Technology Management, Inc.

All rights reserved.
Revised: August 06, 2011

Automating Microsoft Office From External Clients

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 (e.g., 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 three 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.
  • 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.

White paper: 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.

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

More sample Code can be found here:

INFO: Office Developer Samples and Tools Available for Download (Q253338)

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.

2001 Microsoft Corporation. All rights reserved.

 

Revised: June 21, 2006.         JTM Home Page