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
- 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
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.
Discusses Office 97 and 2000 Automation and Provides Sample Code
More sample Code can be found here:
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
2001 Microsoft Corporation. All rights reserved.