Previous Page TOC Index Next Page Home


25

OLE: Communicating with Other Applications

OLE Automation Explained

Windows users have come to expect seamless integration between products. They are not concerned with what product you use to develop their application; they just want to accomplish their tasks. Often Microsoft Word, Microsoft Excel, or some other product is best suited for a particular task that your application must complete. It is your responsibility to pick the best tool for the job. This means that you must know how to communicate from your application directly to that tool.

All of this means that you can no longer learn only about the product and language that you select as your development tool. Instead, you must learn about all the other available applications. Furthermore, you must learn how to communicate with these applications—a challenging but exciting feat.

OLE (Object Linking and Embedding) Automation is the capability of one application to control another application's objects. This means that your Access application can launch Excel, create or modify a spreadsheet, and print it, all without the user having to directly interact with the Excel application. Many people confuse OLE Automation with the process of linking and embedding. OLE 1.0 provided you with the ability to create compound documents, meaning that you could embed an Excel spreadsheet in a Word document or link to the Excel spreadsheet from within a Word document. This capability was exciting at the time and is still is quite useful in many situations, but OLE 2.0 (in addition to everything that OLE 1.0 provides) introduces the capability for one application to actually control another application's objects. This is what OLE Automation is all about.

Just as you can control other applications using OLE Automation, your Access application can be controlled by other applications such as Excel or a Visual Basic application. This means that you can take advantage of Access's marvelous report writer from your Visual Basic application. In fact, you can list all the Access reports, allow your user to select one, and then run the report, all from within a Visual Basic form.

OLE Terms

Before you learn how OLE Automation works, you need to understand a few OLE terms. OLE Automation requires an OLE client and an OLE server. The OLE client application is the one that is doing the talking. It is the application that is controlling the server application. Because this book is about Access, most of the examples in this chapter show Access as an OLE client, meaning that the Access application is controlling the other application (Excel, Word, and so on). The OLE server application is the application being controlled. It contains the objects that are being manipulated. Excel is acting as an OLE server when Access launches Excel, makes it visible, creates a new worksheet, sends the results of a query to the worksheet, and graphs the spreadsheet data. It is Excel's objects that are being controlled, Excel's properties that are being changed, and Excel's methods that are being executed.

Another important component of OLE Automation is a Type Library. A Type Library is a database that lists the objects, properties, methods, and events exposed by an OLE server application. Type Libraries allow the server application's objects, properties, and methods to be syntax-checked by the Access compiler. Furthermore, using a Type Library, you can get help on another application's objects, properties, and methods from within Access.

An Object Model of an OLE server application contains the set of objects that are exposed to OLE client applications. The objects within the Object Model are called object types. When you write OLE Automation code, you create and manipulate instances of an object type. These instances are called objects.


It is important to be aware of the hardware that OLE Automation requires. It is common for a developer using a Pentium with 32M of RAM to create a really slick application only to find that it won't run on the 12M 486s owned by the users. OLE Automation craves RAM. The more, the better! I recommend 24M of RAM or more for applications that use OLE. It is also important to recognize that OLE Automation is not fast, even on the slickest of machines.

Declaring an Object Variable to Reference the Application You Are Automating

OLE Automation requires that you create object variables that reference application objects. After you have created an object variable, you can query and change the object's properties as well as execute its methods.

You can learn about an object's properties and methods using its Object Libraries. An Object Library contains a listing of all the properties and methods that an object exposes. To be able to view foreign objects from within Access, you must first establish a reference to that application. When a reference has been established, you can view that object's properties and methods using the Object Browser. You can also view any modules and classes that the parent object exposes.

To register an object, the code window must be visible. With the code window visible, select Tools|References. The References dialog appears (see Figure 25.1).


Figure 25.1. The References dialog.

Each time you install a program, the Windows Registry is updated. The References dialog shows you all the objects that are registered in Windows (see Figure 25.2). If you want to link to one of the available objects from within Access, you must mark the check box to the left of the object name. Select OK. You can browse that object's properties and methods in the Object Browser. The Object Browser is shown in Figure 25.3. As covered in Chapter 10, "The Real Scoop on Objects, Properties, and Events," to access the Object Browser, select Tools|Object Browser, press F2, or click the Object Browser tool while in the Module window. Notice that in Figure 25.3 the Object Browser is displaying all the modules and classes that belong to the PowerPoint 7.0 Object Library. The Presentation class is selected. All of the methods and properties that apply to the Presentation class are displayed in the rightmost list box. The SaveAs method is selected. Notice that the bottom half of the Object Browser shows all the arguments associated with the SaveAs method of the Presentation class.


Figure 25.2. Registered OLE server objects.


Figure 25.3. The Object Browser.

CreateObject and GetObject

