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 encryption).
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.