The usual way to get Office applications to talk to each
other—and to automate their interaction—is via the built-in VBA language. But developers
should start looking to the future of Microsoft programming, Visual Studio .NET,
with its far richer set of tools, its object-oriented features, and its
enormous framework (a set of code libraries containing useful objects, such as
Many developers find themselves tasked with devising ways
for Office applications to communicate among themselves, or with the outside
world. I’ll show you how to write a VB.NET application
that instantiates Access and makes it display a report. Using the techniques
illustrated here, you can automate Office applications from outside the Office
suite, and from outside VBA.
Creating your first VB.NET project for Office 2003
The following example assumes that you have Visual Studio
2003 and Office 2003 (though it may work with earlier versions, we haven’t
tested it). You’ll also need the sample Northwind database, as that is the
database that this app opens.
Start up VB.NET and open a new Windows-style application.
Choose File | New | Project from the menus. Click Visual Basic Projects in the
left pane (Project Types), then double-click Windows
Application in the Templates pane. Form1 appears, but get to the code
window by double-clicking the form.
You now see the Form_Load event that VB.NET programmers use
to write initialization code. In the case of a utility with little code, like
the one I’m illustrating here, programmers often put all the code in this event.
Type in the code from Listing A into the
Form1_Load event (you can also cut-and-paste, but where’s the fun in that?). As
you type in this code, you likely will see sawtooth lines (indicating an error)
under the following items:
If you hover your mouse pointer over these objects, you see
a little message saying that this type is not defined. For experienced VB
programmers, you may be surprised by the meaning of the word type in this context. Don’t be alarmed:
This is just one of the side effects of the fact that VB.NET was not written by
VB programmers. Nor does it seem that many people with VB experience were
included in the process of documenting VB.NET. So just get used to lots of
C-language inflections, of which this is one. Type here really means object.
Download the Office 2003 assembly to test this code
If you press F5 and try to test this code, you get the same
type-not-defined error messages. What’s happened is that you’ve not included a
necessary “reference” (similar to using an Imports statement) to
bring in the Access object library.
You have to choose Project | Add Reference from the VB.NET
code window, then click the COM tab in the dialog box. Now scroll down to find,
and double-click, the Microsoft Access 11.0 Object Library (11.0 means Office
2003). Click OK to close the dialog box, thereby adding this library of
Access-specific code to your VB.NET project.
What if you can’t find Microsoft Access 11.0 Object Library
in the list of COM libraries? This is just another brick wall that programmers
run into all the time in the lovely world of distributed frameworks. Luckily,
it’s easy to fix if you don’t have this particular object library. Just download it from MSDN.
And to make your life richer with confusing nomenclature,
the item you’re downloading is called Office XP primary interop assembly. Assembly is yet another term for code
library, DLL, or whatever you want to call them. I count at least 25 different
terms for code library: assembly,
control library, class library, control type library, core type library,
development environment, dynamic link library, extensibility, host object
model, kernel, helper, object library, object model, namespace, project model,
proxylib, plug-in, plugin type library, runtime execution library, runtime
execution engine, runtime library, services, services library, type library. Admittedly,
some of these terms do have slightly different connotations. For example, a control often adds a user interface to
its code library. Nonetheless, most of these terms are merely redundant synonyms.
After you’ve downloaded the Office XP PIA, do you suppose
you can then just use it out of the
box? Sorry, but you’ve got more tinkering to do. It comes with a readme.htm file
that describes the steps you have to take to get it up and running. Also, be
sure to restart Windows after you’ve installed the PIA. As an aside, you can sometimes communicate between VB.NET and
Office 2003 applications without using this code library. However, for the
example in this article, the PIA is required.
Running your VB.NET application
Now you should be ready to go ahead and press F5 to see the
code execute. Access should start up and then display the Sales by Category report
in the Northwind sample database. If you see an error message indicating that
Access cannot open the database, it means that your Northwind.mdb database isn’t
located in this path on your hard drive:
“C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb”
Either locate this sample database on your hard drive and
change the path in the code, or install it from the Visual Studio CDs. That
should be all it takes to get this example working—your very first VB.NET
Office 2003 application.