Using Automation

Using Automation, you can integrate two or more components in a way that appears seamless to the user. Working with objects through Automation is very similar to working with objects in the component itselfyou set and retrieve the object's properties and apply its methods.

When you perform an Automation operation, there are always two components involved. One component contains the objects you want to use. The other component is the one in which you are currently working, and from which you want to control those objects. Many components, including Microsoft Access, can act as either type of component. Some components can act as one or the other, but not both.

For example, if you are working in Microsoft Access, you can create a Microsoft Word document through Automation if Microsoft Word 97 is installed on your computer. On the other hand, if you are working in Microsoft Word, you can use Automation to start Microsoft Access so that you can create a new database or retrieve information from an existing database.

Note   Microsoft Word 97 is the first version of Microsoft Word that supports Automation.

To use Automation in Microsoft Access, perform these steps, which are explained in greater detail in the sections that follow:

1.  Set a reference to the component's object library.

2.  Create an instance of a class that defines an object in that object library and assign it to an object variable.

3.  Work with the new object by setting its properties and applying its methods.

4.  Close the object by using its Quit or Close method, or use the Nothing keyword to free the memory it has been using.

Setting a Reference to an Object Library

Any component that supports Automation supplies information about its objects in an object library. The object library contains classes that define individual objects. An object's class includes its name and information about its properties and methodsthe information you need to know about the object in order to use it through Automation.

Before you use another component's objects in Visual Basic, you should set a reference to that component's object library. Once you set a reference to an object library, Visual Basic recognizes the objects in that object library. You can then view those objects and their properties and methods in the Object Browser, which is available through the View menu when you have a module open.

   To set a reference to a component's object library

1   Open a new or existing module.

2   On the Tools menu, click References.

Microsoft Access displays the References dialog box.

3   In the Available References box, select the check box next to the object library that you want to reference.

If a component's object library doesn't appear in the list, click Browse to locate the object library yourself. Often object library files end with .olb or .tlb file name extensions.

For example, to see what objects are available in Microsoft Word, set a reference to the Microsoft Word 8.0 Object Library from Microsoft Access. Then open the Object Browser and click Word in the Project/Library box. The Classes box now lists all of the objects in the Microsoft Word object model.

See Also   For more information on the Object Browser, see "Using the Object Browser" in Chapter 5, "Working with Objects and Collections." For more information on setting references to object libraries, see "Referencing Library Databases" in Chapter 12, "Using Library Databases and Dynamic-Link Libraries."

Note   You can also set references in Visual Basic. For more information on setting references in code, search the Help index for "Reference object" or "References collection."

Binding Object Variables

You can use a component's objects without setting a reference to that component's object library. However, your code runs much faster if you do set a reference. Setting a reference affects when your code is bound. Binding is the process by which Visual Basic determines what type of object an object variable refers to. There are two types of binding: early binding and late binding.

If you haven't set a reference to the object library, you must declare any object variables that point to the component's objects as type Object. When you declare an object variable as type Object, your code is late-bound. With late binding, Visual Basic has to determine at run time what type of object it is that you want to use; this process slows down your code.

Once you've set a reference to an object library, you can declare object variables according to their specific types. When you declare an object variable as a specific object type, your code is early-bound. Early-bound code runs faster than late-bound code because with early binding, Visual Basic determines what kind of object to create when you compile your codebefore you run it.

For example, if you are using Microsoft Excel's objects through Automation, you can set a reference to the Microsoft Excel object library and then declare an object variable that points to a Workbook object as type Workbook. This code is early-bound.

Dim wbk As Excel.Workbook

Note   You can also declare the object variable in the previous example by using the New keyword. For more information on the New keyword, see "Creating a New Instance with the New Keyword" later in this chapter.

Alternatively, if you declared the variable as type Object, the code would be late-bound. This is true whether or not you've set a reference to the Microsoft Excel object library.

Dim wbk As Object

See Also   For more information on object libraries, see Chapter 5, "Working with Objects and Collections." For more information on setting references, search the Help index for "setting references."

Creating an Instance of a Class

Once you set a reference to a component's object library, you can begin using that component's objects in Visual Basic. To create an object to use in Automation operations, you create an instance of that object's class and assign the instance to an object variable. The object variable then points to the new object in memory. Note that when you create a new instance of a class, you are simply creating an object defined by that class.

You can't immediately use any object in the component's object model, however. When you begin an Automation operation, the operating system has to start the component and return a reference to it. There are only a few objects in each object hierarchy that the operating system itself recognizes. Objects that the operating system recognizes are registered with the operating system. When you begin an Automation operation, you must begin with a registered object.

Most components that are also applications have an Application object at the top of their object hierarchy which represents the component and contains all other objects and collections. The Application object is always recognized by the operating system. When you create a new instance of a component's Application class, the operating system starts the component. You can then access all of the other objects in the component's object model through the Application object.

