Microsoft Office Tutorials and References
In Depth Information
Understanding Office Automation
You get the idea — sooner or later you’ll need to perform some kind of operation that another
application was especially made to handle. This lesson lays the groundwork for you to understand Office
automation from Excel, and the theory behind some best practices in doing so.
undErsTAnding officE AuToMATion
Where VBA is concerned, the only difference between Excel, Word, Access, PowerPoint, and
Outlook lies in their object models. Each of these applications can access another’s object model,
so long as the target application has been properly installed on the host computer. Controlling one
Office application from another becomes a simple matter of knowing how to link to the object
model of the Office application you want to control.
The term “automation” is an Office programmer’s way of referring to the VBA technology that
provides the ability to manipulate another application’s objects. Though VBA is the common language
among Office applications, the respective object models differ in their objects’ names, methods,
and properties. Both Excel and Word have a Range object but with different properties. Excel has a
Workbooks object, which is the counterpart to PowerPoint’s Presentations object.
For Excel to access another Office application’s object model, a connection needs to be established
to that target application. Two options for doing this exist: one option is called early binding , and
the other option is called late binding . The term “binding” refers to the verification that an object
exists, and that the command to manipulate that object’s methods and properties is valid.
With early binding, a reference is established with the
target application’s object library before you write
your macro, so that the application’s objects,
methods, and properties can be accessed in your code. For
example, if you are using Office 2010 and you want
to write a macro to open Word and edit a document,
you would first need to establish a reference to the
Microsoft Word 14.0 Object Library. To do that, you
can go to the Visual Basic Editor, and from the menu
bar click Tools ➪ References and scroll to select the
reference, as shown in Figure 26-1.
VBA sees versions of Microsoft Office as numbers, not names. For example, VBA
knows Office 2003 as version 11, Office 2007 as version 12, and Office 2010 as
version 14 (Microsoft knowingly skipped number 13). Therefore, if you are
working with Office 2007 at home, you’d have Word 12 listed in your VBA References,
but if you are using Office 2010 at work, you’d see Word 14 listed.