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 itself—you 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.
To use Automation in Microsoft Access, perform these steps, which are explained in greater detail in the sections that follow:
Any component that supports Automation supplies information about its objects in an object library. The object library contains classes that define individual objects and collections of objects. An object’s class includes its name and information about its properties and methods—the 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, or by pressing F2, when you have a module open.
Û To set a reference to a component’s object library
Microsoft Access displays the References dialog box.
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, .tlb, .dll, or .exe 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.”
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. Late-bound component objects cannot be viewed using the Object Browser.
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 code—before 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.”
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 instance of the 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 application itself and contains all other objects and collections from the application’s type library. The Application object is always recognized by the operating system. When you create a new instance of a component’s Application class, you can then access all of the other objects in the component’s object model.
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.
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.
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 codeis 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 Object.
Dim bnd As Object
' 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.
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 existing 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.
| Component | class argument | Object returned |
| Microsoft Access |
|
Microsoft Access Application object |
| Microsoft Excel |
|
Microsoft Excel Application object |
|
Microsoft Excel Workbook object | |
|
Microsoft Excel Chart object | |
| Microsoft Word |
|
Microsoft Word Application object |
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.
| GetObject function example | Result |
|
Returns a reference to an existing instance of the Microsoft Excel Application class. Generates an error if Microsoft Excel is not already running. |
|
Creates a new hidden instance of the Microsoft Excel Application class and returns a reference to that instance. |
|
Creates a new hidden instance of the Microsoft Excel Application class and returns a reference to a Workbook object. |
|
Generates an error whether or not Microsoft Excel is running because the object type argument is different than the declared data type of the wbk object variable. |
|
Creates a hidden instance of the Microsoft Excel Application class and returns a reference to the Workbook object representing Revenue.xls. |
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.
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 WorksheetFunction 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 in the variable avarItemArray.
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.WorksheetFunction.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 WorksheetFunction object, while the function stands by itself. Functions that belong to a component are often available as methods of an object.
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.
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.
You must also carefully consider any errors that may occur when your Automation code is running and try to prevent them or handle them gracefully. 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.
See Also For more information on error handling, see Chapter 8, “Handling Run-Time Errors.”
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 should 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
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 the object is no longer needed, 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.
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.” Also, make sure that the Northwind database Startup form is configured not to open when you open the Northwind sample database.
' 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