Integrating Access 2003 with Other Microsoft Office Applications

Visual Basic for Applications (VBA) isn't a programming language for Microsoft Access 2003 alone. It's a programming language for all application programs that support Automation. Automation (with a capital A) refers to the ability of a program to be manipulated programmatically, or controlled automatically using a programming language such as VBA. All the major applications in Microsoft Office, including Microsoft Access, Microsoft Excel, Microsoft Outlook, Microsoft PowerPoint, and Microsoft Word support Automation. You can write code to automatically share data among them.

The terms Component Object Model (COM) and OLE automation are older terms for (but are basically synonymous with) what's currently called Automation.

Before you start writing code

Before you exchange data between Access and other programs in the Microsoft Office Suite, be aware that writing code to do the job is rarely required. You can do plenty of importing and exporting data between Microsoft Office applications without writing any code at all. For example, you can perform the following actions:

• Import and export data by using options on the Access File menu.

• E-mail Access objects, such as reports, by choosing Send To --> Mail Recipient.

• Use the OfficeLinks feature to send objects to other programs.

• Use basic Windows cut-and-paste techniques and OLE (Object Linking and Embedding) to copy and link data between programs.

• Merge data from Access tables to Microsoft Word letters, labels, envelopes, or other reports, using the Word Mail Merge feature. (Search the Word Help system for merge.)

If you're just looking to get data from Access to another program (or vice versa), writing code is probably not the easiest approach. Any of the previous approaches are easier than writing custom VBA code to do the job.

Of course, once in a proverbial blue moon, you come across a situation where writing VBA code is the only, or perhaps just the best, way to get the job done. This article shows you some basic concepts that you'll want to understand.

Loading the appropriate object models

The Access object model provides a means of referring to objects by name so that you can manipulate those objects by using VBA code. Every Office application program that exposes itself to VBA has an object model, just as Access does. After an application program's object library is available, you can use VBA to control that application.

An object model defines the names and organization of objects in the application. An object library is the actual file that's stored on your hard disk and defines that conceptual object model.

Before you write code to control an external application from Access, you need to load the appropriate object library into the References dialog box in Access. Follow these steps:

1. Make sure that you're in the Visual Basic Editor.

Press Alt+F11 if you're in the Access program window.

2. Choose Tools --> References from the Visual Basic Editor menu.

The References dialog box opens.

3. Scroll through the Available References list and select the object libraries for the programs that you want to control.

In Figure 1, the selected object libraries are for Access, Excel, Outlook, PowerPoint, and Word (among others).

>

Figure 1: Several object libraries selected in the References dialog box.

4. Click OK.

All the selected object libraries will be opened, and you have access to all their object models from this point on.

Unloading object libraries

Loading more object libraries than necessary is wasteful because VBA programs run slower. In real life, you rarely need to select all the object libraries available (see Figure 1) unless you really intended to interact with all those programs from the current database.

You can unload libraries as easily as you load them. Open the References dialog box and clear the check mark next to any object library that you don't really intend to use.

Exploring a program's object model

The Object Browser in the Visual Basic Editor provides access to all the object models currently selected in the References dialog box. Each loaded object model contains many objects, classes, properties, and such. But for Automation, you mainly want to look at each program's Application Object. For example, the Access Application Object exposes Access to other programs that support Automation. The Excel Application Object (contained within the Excel object library) exposes Excel to other Automation programs and so on.

To open the Object Browser in the VBA Editor, choose View --> Object Browser or press F2. To get help with an item in the Object Browser, click its name, and then click the Help (?) button in the Object Browser toolbar.

When you choose <All Libraries> from the Project/Library list in the Object Brower and scroll down the Classes list in the left column, you see several Application objects. When you click one of the Application objects, the name of the application appears down near the bottom of the dialog box. Members of that application object appear in the pane to the right, as shown in Figure 2.

>

Figure 2: The Object Browser after adding multiple object libraries.

Setting references to other programs

With object models loaded, you're ready to start setting up references to available programs in VBA code. The first step is to use a Dim statement to create an object variable that refers to the application to which you want to connect, using the following syntax:

Dim anyName As [New] program.Application

In the syntax, anyName is any name you like, to be used within your code to refer to the application. The program argument is the official name of the Automation program. The New keyword is optional. If included, the New keyword opens a copy of the application in the background (not necessarily visible on-screen) before the code runs.

Some examples of Dim statements are the following:

Dim appExcel As New Excel.Application
Dim appOutlook As New Outlook.Application
Dim appPowerPoint As New PowerPoint.Application
Dim appWord As New Word.Application

A Dim statement is valid only if the appropriate object library is loaded. For example, the Dim appWord As New Word.Application statement fails if the object library for Microsoft Word isn't selected in the References dialog box.

The Dim statements merely create object variables that refer to the application from within your code. To actually make the connection to an application's Application object, you need to set the object variable name to the application. The syntax is

Set objectVariable As CreateObject("appName.Application")

where objectVariable is the same as the name you specified in the Dim statement, and appName is the name of the application program. Referring to the earlier Dim statements, the Set statements that you use for each defined object variable are the following:

Set appExcel = CreateObject("Excel.Application")
Set appOutlook = CreateObject("Outlook.Application")
Set appPowerPoint = CreateObject("PowerPoint.Application")
Set appWord = CreateObject("Word.Application")

Each application in the Office suite has its own object model with its own objects and properties, but the basic ideas are the same regardless of which program and object model you use.

>
dummies

Source:http://www.dummies.com/how-to/content/integrating-access-2003-with-other-microsoft-offic.html