Many situations occur where you need to transmit
data to a person who doesn’t have Access, where Access limitations prevent you from
doing exactly what you feel is required, or where you need to perform a function that
Access doesn’t support. Examples include
sending data to a user in Excel report format from an Access database, creating a
report with complex formatting in Word, and sending email from an Access database. This
tutorial introduces the concept of Automation (formerly known as OLE Automation and
before that as ActiveX – all these were a replacement for something called DDE which
was a simple, but slow and fragile technology used to communicate between applications
– many people still use it for mail merges between Access and Word), and describes
some of the power provided by it.
Automation is provided as a feature of COM (Component Object Model)
technology. Applications built using
this technology make their objects accessible to VBA and to other applications that
are COM compliant. In this view of the
world, there is always an Automation Client and
an Automation Server – the distinction
is that the client is the host application, and the server is the application the
client is controlling. For example, if
you build a report in Excel or Word using Access, Access is the client, and Excel
or Word is the server. On the other hand,
if you reach into an Access database to extract data and present it in Word using
a Word VBA module, Word is the client and Access is the server. Earlier
versions of Office applications had some constraints, as not all of them could act
as servers, but with Office 2000, all mainstream applications could be servers. However
you may encounter Microsoft other vendor applications that are not mainstream that
have similar constraints.
How
do I create an automation session between two applications?
Let’s suppose you want to create a Word report from Access. In
that case the VBA in and Access module would look something like this:
Dim wordApp
As Object 'Declare
object variable
Set wordApp
= CreateObject("word.application") 'Start
Word
This code will create an instance of Word if it isn't already running,
but it may not be visible so you will probably want a line of code to make it visible:
wordApp.Visible
= True 'make
the Word instance visible
Then to convince yourself that you really did something useful, you
need to create a simple document:
wordApp.Documents.Add '
create a new document
wordApp.Selection.Text
= "My dog has fleas!" 'insert some text
Finally, once you've done what you need to, you must remember to close
the application and release the reference (and the memory it uses):
wordApp.Quit '
Terminate the Word application
Set wordApp
= Nothing '
Release the object resources
That was a simple example,
but how do I deal with real-world problems?
The example of automating Word from Access is simple, but it demonstrates
most of the principles of Automation. Unfortunately,
there are a number of issues and situations that need to be considered, but many are
beyond the scope of this tutorial. Examples
include "what if Word or whatever application is already open?", "what are the unique
aspects of the Automation Server?", "should I use early or late binding (the example
uses late binding)?", and "how do I deal with errors that occur along the way?" A
case in point is how to deal with saving the Word document that was created above. If
you run the code as it is, you will notice that you get a prompt asking if you want
to save the new document before Word closes, a characteristic of Word and many other
applications. In a real world situation,
you would either want to automate the save process completely, or provide a more friendly
prompt such as do you want to save the new document. To
see an example of an actual Automation project, visit our tutorial on
mail merges. Dealing with real-world
problems invariably requires that you understand the object model of the application
you are automating
A first step is setting references to the application(s) you intend
to work with. That can be done with the
VBA editor, by using the Tools / References command
which brings up the References dialog box. When
you do that, you will typically see references set to things such as Visual Basic,
Microsoft Access XX Object Library, and OLE Automation. You
may also see references set to such things as the Microsoft Office XX Object Library,
Microsoft DAO or ADO, and other applications. The
references are set on a database by database basis, so each new database must have
its own special references set. Once
you have that, the challenge is to explore the object model.
How do I find out about the
object model of the application?
One way is to open a VBA module in that application, open VBA help,
and search for "object model." Each of
the applications has its own object model, and the help files give a graphical view
of that model. If you want more detail,
the object browser is the tool of choice to see all of the objects and methods. It
can be opened from the VBA editor for any referenced application, and is activated
by pressing F2, clicking on the Object Browser toolbar button, or by selecting View
/ Object Browser. With this tool, you
can determine what objects exist and some of the components of the object. If
you highlight a specific member of an object class, you can press the F1 key and VBA
Help will be opened to the specific topic for that member. Unfortunately
the help files with Office 2000 and 2002 do contain some errors where no help is displayed,
so you may want to check for the latest version of help available by download, or
refer to the online version of help files on the Microsoft web site.
Where can I get more information
about Automation?
There are several places you can get additional details and examples
of Automation. For starters there is
the Help for VBA. If you search on the
string “Understanding Automation” you will get some topics – another search string
“OLE Automation” will return some different topics. The
following topics are useful in our opinion:
Understanding Automation
Automation with Microsoft Access
CreateObject function
GetObject function
Object property
Set Reference to a Type Library
User Control Property
Application Object
SetOption Method
Working across applications
A second resource that is quite valuable in getting examples and basic
concept information is the Microsoft Knowledge Base. Some
of the more useful articles are:
Microsoft
Office 2000 Automation Help File Available (if you are starting
an automation project be sure to download the latest version of this help file)
GetObject
and CreateObject Behavior of Office Automation Servers
Obtain
Built-In Constant Values for an Office Application
Find
and Use Office Object Model Documentation
Using
Automation to Create and Manipulate an Excel Workbook
Using
Automation to Transfer Data to Microsoft Excel
Using
Automation to Create a Microsoft Excel Chart
HOW
TO: Call Excel Functions from Within Microsoft Access 2000
Sending
the Current Record to Word 2000 with Automation
Use
Automation to Find a Location in a Word Document in Access 2000
How
to Use Automation to Run Word 2000 Mail Merge from Access
How
to Use Automation to Find Bookmarks in Word 2000 Documents
How
to Use Automation to Create a Word 2000 Merge Document
Use
Automation to Print a Microsoft Word Document using Access 2000
How
to Use Automation to Create a New Contact Item in Microsoft Outlook
How
to Use Automation to Add a Task or a Reminder to Microsoft Outlook
Use
Automation to Send a Microsoft Outlook Message using Access 2000
How
to Use Automation to Add Appointments to Microsoft Outlook
How
to Use Automation to Create a PowerPoint Presentation
How
to Implement Automation to Microsoft Project
How
to Use Visual Basic for Applications to Minimize, Maximize, and Restore Access
You are also likely to find topics related to the Automation of various
applications at the Microsoft MVP site www.mvps.org . In
the world of books, an excellent and detailed exploration of Automation can be found
in “The Access Developer’s Handbook” by Litwin, Getz, et. al. published by Sybex with
versions for 97, 2000 and 2002 of Access. Other
titles that have a significant description of Automation include “Beginning Access
VBA Programming” and “Professional Access VBA Programming”, both published by Wrox,
“F. Scott Barkers Access 2000 Power Programming”, and “Alison Balter’s Mastering Access
2002 Desktop Development”, both published by SAMS.