Before you can talk to an application, you need to know the objects contained within it. You can then use Dim, Private, or Public statements to point to and control various application objects. Each product comes with documentation indicating which objects it supports. You can also view the objects that a product supports using the Object Browser. When you have created an object variable, you can manipulate the object without user intervention.

CreateObject

To create an instance of an object, you must first create a generic object variable that holds a reference to the object. This is accomplished with a Dim statement:

Dim objExcel As Object

The CreateObject function can then be used to assign an OLE server object to the object variable. The CreateObject function receives the class name for an application object as its parameter. This is the name that the Windows Registry uses to reference the object. Here's an example:

Set objExcel = CreatObject("Excel.Application")

This code creates an object variable pointing to the Excel application object. A new instance of the Excel application is started automatically. This Excel object is part of the Excel application. It can be controlled by VBA using the object variable. Unless instructed otherwise, the instance of Excel will be invisible. It can be made visible with the following statement:

objExcel.Visible = True

GetObject

The CreateObject function creates a new instance of the specified application, and the GetObject function points an object variable at an existing object. If the object does not exist, an error results. The GetObject function receives two parameters. The first is the full path to a file, and the second is the name of the application class. Here's an example:

objExcel = GetObject(,"Excel.Application")

This code points an existing occurrence of Excel to the objExcel object variable. If no instances of Excel are found, an error results. Because you did not specify a path name, the instance of Excel does not point at a specific file.

Certain applications register themselves as single-instance objects. This means that no matter how many times the CreateObject function is run, only one instance of the object is created. Microsoft Word is an example of a single-instance object. On the other hand, if the CreateObject function is used to launch Microsoft Excel, several instances of the application are created. The following code addresses this problem:

Sub LaunchExcel()
    On Error Resume Next
    'Sets Error Handling to Resume on the Line Following the Error
    Dim objExcel As Object   'Create Generic Object Variable
    'Attempt to Point an Occurrence of Excel to the Object Variable
    Set objExcel = GetObject(, "Excel.Application")
    If Err.Number Then   'Test to See if an Error Occurred
        'If an Error Occurs, Use CreateObject to Create an Instance of Excel
        Set objExcel = CreateObject("Excel.Application")
    End If
    objExcel.Visible = True
End Sub

This subroutine creates a generic object variable called objExcel. It uses the GetObject function to try to point the objExcel variable to an existing copy of Excel. If an error occurs, you know that Excel was not running. The CreateObject function is then used to create a new instance of Excel. Finally, the Excel object is made visible. This code ensures that only one copy of Excel will be launched. Only if the GetObject function returns an error do you use CreateObject to launch Excel.


It is important that you are cognizant of which objects register themselves as single-instance objects and which register themselves as multi-instance objects. Steps must be taken with multi-instance objects to ensure that you do not accidentally launch several instances of the application.

Manipulating an OLE Object

After you have created an instance of an object, you are ready to set its properties and execute its methods. You can talk to the object through the object variable you created. Using this object variable, you can get and set properties and execute methods.

Setting and Retrieving Properties

The objects you will be talking to through OLE Automation all have properties. Properties are the attributes of the object—the adjectives you use to describe the objects. You can use VBA to inquire about the properties of objects and set the values of these properties. Here are some examples:

objExcel.Visible = True
objExcel.Caption = "Hello World"
objExcel.Cells(1, 1).Value = "Here I Am"

Each of these examples sets properties of the Excel application object. The first example sets the Visible property of the object to True. The second example sets the Caption of the object to "Hello World". The final example sets the Value property of the Cells object, contained within the Excel object, to the value "Here I Am".

Executing Methods

Properties refer to the attributes of an object, and methods refer to the actions that you can take upon the object. Methods are the verbs that apply to a particular object type. Here's an example:

objExcel.Workbooks.Add

This code uses the Add method to add a workbook to the Excel object.

Controlling Excel from Access

Before you attempt to talk to Excel, you must understand its Object Model. Excel help provides you with an excellent overview of the Excel Object Model. This model can be found by searching for "Object Model" in Excel Help. Each object in the model has hypertext links that enable you to obtain specific help on the object, its properties, and its methods.

When you launch Excel, it launches as a hidden window with a Visible property of False. Furthermore, destroying the Excel object variable does not cause Excel to terminate. To make things even more complicated, each time you use the CreateObject function to launch Excel, a new instance of Excel is launched. This means that it is possible for numerous hidden copies of Excel to be running on a user's machine, which can lead to serious resource problems. Therefore, you need to take several precautions when you want to communicate with Excel.

To begin, you must determine whether Excel is running before attempting to launch a new instance. If Excel is already running, you do not want to launch another copy of Excel, and you do not want to exit Excel when you are done working with it. If your application loads Excel, you will close it when you are done. The following subroutine launches Excel. As discussed earlier in the "GetObject" section, the GetObject function is used to attempt to point to an existing copy of Excel. If an error occurs, the CreateObject function is used to point the object variable to a new instance of Excel. If the error occurs, the Public variable gobjExcel is set to False, indicating that you are sure that Excel was not running before your application loaded it. This variable is used in the cleanup routine to determine whether the application exits Excel.

