Access Basic: OLE Automation

OLE is a standard way that applications use to expose (supply) their objects to external development tools, macro languages, and other applications that support OLE. OLE Automation is a development integration feature that enables you to access and manipulate another application's objects from outside that application. These exposed objects are called OLE components. Different applications can expose different kinds of OLE components. For example, a spreadsheet application may expose a spreadsheet, chart, cell, or range of cells as different types of objects. A word processor may expose objects such as documents, paragraphs, or sentences.

OLE Automation components differ from other OLE objects in that they can only be accessed using a programming or macro language such as Access Basic. OLE Automation objects aren't visible to the user, and are mostly used to automate repetitive tasks or tasks that don't involve user interaction. Since they are created using code, they are temporary and do not remain after the code is executed. For this reason, OLE Automation objects can't be linked or embedded. In comparison, OLE objects created using the Insert Object or Paste Special commands in Microsoft Access are visible to the user as linked or embedded objects in bound or unbound object frames. To update these objects, the user can double-click them to activate the object's application and make changes to the data.

When an object application supports OLE Automation, you can use Access Basic to access the OLE objects it exposes in one of two ways. First, you can invoke methods supported by the OLE objects. Second, you can retrieve and set OLE Automation object properties just as you can retrieve and set Microsoft Access object properties. This enables your Microsoft Access application to automate repetitive tasks, such as application-wide edits or updates. When talking about OLE Automation, controller applications takes the place of containers.

Here are some examples of OLE components that can be automated with the Microsoft Access database management system.

Using the CreateObject Function

You can add an object that isn't embedded in or linked to an unbound object frame by using the CreateObject function. This enables you to create objects that aren't visible to Microsoft Access users (for example, to perform automated tasks without displaying output to the user). Similarly, you can also retrieve and activate an object from a file by using the GetObject function.

You can manipulate other applications' OLE objects without creating a linked or embedded OLE object by using the CreateObject function. Since this type of OLE object isn't embedded or linked, it can be used for performing actions that don't require user interaction.

For example, to print a Word document without having to activate Word, you can place a Print button on your form and use Access Basic to create a Word OLE Automation object, activate it, and then print the document.

Before creating an OLE object with the CreateObject function, you define a variable that you can use to reference the object. You do this by declaring a variable of type Object. For example:

Dim MyObj As Object

You then use the CreateObject function to create the OLE object. This function requires a single-string argument that indicates the object application name and the type of object you want to create. The combination of these two elements is the object's class. Use the following syntax to specify an object's class:

"appname.objecttype"

For example, when Microsoft Word exposes a WordBasic object and Microsoft Excel exposes a Worksheet object, these objects are specified as:

Word.Basic
Excel.Sheet.5

There are two ways to specify OLE object names. You can either append the version number of the object application after the OLE object name, or leave the version number off. An object name with a version number appended, as in Excel.Sheet.5, specifies an object created with the specified version of the application. When you leave off the version number, as in Excel.Sheet, you specify an object created by the most recent version of the application.

Once you know the type of object you want to create, you use a Set statement to assign the object returned by the CreateObject function to the object variable. For example:

Dim Sheet as Object
Set Sheet = CreateObject ("Excel.Sheet")

To delete an OLE Automation object and close the object's application, use the Nothing reserved word with the Set statement.

Set Sheet = Nothing

When this code is executed, Microsoft Access starts the object application automatically, if it isn't already running, and creates an OLE object. Unlike the image that is displayed when you create a linked or embedded object with an unbound object frame, the object's image isn't displayed anywhere in Microsoft Access, nor is the object's data updated by Microsoft Access. The object is part of the Microsoft application that creates it. This object can be referred to in Microsoft Access code using the object variable you defined. For example, you can write code such as the following to perform some simple actions in a Word document and then save the document:

Dim WordObj As Object
Dim Count As Integer
    Set WordObj = CreateObject ("Word.Basic")
    WordObj.Fileopen "C:\WINWORD\TEST.DOC"

    ' Select and do text formatting.
    WordObj.ParaUp 1
    WordObj.ParaDown 1, 1
    WordObj.Bold 1
    WordObj.EditSelectAll
    WordObj.FontSize 10
    ' Get the value from a control and insert it into the document.
    GetCategory = Forms![MyForm]![Category Name]
    WordObj.EndOfDocument            'Move the insertion point
    WordObj.Insert GetCategory
    WordObj.InsertPara                ' Insert a carriage return.

    ' Save the changes and close the document.
    WordObj.FileClose (1)

You can create a Microsoft Excel OLE Automation object, such as a Worksheet object, and access its methods and properties in Access Basic. For example, the following code creates a worksheet called Budget, inserts a text file and a value from a Microsoft Access database, saves the worksheet, and closes the application.

Sub Check (FromMicrosoft Access As Integer)
    Dim Sheet As Object

    ' Create the object.
    Set Sheet = CreateObject("Excel.Sheet")

    ' Insert value from a Microsoft Access database into a cell.
    Sheet.Cells (1,1).Value = FromMicrosoft Access

    ' Save the worksheet and close Microsoft Excel.
    Sheet.SaveAs("MySheet.XLS")
    Set Sheet = Nothing        ' Setting the variable to Nothing also
                            ' closes the application.
End Sub

Manipulating Objects

Once you've created a variable that references an OLE object, you can manipulate the object in Microsoft Access in the same way you manipulate any Microsoft Access object, such as a control. You use the object.property syntax to set and retrieve the object's properties and to perform methods on the object.

To assign a value to an object's property, put the object variable and property name on the left side of an equation and the property setting on the right side. For example:

Dim Sheet As Object

    Set Sheet = CreateObject ("Excel.Sheet")
    Sheet.Name = "Sheet1"

You can also retrieve property setting values from an object by assigning the object variable and property name to a variable:

Dim X As Variant
X = Sheet.Name

In addition to setting and retrieving properties, you can manipulate an object using the methods it supports. Some methods may return a value. In WordBasic, however, you manipulate objects using WordBasic functions and statements. For example, in the following WordBasic macro, the Bold function returns True or False depending on whether the selected text in the WordBasic object is bold.

' Bold function returns True or False.
Dim IsBold As Variant
Dim WordObj as Object

    Set WordObj = CreateObject ("Word.Basic")
    WordObj.FileOpen "C:\WINWORD\LETTER.DOC"
    IsBold = WordObj.Bold ()
    If IsBold Then
        MsgBox "The text is bold."
    Else
        MsgBox "The text is not bold."
    End If

Access Basic: OLE Controls

In addition to OLE Automation, Microsoft Access supports the use of OLE Controls to extend its development environment. OLE Controls can be thought of as "plug-in" components that perform specific tasks within a Microsoft Access application. Examples include things like calendars, communication controls, multimedia controls, etc. An OLE Control is an component, which can be embedded, with in-place activation capabilities. Every OLE Control has three sets of attributes:

Properties. Named characteristics or values of the control such as color, text, number, font, etc.

Events. Actions triggered by the control in response to some other external actions on the control, such as clicking a mouse button or pressing a key.

Methods. A function implemented in the control that allows external code to manipulate its appearance, behavior or properties. For example, an edit or other text-oriented control would support methods to allow the control container to retrieve or modify the current text, perhaps performing operations such as copy and paste.

The syntax for manipulating an OLE Control within Access Basic is identical to that of OLE Automation with the exception of CreateObject() and GetObject(). The properties and methods of a control are referred to as follows:

ControlName.Object.Property

ControlName.Object.Method