Some components register a few other objects with the operating system as well. For example, Microsoft Excel registers a Workbook object in addition to its Application object. You can create a new Workbook object directly, without going through the Application object.

See Also   For more information on which objects can be created directly through Automation, see the component's documentation.

The different ways to create a new instance of an object's class are described in the following sections.

Creating a New Instance with the New Keyword

The simplest way to create a new object is to declare an object variable by using the New keyword. When you use the New keyword, Visual Basic creates a new instance of an object's class, returns a reference to that instance, and assigns the reference to the object variable being declared.

For example, the following line of code creates a new instance of the Microsoft Excel Application class, returns a reference to that instance, and assigns it to an object variable.

Dim appXL As New Excel.Application

You can use the New keyword syntax only if you have set a reference to the object library supplied by the component. For example, you must set a reference to the Microsoft Excel object library before you can create a new instance of the Microsoft Excel Application class and assign it to an object variable. Variables declared with the New keyword are always early-bound.

Note in the preceding example that the Application object is qualified so that Visual Basic knows to create the Microsoft Excel Application class. If you were to omit this qualification, Visual Basic would create a new instance of the Microsoft Access Application class, because you're working within Microsoft Access. You can qualify an object with the name of the component that supplies the object library, as long as you've set a reference to that object library. To determine the name of the component, open the Object Browser and look in the Project/Library box. In the preceding example, the name of the component is Excel.

Note   It's a good idea to make a habit of qualifying all objects used in Automation operations, because objects in different object libraries may have the same names. If you qualify your objects as you write Automation code, you can be assured that your code will continue to run properly in the presence of new object libraries that may be introduced in the future.

If you want to use Microsoft Access objects from another component, such as Microsoft Excel, you can use the New keyword to create a new instance of the Microsoft Access Application class. For example:

Dim appAccess As New Access.Application

Each time you use the New keyword to create a new instance of a component's Application class, Visual Basic starts the component. If the component is already running and you don't want to start another instance, you should use the GetObject function.

See Also   For more information on the GetObject function, see "Getting an Existing Instance with the GetObject Function" later in this chapter.

A component that supports the New keyword syntax must have an object library, but not all components with object libraries also support the New keyword syntax. Check the component's documentation to determine whether it supports the New keyword syntax. For components that don't support the New keyword syntax, you can use the CreateObject function to return a new instance of an object's class.

Creating a New Instance with the CreateObject Function

The CreateObject function creates a new instance of a class and returns a reference to that instance. The CreateObject function is most useful for working with components that don't support the New keyword syntax, although you can also use it with components that do support the New keyword.

Use the Set statement to assign the reference returned by the CreateObject function to an object variable. The syntax for the CreateObject function is:

Set objectvariable = CreateObject(class)

In this syntax, objectvariable is an object variable of the type of object you want to create. For example, Microsoft Office Binder supplies an object library but doesn't support the New keyword. To work with an instance of Microsoft Office Binder, you would first declare an object variable of type Binder, qualifying it with the name of the object library, as follows:

Dim bnd As OfficeBinder.Binder

In order for this example to work, you must have Microsoft Office Binder installed on your computer, and you must set a reference to the Microsoft Office Binder object library.

As discussed earlier, declaring the object variable as a specific object type makes your code early-bound. If you declare the object variable as type Object, your code is late-bound.

The class argument specifies which component creates the object and what type of object it is, and takes the form appname.objecttype. To determine what the class argument should be, consult the component's documentation. In the following example, appname is Office and objecttype is Binder.

Set bnd = CreateObject("Office.Binder")

The following example creates a new binder and adds a new Microsoft Word document and a new Microsoft Excel document.

Sub CreateNewBinder()
   ' Declare variable of type Binder.
   Dim bnd As OfficeBinder.Binder
   ' Create new instance and assign to variable.
   Set bnd = CreateObject("Office.Binder")
   ' Add new Microsoft Word document.
   bnd.Sections.Add("Word.Document")
   ' Add new Microsoft Excel workbook.
   bnd.Sections.Add("Excel.Sheet")
   ' Save binder.
   bnd.SaveAs FileName:= "NewBinder.obd"
   ' Free memory.
   Set bnd = Nothing
End Sub

Note   If you have more than one version of a component on your computer, you can specify the version number of the component that you want to use for an Automation operation. The following example creates a new instance of the Microsoft Excel version 7.0 Application class.

Dim appXL As Excel.Application
Set appXL = CreateObject("Excel.Application.7")

The version number for most applications included in Microsoft Office 95 is 7.0, while those in Microsoft Office 97 are version 8.0. For example, Microsoft Excel 95 is version 7.0, and Microsoft Excel 97 is version 8.0.

Getting an Existing Instance with the GetObject Function