Function CreateExcelObj()
    On Error Resume Next
    'Sets Error Handling to Resume on the Line Following the Error
    CreateExcelObj = False
    'Attempt to Point an Occurrence of Excel to the Object Variable
    Set gobjExcel = GetObject(, "Excel.Application")
    If Err.Number Then   'Test to See if an Error Occurred
        'If an Error Occurs, Use CreateObject to Create an Instance of Excel
        Set gobjExcel = CreateObject("Excel.Application")
        If gobjExcel Is Nothing Then
            gbExcelRunning = False
            CreateExcelObj = True
            MsgBox "Could Not Create Excel Object"
        Else
            gbExcelRunning = False
            CreateExcelObj = True
        End If
    Else
        gbExcelRunning = True
        CreateExcelObj = True
    End If
    Exit Function
End Function

This code, and all the code in this chapter, can be found within the database called CHAP25EX.MDB located on your sample code CD. This routine is located within basUtils.

The CreatExcelObj function is called from the Click event of cmdFillExcel. Only if the return value of the function is True, indicating that Excel was loaded successfully, does the application proceed in attempting to talk to the Excel object.

Private Sub cmdFillExcel_Click()
   gbExcelRunning = True
   If CreateExcelObj() Then
      Call FillCells
   End If
End Sub

If Excel launches successfully, the FillCells subroutine executes. This routine appears as follows:

Sub FillCells()
   Dim oWS As Object
   gobjExcel.Workbooks.Add
   Set oWS = gobjExcel.ActiveSheet
   oWS.Cells(1, 1).Value = "Schedule"
   oWS.Cells(2, 1).Value = "Day"
   oWS.Cells(2, 2).Value = "Tasks"
   oWS.Cells(3, 1).Value = 1
   oWS.Cells(4, 1).Value = 2
   gobjExcel.Range("A3:A4").SELECT
   gobjExcel.Selection.AutoFill gobjExcel.Range("A3:A33")
   gobjExcel.Range("A1").SELECT
   gobjExcel.Visible = True
End Sub

This relatively simple routine is found in frmSimpleExcel (see Figure 25.4), which is part of the CHAP25EX.MDB database file. It begins by using the Add method on the Workbooks collection of the Excel object to add a new workbook to the instance of Excel. It then uses Set oWS = poExcel.ActiveSheet to provide a shortcut for talking to the active sheet in the new Excel workbook. Using the oWS object reference, the values of several cells are modified. The AutoFill method is employed to quickly fill a range of cells with data. The cursor is returned to cell A1, and the Excel object is made visible. You might wonder what the AutoFill method is. The AutoFill method automates the process of filling a range of cells with a pattern of data. The results are shown in Figure 25.5. I mention it here not just to tell you what it is, but also to illustrate an important point: You must know the product you are automating and what it is capable of. If you are not familiar with the product from a user's perspective, you will find it extremely difficult to work with the product using OLE Automation.


Figure 25.4. The form used to launch, communicate with, and close Excel.


Figure 25.5. Using the AutoFill method to populate a range of cells.

Closing an OLE Automation Object

When the user clicks the CloseExcel command button, the CloseExcel subroutine is called. The CreateExcelObj routine determined whether the user was running Excel prior to launching your application. When the CloseExcel routine runs, it prompts the user to close Excel only if the Public variable gbExcelRunning indicates that Excel was not running prior to your application. Otherwise, it prompts the user, warning that he or she must personally close Excel.

Sub CloseExcel()
    On Error GoTo CloseExcel_Err
    Dim intAnswer As Integer
    Dim objWK As Object
    'Attempt to point to an active workbook
    Set objWK = gobjExcel.ActiveWorkbook
    'If Excel is Still Running and was NOT running before
    'this application excuted it, prompt user to close
    If Not gbExcelRunning Then
        intAnswer = MsgBox("Do You Want to Close Excel?", vbYesNo)
        If vbYes Then
            objWK.Close False
            gobjExcel.Quit
        End If
    Else
        MsgBox "Excel Was Running Prior to This Application." & Chr(13) _
             & "Please Close Excel Yourself."
        gobjExcel.Visible = True
    End If
CloseExcel_Exit:
    Set gobjExcel = Nothing
    Set objWK = Nothing
    Exit Sub
CloseExcel_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume CloseExcel_Exit
End Sub

Creating a Graph from Access

Now that you have learned how to talk to Excel, you are ready to learn how to do something a little bit more practical. Figure 25.6 shows a form called frmCreateExcelGraph. The form shows the result of a query that groups the result of price multiplied by quantity for each country. The Create Excel Graph command button sends the result of the query to Excel and produces the graph pictured in Figure 25.7.


Figure 25.6. The form used to create an Excel graph.


Figure 25.7. The result of a query graphed in Excel.

The code looks like this:

Private Sub cmdCreateGraph_Click()
   On Error GoTo cmdCreateGraph_Err
   Dim db As DATABASE
   Dim rst As Recordset
   Dim fld As Field
   Dim objWS As Object
   Dim intRowCount As Integer
   Dim intColCount As Integer
   'Display Hourglass
   DoCmd.Hourglass True
   Set db = CurrentDb
   'Attempt to create Recordset and launch Excel
   If CreateRecordset(db, rst, "qrySalesByCountry") Then
      If CreateExcelObj() Then
         gobjExcel.Workbooks.Add
         Set objWS = gobjExcel.ActiveSheet
         intRowCount = 1
         intColCount = 1
         'Loop though Fields collection using field names
         'as column headings
         For Each fld In rst.Fields
            If fld.Type <> dbLongBinary Then
               objWS.Cells(1, intColCount).Value = fld.Name
               intColCount = intColCount + 1
            End If
         Next fld
         'Loop though recordset, placing values in Excel
         Do Until rst.EOF
            intColCount = 1
            intRowCount = intRowCount + 1
            For Each fld In rst.Fields
               If fld.Type <> dbLongBinary Then
                  objWS.Cells(intRowCount, intColCount).Value = fld.Value
                  intColCount = intColCount + 1
               End If
            Next fld
            rst.MoveNext
         Loop
         gobjExcel.Columns("A:B").SELECT
         gobjExcel.Columns("A:B").EntireColumn.AutoFit
         gobjExcel.Range("A1").SELECT
         gobjExcel.ActiveCell.CurrentRegion.SELECT
         'Add a Chart Object
         gobjExcel.ActiveSheet.ChartObjects.Add(135.75, 14.25, 607.75, 301).SELECT
         'Run the Chart Wizard
         gobjExcel.ActiveChart.ChartWizard Source:=Range("A1:B22"), _
         Gallery:=xlColumn, _
         Format:=6, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels _
         :=1, HasLegend:=1, Title:="Sales By Country", CategoryTitle _
         :="", ValueTitle:="", ExtraTitle:=""
         'Make Excel Visible
         gobjExcel.Visible = True
      Else
         MsgBox "Excel Not Successfully Launched"
      End If
   Else
      MsgBox "Too Many Records to Send to Excel"
   End If
   DoCmd.Hourglass False
cmdCreateGraph_Exit:
   Set db = Nothing
   Set rst = Nothing
   Set fld = Nothing
   Set objWS = Nothing
   DoCmd.Hourglass False
   Exit Sub
cmdCreateGraph_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume cmdCreateGraph_Exit
End Sub

The routine begins by creating several object variables. It then points the db object variable to the current database. It calls a user-defined function called CreateRecordset. The CreateRecordset function receives three parameters: the database object variable, a recordset object variable, and the name of a query. The CreateRecordset function looks like this:

Function CreateRecordset(dbAny As DATABASE, rstAny As Recordset, _
strTableName As String)
   Dim rstCount As Recordset
   On Error GoTo CreateRecordset_Err
   'Create recordset that contains count of records in query
   Set rstCount = dbAny.OpenRecordset("Select Count(*) As NumRecords _
   from " & strTableName)
   'If more than 500 records in query result, return false
   'Otherwise, create recordset from query
   If rstCount!NumRecords > 500 Then
      CreateRecordset = False
   Else
      Set rstAny = dbAny.OpenRecordset(strTableName, dbOpenDynaset)
      CreateRecordset = True
   End If
CreateRecordset_Exit:
   Set rstCount = Nothing
   Exit Function
CreateRecordset_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume CreateRecordset_Exit
End Function

This function begins by counting how many records are returned by the query name that it is passed. If the number of records exceeds 500, the function returns a False; otherwise, the function opens a recordset based on the query name that it is passed and returns a True. In summary, the purpose of this function is to ensure that only a reasonable number of records are sent to Excel and that a recordset can be successfully opened.

If the CreateRecordset function returns a True, the remainder of the code in the Click event of the cmdCreateGraph command button is executed. The routine uses the CreateExcelObj function to launch Excel. If Excel is opened successfully, a new workbook is created. The routine then loops through each field in the Fields collection of the recordset (the result of the query). The values of the cells in the first row of the worksheet are set equal to the names of the fields in the recordset. Next, the routine loops through each record in the recordset. The data from each row in the recordset is placed in a different row within the spreadsheet. The data from each column in a particular row is placed in the various columns of the worksheet. OLE fields (dbLongBinary) are excluded from the process.

When all the data in the recordset has been sent to Excel, the routine is ready to create a chart. It moves the cursor to cell A1 and then selects the entire contiguous range of data. It adds a chart object to the worksheet and then uses the Chart Wizard to create a chart. Finally, Excel is made visible so that users can see the fruits of their efforts.

Controlling Word from Access

As you discovered in the previous section, Excel exposes many objects. Each of these objects can be manipulated separately, using its own properties and methods. Microsoft Word is definitely a different animal; it exposes only one object, called Word.Basic. This object exposes the Word Basic compiler and the more than 800 Word Basic functions that are available to the compiler. By executing methods of the Word.Basic object, you can execute most of Word's commands and functions.

Unlike Excel, Word is a single-instance object, meaning that you can use the CreateObject function as many times as you want without being concerned that multiple instances of Word will be launched. Like Excel, Word launches as a hidden object. Word does not have a Visible property. Instead, you must use its AppShow method to make it visible. If you create a Word object using OLE Automation, Word terminates as soon as the object variable is destroyed.

Figure 25.8 shows the form called frmMergeToWord, which shows the results of running a query called qryMailMerge. After the user clicks the Merge to Word command button, all the records that are displayed are sent to a Word mail merge and printed. Figure 25.9 shows an example of the resulting document.


Figure 25.8. The data that will be merged to Word.


Figure 25.9. The result of the mail merge.

The code looks like this:

Private Sub cmdMergeToWord_Click()
   On Error GoTo cmdMergeToWord_Err
   DoCmd.Hourglass True
   Set mobjWord = CreateObject("Word.Basic")
   mobjWord.FileOpen "c:\databases\customerletter.doc"
   mobjWord.MailMergeToDoc
   mobjWord.FilePrint   'Preview
   mobjWord.AppShow
   DoCmd.Hourglass False
cmdMergeToWord_Exit:
   DoCmd.Hourglass False
   Exit Sub
cmdMergeToWord_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Set mobjWord = Nothing
   Resume cmdMergeToWord_Exit
End Sub

The code begins by presenting an hourglass mouse pointer to the user. This ensures that if the process takes awhile, the user knows that something is happening. It then creates a Word.Basic object. The FileOpen method is executed on the Word.Basic object. It opens a document called customerletter in the databases directory. The customerletter document has already been set up to do a mail merge with the results of a query called qryMerge. The MailMergeToDoc method is then issued on the Word.Basic object. This merges the results of qryMailMerge and creates a new document with the mail-merged letters. The FilePrint method is executed on the Word.Basic object so that the documents are printed. Finally, the AppShow method of the Word.Basic object makes Word visible, and the hourglass vanishes.

Controlling PowerPoint from Access

Believe it or not, even PowerPoint can be controlled using OLE Automation. You can create a presentation, print a presentation, or even run a slide show directly from Access.

PowerPoint launches as a hidden window. To make PowerPoint visible, you must set the Visible property of the AppWindow to True. Destroying the PowerPoint object variable does not terminate the PowerPoint application. Details of the PowerPoint Object Model can be found on the Microsoft Solutions Development Kit CD. You should review this object model before attempting to communicate with PowerPoint.

The following code is found under the Click event of the cmdChangePicture command button on frmOLEToPowerPoint shown in Figure 25.10. The resulting PowerPoint slide is pictured in Figure 25.11.


Figure 25.10. The form that is used to create a PowerPoint slide.


Figure 25.11. The resulting PowerPoint slide.

Private Sub cmdChangePicture_Click()
   dlgCommon.ShowOpen
   olePicture.SourceDoc = dlgCommon.FileName
   olePicture.Action = acOLECreateLink
End Sub

The code in the Click event of cmdChangePicture invokes the File Open common dialog so that the user can select a picture to be added to the slide. The FileName property returned from the File Open common dialog is used as the SourceDoc property for the OLE object. The new picture is then linked to the OLE object.

The routine that creates the PowerPoint slide looks like this:

Private Sub cmdMakePPTSlide_Click()
   Dim objPresentation As Object
   Dim objSlide As Object
   'Create instance of PowerPoint application
   Set mobjPPT = CreateObject("PowerPoint.Application.7")
   'Make instance visible to user
   mobjPPT.AppWindow.Visible = True
   'Add a Presentation
   Set objPresentation = mobjPPT.Presentations.Add
   'Add a Slide
   Set objSlide = objPresentation.Slides.Add(1, ppLayoutTitleOnly)
   'Change the Slide Background
   objSlide.Background.Fill.ForeColor.RGB = RGB(255, 100, 100)
   'Modify the Slide Title
   With objSlide.Objects.Title
      .Text = txtTitle.Value & ""
      .Text.Font.Color.RGB = RGB(0, 0, 255)
      .Text.Font.Italic = ppTrue
   End With
   'Add the OLE Picture
   objSlide.Objects.AddOleObjectFromFile olePicture.SourceDoc, _
         ppTrue, 2000, 4000, 11000, 5000
cmdMakePPTSlide_Exit:
   Set objPresentation = Nothing
   Set objSlide = Nothing
   Exit Sub
cmdMakePPTSlide_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume cmdMakePPTSlide_Exit
End Sub

The routine begins by creating an instance of PowerPoint. The instance is made visible. A presentation is then added to the PowerPoint object. After the presentation has been added, a slide is added to the presentation. The background fill of the slide is modified. The text, color, and italic properties of the title object are customized. Finally, the SourceDoc property of the olePicture object is used to create an OLE object, which is added to the slide.

Controlling Schedule Plus from Access

Microsoft Schedule Plus is probably one of the more widely used Microsoft applications. It is very useful to be able to integrate Schedule Plus with an Access application. By doing this, you can view and report on Schedule Plus data using Access as your report writer, or you can send data from Access directly to Schedule Plus. In this example, you see how data from an Access form can be used to schedule an appointment in Schedule Plus.


The Schedule Plus Object Model can be found on the Microsoft Solutions Development Kit CD. The CD provides details on all Schedule Plus's objects, properties, and methods, and it offers some great sample code. The Microsoft Solutions Development Kit CD is an invaluable tool when writing OLE Automation code involving any part of Microsoft's application suite.

Schedule Plus launches as a hidden window. To make Schedule Plus visible, you must set the Visible property of a Schedule object to True. Destroying the Schedule Plus object variable does not cause Schedule Plus to terminate. If you want to close Schedule Plus, you need to include code to exit the application.

The form shown in Figure 25.12, called frmOLEToSchedule, allows the user to fill out information that is used to create an appointment in Schedule Plus. Figure 25.13 shows the resulting entry in Schedule Plus. The code looks like this:


Figure 25.12. The form that provides information to Schedule Plus.


Figure 25.13. The resulting appointment in Schedule Plus.

Private Sub cmdAddAppointment_Click()
   On Error GoTo cmdAddAppointment_Err
   Dim objSession As Object
   Dim objApplication As Object
   Dim objSchedule As Object
   Dim objTable As Object
   Dim objItem As Object
   'Create instance of Schedule Plus
   Set objApplication = CreateObject("SchedulePlus.Application")
   'Logon to Schedule Plus
   objApplication.Logon
   'Set Object Variable to Schedule
   Set objSchedule = objApplication.ScheduleLogged
   'Set Object Variable to Appointments Collection
   Set objTable = objSchedule.Appointments
   'Create a New Appointment
   Set objItem = objTable.New
   'Set the Properties of the Appointment
   objItem.SetProperties Text:=Me!txtDescription & "", _
         Notes:=Me!txtNotes & "", _
         BusyType:=CLng(1), _
         Start:=(CVDate(txtDate & " " & txtStart)), _
         End:=(CVDate(txtDate & " " & txtEnd))
   'Make Schedule Visible
   objSchedule.Visible = True
cmdAddAppointment_Exit:
   Set objItem = Nothing
   Set objTable = Nothing
   Set objSchedule = Nothing
   Set objApplication = Nothing
   Set objSession = Nothing
   Exit Sub
cmdAddAppointment_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume cmdAddAppointment_Exit
End Sub

This routine begins by creating an instance of the Schedule Plus application. It then points to the Appointments collection and adds a new appointment to the collection. It sets all the properties of the new appointment and makes the Schedule Plus object visible.

Controlling Access from Other Applications

Many times, you will want to control Access from another application. For example, you might want to run an Access report from a Visual Basic or Excel application. Just as you can tap into many of the rich features of other products such as Excel from within Access, you can utilize some of Access's features from within another program. Fortunately, it is extremely easy to control Access from within other applications.

An overview of the Access object model can be found in Access help. Unless you are very familiar with the Access object model, you should look at this graphical representation of Access's object model before you attempt to use OLE Automation to control Access. Access launches with its Visible property set to False. You can change the Visible property of the application object to True to make Access visible. If the instance of the object was created using OLE Automation, it terminates when its object variable is destroyed.

The form shown in Figure 25.14 is a Visual Basic form. It is called frmReportSelect.frm and is part of a Visual Basic project called AutomateAccess.vbp. The form allows you to select any Access database. It displays a list of all reports in the selected database. After the reports have been listed, it allows you to preview one Access report or print multiple Access reports.


Figure 25.14. The Visual Basic form that allows Access reports to be printed.

The following code shows how this is all accomplished:

Private Sub cmdSelectDB_Click()
    Call LoadReports
End Sub
Sub LoadReports()
    Dim ws As Workspace
    Dim db As Database
    Dim doc As Document
    Dim cnt As Container
    'Point at the Jet Engine
    Set ws = DBEngine(0)
    'Set a Filter and Intial Directory for the
    'Common Dialog Control
    dlgCommon.Filter = "Databases (*.mdb)|*.mdb"
    dlgCommon.InitDir = App.Path
    'Display the File Open Common Dialog
    dlgCommon.ShowOpen
    'Open a Database using the selected Access file
    Set db = ws.OpenDatabase(dlgCommon.filename)
    'Look at the Reports collection
    Set cnt = db.Containers!Reports
    'Clear the List Box of previous entries
    lstReports.Clear
    'Loop through the collection of Reports
    'Add each report name to the List Box
    For Each doc In cnt.Documents
       lstReports.AddItem doc.Name
    Next doc
End Sub

The code begins by creating an instance of the Access application. It uses the OpenDatabase method of the Workspace object to open the Access database selected by the user in the File Open common dialog. It then loops through the Reports collection of the selected database. The name of each report is added to the list box. So far, you have not launched Access. Instead, you have used DAO (data access objects) to get at its objects.

Private Sub cmdPreview_Click()
    Call RunReport
End Sub
Sub RunReport()
    On Error GoTo RunReport_Err
    'Create an Instance of the Access application
    Dim objAccess As New Access.Application
    'Open the selected Database
    objAccess.OpenCurrentDatabase (dlgCommon.filename)
    'Preview the Selected Report
    objAccess.DoCmd.OpenReport lstReports.Text, View:=acPreview
    'Set the Visible property of the Application to True
    objAccess.Visible = True
RunReport_Exit:
    Set objAccess = Nothing
    Exit Sub
RunReport_Err:
    MsgBox Err.Description
    Resume RunReport_Exit
End Sub

This routine creates a new instance of the Access application object. Dim objName As New is another way to create an instance of a registered application object. After the instance is created, the OpenCurrentDatabase method is used to open the selected database. The OpenReport method is used along with the constant acPreview. This causes the selected report to be previewed. Finally, the Access application object is made visible.

The Visual Basic application also gives the user the opportunity to send multiple Access reports to the printer. The code looks like this:

Private Sub cmdRunReport_Click()
    Call PrintReports
End Sub
Sub PrintReports()
    Dim intCounter As Integer
    On Error GoTo PrintReports_Err
    'Create an Instance of the Access Application
    Dim objAccess As New Access.Application
    'Open the Database that was selected in the
    'File Open Common Dialog
    objAccess.OpenCurrentDatabase (dlgCommon.filename)
    'Loop through the List Box
    'Print each report that is selected
    For intCounter = 0 To lstReports.ListCount - 1
        If lstReports.Selected(intCounter) Then
            objAccess.DoCmd.OpenReport lstReports.Text
        End If
    Next intCounter
PrintReport_Exit:
    Set objAccess = Nothing
    Exit Sub
PrintReports_Err:
    MsgBox Err.Description
    Set objAccess = Nothing
    Resume PrintReport_Exit
End Sub

This routine creates an instance of Access and then opens the selected database. It loops through the list box, identifying all the reports that have been selected. It then sends each report to the printer.

Practical Examples: Using OLE Automation to Extend the Functionality of the Time and Billing Application

Many potential applications of OLE Automation exist for the time and billing application. One of them is discussed in this section.

Allowing the Table or Query to Be Sent to Excel

The form in Figure 25.15 allows users to select any table or query that they want to send to Excel. The form is called frmSendToExcel.


Figure 25.15. This form allows selection of a table or query to send to Excel.

The list box on the form is populated with the following Callback function. Notice that the function uses the TableDefs and QueryDefs collections to populate the list box, excluding all the system tables.

Function FillWithTableList(ctl As Control, vntID As Variant, _
        lngRow As Long, lngCol As Long, intCode As Integer) _
        As Variant
   Dim db As DATABASE
   Dim tdf As TableDef
   Dim qdf As QueryDef
   Dim intCounter As Integer
   Static sastrTables() As String
   Static sintNumTables As Integer
   Dim varRetVal As Variant
   varRetVal = Null
   Select Case intCode
      Case acLBInitialize         ' Initialize.
         Set db = CurrentDb
         'Determine the Total Number of Tables + Queries
         sintNumTables = db.TableDefs.Count + db.QueryDefs.Count
         ReDim sastrTables(sintNumTables - 2)
         'Loop through each Table adding its name to
         'the List Box
         For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) <> "MSys" Then
               sastrTables(intCounter) = tdf.Name
               intCounter = intCounter + 1
            End If
         Next tdf
         'Loop through each Query adding its name to
         'the List Box
         For Each qdf In db.QueryDefs
            sastrTables(intCounter) = qdf.Name
            intCounter = intCounter + 1
         Next qdf
         varRetVal = sintNumTables
      Case acLBOpen                 'Open
         varRetVal = Timer          'Generate unique ID for control.
      Case acLBGetRowCount          'Get number of rows.
         varRetVal = sintNumTables
      Case acLBGetColumnCount       'Get number of columns.
         varRetVal = 1
      Case acLBGetColumnWidth       'Get column width.
         varRetVal = -1             '-1 forces use of default width.
      Case acLBGetValue             'Get the data.
         varRetVal = sastrTables(lngRow)
   End Select
   FillWithTableList = varRetVal