You can use the GetObject function to return a reference to an existing instance of a class in order to use that instance for an Automation operation. The GetObject function is useful when an instance of a component's Application class already exists in memory, and you don't want to create another instance. Use the Set statement to assign the reference returned by the GetObject function to an object variable. The syntax for the GetObject function is:

Set objectvariable = GetObject([pathname][, class])

As with the CreateObject function, objectvariable is an object variable of the type of object you want to work with. For example, if you want to work with an instance of the Microsoft Excel Application class, first declare an object variable of type Excel.Application, and then use this variable with the Set statement and the GetObject function, as follows:

Dim appXL As Excel.Application
Set appXL = GetObject(, "Excel.Application")

The optional pathname argument specifies the fully qualified path to an existing file. Use this argument if you want to work with an object in a specific file. The class argument specifies which component creates the object and what type of object it is, and takes the form appname.objecttype. In the previous example, appname is Excel and objecttype is Application.

The following table provides a list of class arguments for some Microsoft components.

See Also   To determine the class argument for a component not in this list, consult the component's documentation.

You must specify either the pathname or the class argument, or both. If you specify a zero-length string ("") for the pathname argument, the GetObject function creates a new instance of the component's Application class specified by the class argument.

You will get different results depending on which arguments you use with the GetObject function. The examples in the following table show different uses of the GetObject function with Microsoft Excel.

If a component saves documents to disk as files, you can use the GetObject function to return a reference to an object in a specific document. For example, you can use the GetObject function to return a reference to a specific Microsoft Excel Workbook object, as shown in the last entry of the preceding table.

This can be useful if a user has more than one instance of a component running at one time and you need to return a particular instance. For example, suppose your code has already opened the Microsoft Excel workbook Revenue.xls. If there's another instance of Microsoft Excel open at the same time, the GetObject function may return either one. If you specifically need a reference to Revenue.xls, then you can specify the path and file name as an argument to the GetObject function.

The following example creates an instance of the Microsoft Excel Application class and returns a reference to the Workbook object that represents the file Revenue.xls. The instance of Microsoft Excel is invisible to the user.

Dim wbk As Excel.Workbook
Set wbk = GetObject("C:\My Documents\Revenue.xls", "Excel.Sheet")

In the preceding example, the class argument, "Excel.Sheet", is optional. If you omit this argument, the operating system still knows what type of object to create because you've specified a particular file name. However, if you have more than one version of the component on your computer, you should specify the class argument with the component's version number so that the correct version is used.

Working with Objects Through Automation

Once you've created a new object and returned a reference to it, you can work with the object in Visual Basic in the same way you would work with any other object. That is, you can set and retrieve the object's properties and apply its methods.

Automation is useful when you're writing code within a component that doesn't provide some functionality you need, but you have another component that does provide that functionality. If both components support Automation, you can use the second component's objects, properties, and methods from the first component.

For example, Microsoft Access doesn't have a built-in means to determine the median of a set of numbers. You can write your own procedure to find the median value, but using Automation to access the Median method of the Microsoft Excel Application object is easier. The following procedure uses the Microsoft Excel Median method to determine the median for a set of data passed as an array.

Function GetMedian(avarItemArray As Variant) As Variant
   ' Create new instance of Microsoft Excel Application class.
   Dim appXL As New Excel.Application
   ' Pass array to Microsoft Excel's Median function.
   GetMedian = appXL.Application.Median(avarItemArray)
   ' Close Excel.
   appXL.Quit
End Function

Note   The Microsoft Excel Median method is also a function in Microsoft Excel. The method and the function are identical, but the method is used with the Application object, while the function stands by itself. Functions that belong to a component are often available as methods of an object.

Anticipating Messages in Automation Code

When you write Automation code, you should avoid actions that cause a component to display a dialog box, such as a message box. Once a dialog box is open, the code stops running until the user closes the dialog box. If the component isn't visible to the user, the user won't be able to close the dialog box. The best solution is to avoid writing code that displays a dialog box.

Some components post warning messages to the user before certain operations take place. For example, Microsoft Access warns the user before deleting a record in a table. When a component posts a warning message, execution halts until the user closes the warning message.

The posted warning message presents problems if the component isn't visible to the user or if the user doesn't know what to do when the warning is posted. In some components, you can solve this problem in part by turning off all messages posted by the component. For example, in Microsoft Access you can use the SetWarnings method of the DoCmd object to turn off messages. This method turns off all messages except error messages. To determine whether a particular component offers a similar method, check the component's documentation.

Handling Errors in Automation Code

You must also carefully consider any errors that may occur when your Automation code is running and try to prevent them. For example, if a user must input a value that lies within a certain range, try to write the procedure so that it tests the value without generating an error. If an error does occur, Visual Basic posts an error message and halts the running code, unless you've included error-handling routines. By using an error-handling routine, you can prevent the error message from being displayed and correct the error. However, depending on the error, an error-handling routine can sometimes generate unpredictable results. If possible, it's best to avoid generating an error in the first place.