End Function

The Click event of the cmdSendToExcel command button sends the selected table or query to Excel. The code looks like this:

Private Sub cmdSendToExcel_Click()
   On Error GoTo cmdSendToExcel_Err
   gbExcelRunning = True
   Dim objWS As Object
   Dim db As DATABASE
   Dim rst As Recordset
   Dim fld As Field
   Dim intColCount As Integer
   Dim intRowCount As Integer
   Set db = CurrentDb
   'Invoke Hourglass
   DoCmd.Hourglass True
   'Try to Create Recordset and Create Excel Object
   If CreateRecordset(db, rst, lstTables.Value) Then
      If CreateExcelObj() Then
         'Add a Workbook
         gobjExcel.Workbooks.Add
         'Create a Shortcut to the Active Sheet
         Set objWS = gobjExcel.ActiveSheet
         intRowCount = 1
         intColCount = 1
         'Loop through the Fields collection
         'Make each field name a column heading in Excel
         For Each fld In rst.Fields
            If fld.Type <> dbLongBinary Then
               objWS.Cells(1, intColCount).Value = fld.Name
               intColCount = intColCount + 1
            End If
         Next fld
         'Send Data from Recordset out to Excel
         Do Until rst.EOF
            intColCount = 1
            intRowCount = intRowCount + 1
            For Each fld In rst.Fields
               If fld.Type <> dbLongBinary Then
                  objWS.Cells(intRowCount, intColCount).Value = fld.Value
                  intColCount = intColCount + 1
               End If
            Next fld
            rst.MoveNext
         Loop
         gobjExcel.Range("A1").SELECT
         'Set up AutoFilter
         gobjExcel.Selection.AutoFilter
         gobjExcel.Visible = True
      Else
         MsgBox "Excel Not Successfully Launched"
      End If
   Else
      MsgBox "Too Many Records to Send to Excel"
   End If