When necessary, you can write error-handling routines to handle errors. However, you should try to avoid writing code that deliberately generates errors that you then handle with error-handling routines. For example, if you are creating a new TableDef object in code, you may typically assume that no other object exists with the same name, and write an error-handling routine to handle the case in which the name given to the new object is not unique. In most situations this is acceptable coding practice, but you may get unpredictable results during Automation operations. Rather than generating an error, you should test to determine whether the object exists before you try to create it. Once you've created an application that calls Automation code, you should test the application thoroughly.

See Also   For more information on error handling, see Chapter 8, "Handling Run-Time Errors."

Closing an Object

Objects use memory and system resources while they're active. When you've finished working with an object, you should close the object by using the method supported by that particular object. Most objects support a Close method or a Quit method that you can use to close the object and free the resources it has been using. You can also use the Nothing keyword with the Set statement to free the memory that's being used by the object variable associated with an object. Once you set an object variable to the Nothing keyword, it no longer points to an object and no longer uses system resources.

The following example shows different ways to free the memory associated with an object variable:

Sub UseExcel()
   ' Create a new instance of the Excel Application class.
   Dim appXL As New Excel.Application
   ' Declare other object variables.
   Dim wbk As Excel.Workbook, wks As Excel.Worksheet
   Set wbk = appXL.Workbooks.Add
   appXL.Visible = True
      .
      . ' Perform an Automation operation.
      .
   ' Close objects and component and free memory.
   wbk.Close
   appXL.Quit
   Set appXL = Nothing
End Sub

System resources are also freed when an object variable that points to an object goes out of scope. However, it's a good idea to close an object or set it to the Nothing keyword to guarantee that resources are freed when you need them, and to make your code self-documenting.

If you are working with a component's Application object, the component may not actually close when you set the object variable to the Nothing keyword, even though the memory used by the object variable has been freed. In this case, you must explicitly use the Application object's Quit method to close the component before you set the object variable to the Nothing keyword.

Using Microsoft Access Objects Through Automation

So far, you have seen how to work with other components' objects from Microsoft Access. You can also work with Microsoft Access objects from other components. For example, you can use Automation to create a Microsoft Access report from a Microsoft Excel spreadsheet.

The following example uses Automation from Microsoft Excel to create a linked table in a Microsoft Access database and then create a Microsoft Access report based on the data in the linked table. To use this example, you need to create a Microsoft Excel workbook named Revenue.xls, add some data to a worksheet in that workbook, and create a named range called DataRange that includes this data. Then, enter the following code in the Declarations section of a module in the Microsoft Excel workbook.

Important   Before you run this code, make sure that the Microsoft Excel ISAM driver (MSEXCL35.dll) is installed on your system. The Microsoft Excel ISAM driver enables Microsoft Excel files to work with the Microsoft Jet database engine. For more information on working with the Microsoft Excel ISAM driver, search the Help index for "Microsoft Excel driver."

' Enter in Declarations section of a module.
Dim appAccess As New Access.Application
Sub PrintReport()
   Dim rpt As Access.Report, ctl As Access.Control
   Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
   Dim strDB As String, intLeft As Integer
   ' Set this constant to the path to your Northwind sample database.
   Const conFilePath As String = "C:\Program Files\Microsoft Office\Office\Samples\"
      
   ' Open database in Microsoft Access, specifying full path name.
   appAccess.OpenCurrentDatabase conFilePath & "Northwind.mdb"
   ' Return reference to current database.
   Set dbs = appAccess.CurrentDb
   ' Create new TableDef object.
   Set tdf = dbs.CreateTableDef("XLData")
   ' Specify connect string for Microsoft Excel ISAM driver.
   tdf.Connect = "EXCEL 8.0; Database=C:\My Documents\Revenue.xls"
   ' Specify source table as a named range in a worksheet.
   tdf.SourceTableName = "DataRange"
   ' Append new linked table to database.
   dbs.TableDefs.Append tdf
   ' Create new report in Microsoft Access.
   Set rpt = appAccess.CreateReport
   ' Specify linked table as report's record source.
   rpt.RecordSource = tdf.Name
   
   ' Create control on report for each field in linked table.
   For Each fld In tdf.fields
      Set ctl = appAccess.CreateReportControl(rpt.Name, acTextBox, , , _
         fld.Name, intLeft)
      intLeft = intLeft + ctl.Width
   Next fld
   ' Open report in Print Preview.
   appAccess.DoCmd.OpenReport rpt.Name, acViewPreview
   ' Restore report.
   appAccess.DoCmd.Restore
   ' Display Microsoft Access as active application.
   AppActivate "Microsoft Access"
End Sub

© 1996 Microsoft Corporation. All rights reserved.