cmdSendToExcel_Exit:
   DoCmd.Hourglass False
   Set objWS = Nothing
   Set db = Nothing
   Set rst = Nothing
   Set fld = Nothing
   Exit Sub
cmdSendToExcel_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume cmdSendToExcel_Exit
End Sub

The routine begins by creating a recordset object using the CreateRecordSet function that follows. It then attempts to launch Excel. If it is successful, it loops through the Fields collection of the recordset resulting from the selected table or query. It lists all the field names as column headings in Excel. Next, it loops through the recordset, adding all the field values to the rows in the Excel worksheet. Finally, it issues the AutoFilter method so that the user can easily manipulate the data in Excel, filtering it as necessary (see Figure 25.16).


Figure 25.16. Use AutoFilter to analyze data sent to Excel.

Function CreateRecordset(dbAny As DATABASE, rstAny As Recordset, _
strTableName As String)
   Dim rstCount As Recordset
   On Error GoTo CreateRecordset_Err
   'Create recordset that contains count of records in query
   Set rstCount = dbAny.OpenRecordset("Select Count(*) As NumRecords from _
   " & strTableName)
   'If more than 500 records in query result, return false
   'Otherwise, create recordset from query
   If rstCount!NumRecords > 500 Then
      CreateRecordset = False
   Else
      Set rstAny = dbAny.OpenRecordset(strTableName, dbOpenDynaset)
      CreateRecordset = True
   End If
CreateRecordset_Exit:
   Set rstCount = Nothing
   Exit Function
CreateRecordset_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume CreateRecordset_Exit
End Function

This routine, found in basOLE, ensures that the recordset is not too large to send to Excel. If the size of the recordset is acceptable, it creates the recordset and returns True.

Summary

OLE Automation enables you to control other applications from your Access application and allows other programs to control your Access application. This chapter began by providing an overview of what OLE Automation is and why you might want to use it. It continued by showing how you can create an object variable to reference the application that you are automating. After the ins and outs of the object variable were explained, the chapter provided numerous examples of manipulating OLE objects. Detailed code was provided, showing OLE Automation involving Excel, Word, PowerPoint, and Schedule Plus. Finally, this chapter showed how you can control Access from other applications.

The ability to communicate with other applications has become a prerequisite for successful software development. It is extremely important to be aware of the rich wealth of tools available. The ability to call upon other applications' features is helping to make the world document-centric rather than application-centric. This means that users can focus on their task and not on how you are getting the task accomplished. Although OLE Automation requires significant hardware and is also rather slow, the benefits that it provides are often well worth the price.

Previous Page TOC Index Next Page Home