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 this means is 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.

ActiveX 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 automation with the process of linking and embedding. OLE 1.0 gave you the capability to create compound documents, meaning that you can 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 still is quite useful in many situations, but OLE 2.0 (in addition to everything that OLE 1.0 provides) introduced the capability for one application to actually control another application’s objects. With Office 97, Microsoft changed the way users refer to OLE. It became known as automation and is an industry standard and a feature of the Component Object Model (COM).

Just as you can control other applications using automation, you can control the Access application with 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.

Before you learn how automation works, you need to understand a few automation terms. Automation requires an automation client and an automation server. The automation 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 automation client, meaning that the Access application is controlling the other application (Excel, Word, and so on). The automation server application is the application being controlled. It contains the objects being manipulated. Excel is acting as an automation 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 automation is a type library, this is, a dictionary that lists the objects, properties, methods, and events exposed by an automation server application. Type libraries allow the server application’s objects, properties, and methods to be syntax checked by the Access compiler. You also can use a type library to get help on another application’s objects, properties, and methods from within Access.

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

Automation requires that you create object variables that reference application objects. After you create 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 first must establish a reference to that application. After a reference is established, you can view that object’s properties and methods using the Object Browser. You also can view any modules and classes that the parent object exposes.

To register an object, the Visual Basic Editor (VBE) must be active. With the code window active, choose Tools|References. The References dialog box appears, as shown in Figure 22.1.

Figure 22.1. The References dialog box.

Each time you install a program, the Windows registry is updated. The References dialog box shows you all the objects registered in Windows. (See Figure 22.2.) If you want to link to one of the available objects from within Access, you must enable the check box to the left of the object name. Choose OK. You can browse that object’s properties and methods in the Object Browser, as shown in Figure 22.3. As covered in Chapter 8, “Objects, Properties, Methods, and Events Explained,” to access the Object Browser, you can choose View|Object Browser, press F2, or click the Object Browser tool while in the Module window. Notice that in Figure 22.3, the Object Browser displays all the classes that belong to the Excel 10.0 object library. The Range class is selected. All the members of the Range class are displayed in the list box at the right. The AutoFill method is selected. Notice that the bottom half of the Object Browser shows all the arguments associated with the AutoFill method of the Range class.

Figure 22.2. Registered automation server objects.

Figure 22.3. The Object Browser.

Before you can talk to an application, you need to know the objects contained within it. You then can 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 also can view the objects that a product supports by using the Object Browser. After you create an object variable, you can manipulate the object without user intervention.

Dim objExcel As New Excel.Application

objExcel.Visible = True

Dim objExcel as Excel.Application
Set objExcel = New Excel.Application

After you create 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.

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

objExcel.Workbooks.Add

Binding is another important automation concept. There are two types of binding available with automation components. They are early binding and late binding. With early binding, you create a reference to a component’s type library. This notifies Access of all the library’s objects, properties, methods, and events. With late binding, you instantiate objects at runtime without referencing them at design time. VBA does not know anything about the objects that you are creating until runtime.

Most objects that you automate support early binding. You should utilize early binding whenever possible. Early binding has several benefits. Because each object’s properties and methods are resolved at compile time, early binding is faster and more efficient. Furthermore, once you create a reference to a type library, all of the library’s objects and their properties and methods are available via Intellisense. Finally, online help is available for any type libraries that you have referenced. This means, for example, if you have referenced Excel’s library from Access, the process of placing your cursor on an object, property, or method and pressing F1 displays help for the selected item.

Listing 22.1 provides an example of early binding. This code requires that a reference first be made to the Excel object library.

Sub EarlyBinding()

    'Declare and instantiate an Excel Application object
    Dim objExcel As Excel.Application
    Set objExcel = New Excel.Application

    'Set properties and execute methods of the object
    With objExcel
        .Visible = True
        .Workbooks.Add
        .Range("A1") = "Hello World"
    End With
End Sub

Sub LateBinding()

    'Declare a generic object variable
    Dim objExcel As Object

    'Point the object variable at an Excel applciation object
    Set objExcel = CreateObject("Excel.Application.10")

    'Set properties and execute methods of the object
    With objExcel
        .Visible = True
        .Workbooks.Add
        .Range("A1") = "Hello World"
    End With

End Sub

Before you attempt to talk to Excel, you must understand its object model. Excel gives you an excellent overview of the Excel object model. You can find this model 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.

After you launch Excel, it launches as a hidden window with a Visible property of False. Destroying the Excel object variable does not cause Excel to terminate. To make things even more complicated, each time you use the New keyword within the Dim or Set statement, 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. If you want to use a running instance of Excel, you can omit the New keyword. This has its disadvantages as well. Let’s say, for example, that the user of your application has created a large spreadsheet and has not saved it recently. Your application uses an existing instance of Excel, creates a new workbook, prints, and then exits without saving. You might find that your user is very angry about the loss of his or her important work. For this reason, I have found it preferable to suffer the potential resource costs and create my own instance of Excel. If you want to launch Excel invisibly, do your work, and get out, make sure that you terminate Excel upon completion of your code.

Before you execute code that relies on a running copy of Excel, it is important to ascertain that Excel launched successfully. The function shown in Listing 22.3 attempts to launch Excel. If it is successful, True is returned from the function. Otherwise, False is returned from the function.

Function CreateExcelObj() As Boolean
    'Invoke error handling
    On Error GoTo CreateExcelObj_Err

    'Assume a False return value
    CreateExcelObj = False

    'Attempt to Launch Excel
    Set gobjExcel = New Excel.Application

    'If Excel launches successfully, return true
    CreateExcelObj = True

CreateExcelObj_Exit:
    Exit Function

CreateExcelObj_Err:

    'If an error occurs, display a message and return false
    MsgBox "Couldn't Launch Excel!!", vbCritical, "Warning!!"
    CreateExcelObj = False
    Resume CreateExcelObj_Exit
End Function

The routine begins by invoking error handling. It initializes the return value for the function to False. The routine then attempts to launch Excel. If it is successful, the public variable gobjExcel references the running instance of Excel, and True is returned from the function. If an error occurs, the code within the error handler is executed. A message is displayed, and the return value for the function is set to False.

Note

You can find this code and most other examples used in this chapter in the CHAP22EX.MDB database located on your sample code CD-ROM. This routine is located in basUtils.

The CreatExcelObj function is called from the Click event of cmdFillExcel command button on the frmSimpleExcel form. The application attempts to talk to the Excel object only if the return value of the function is True, indicating that Excel was loaded successfully.

Private Sub cmdFillExcel_Click()
    'If Excel is launched successfully,
    'execute the FillCells routine
    If CreateExcelObj() Then
        Call FillCells
    End If
End Sub

If Excel launches successfully, the FillCells subroutine executes, as shown in Listing 22.4.

Sub FillCells()
   'Declare an Excel Worksheet object
    Dim objWS As Excel.Worksheet

    'Invoke Error Handling
    On Error GoTo FillCells_Err

    With gobjExcel
        'Add a Workbook to the Workbooks collection
        .Workbooks.Add
        'Point the Worksheet object at the active sheet
        Set objWS = gobjExcel.ActiveSheet

        'Set the value of various cells in the sheet
        With objWS
            .Cells(1, 1).Value = "Schedule"
            .Cells(2, 1).Value = "Day"
            .Cells(2, 2).Value = "Tasks"
            .Cells(3, 1).Value = 1
            .Cells(4, 1).Value = 2
        End With

        'Select A3 through A4
        .Range("A3:A4").Select

        'Use the AutoFill method to fill the range of A3
        'through A33 with numeric values
        .Selection.AutoFill gobjExcel.Range("A3:A33")

        'Select cell A1
        .Range("A1").Select

        'Make Excel visible
        .Visible = True
    End With

FillCells_Exit:

    Exit Sub

FillCells_Err:
    'If Excel object still set, quit Excel and destroy
    'the object variable
    If Not gobjExcel Is Nothing Then
        gobjExcel.Quit
        Set gobjExcel = Nothing
    End If

    Resume FillCells_Exit
End Sub

You can find this relatively simple routine in frmSimpleExcel, which is part of the CHAP22EX.MDB database file. (See Figure 22.4.) 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 objWS = gobjExcel.ActiveSheet to provide a shortcut for talking to the active sheet in the new Excel workbook. Using the objWS object reference, it modifies the values of several cells. It then uses the AutoFill method to quickly fill a range of cells with data. It returns the cursor to cell A1, and the Excel object is made visible. You might wonder what the AutoFill method is; it automates the process of filling a range of cells with a pattern of data. Figure 22.5 shows the results. 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 its capabilities. 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 automation.

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

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

After the user clicks the Close Excel command button, the CloseExcel subroutine is called, as shown in Listing 22.5. The subroutine first checks to see whether the gobjExcel object variable is still set. If it is, Excel is still running. The DisplayAlerts property of the Excel application object is set to False. This ensures that, when the Quit method is executed, Excel will not warn about any unsaved worksheets. This methodology is acceptable because all work was accomplished using a new instance of the Excel application. If you want to save your work, you should execute the required code before the Quit method is executed.

Sub CloseExcel()

    'Invoke error handling
    On Error GoTo CloseExcel_Err

    'If the Excel object variable is still set,
    'turn of alerts and quit Excel
    If Not gobjExcel Is Nothing Then
        gobjExcel.DisplayAlerts = False
        gobjExcel.Quit
    End If

CloseExcel_Exit:
    'Destroy the Excel object variable
    Set gobjExcel = Nothing
    Exit Sub

CloseExcel_Err:
    'Display error message and resume at Exit routine
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume CloseExcel_Exit
End Sub

Now that you have learned how to talk to Excel, you are ready to learn how to do something a bit more practical. Figure 22.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 shown in Figure 22.7. (Listing 22.6 shows the code that produces this graph.)

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

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

Private Sub cmdCreateGraph_Click()
    On Error GoTo cmdCreateGraph_Err
    Dim rstData As ADODB.Recordset
    Dim rstCount As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim rng As Excel.Range
    Dim objWS As Excel.Worksheet
    Dim intRowCount As Integer
    Dim intColCount As Integer

    'Display Hourglass
    DoCmd.Hourglass True

    'Instantiate an ADO recordset and set its Connection
    Set rstData = New ADODB.Recordset
    rstData.ActiveConnection = CurrentProject.Connection

    'Instantiate a second ADO recordset and set its Connection
    Set rstCount = New ADODB.Recordset
    rstCount.ActiveConnection = CurrentProject.Connection

    'Attempt to create Recordset based
    'on the result of qrySalesByCount
    If CreateRecordset(rstData, rstCount, "qrySalesByCountry") Then

        'If the recordset is created successfully, attempt to launch Excel
        If CreateExcelObj() Then

            'If Excel is launched successfully, add a workbook
            gobjExcel.Workbooks.Add

            'Create a pointer to the Active sheet
            Set objWS = gobjExcel.ActiveSheet
            intRowCount = 1
            intColCount = 1

            'Loop though Fields collection of the recordset,
            'using field names as column headings
            For Each fld In rstData.Fields
                If fld.Type <> adLongVarBinary Then
                    objWS.Cells(1, intColCount).Value = fld.Name
                    intColCount = intColCount + 1
                End If
            Next fld

            'Send Recordset to Excel
            objWS.Range("A1").CopyFromRecordset rstData, 500

            'Format Data
            With gobjExcel
                .Columns("A:B").Select
                .Columns("A:B").EntireColumn.AutoFit
                .Range("A1").Select
                .ActiveCell.CurrentRegion.Select
                Set rng = .Selection
                .Selection.NumberFormat = "$#,##0.00"

                'Add a Chart Object
                .ActiveSheet.ChartObjects.Add(135.75, 14.25, 607.75, 301).Select

                'Run the Chart Wizard
                .ActiveChart.ChartWizard Source:=Range(rng.Address), _
                    Gallery:=xlColumn, _
                    Format:=6, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels _
                    :=1, HasLegend:=1, Title:="Sales By Country", CategoryTitle _

                    :="", ValueTitle:="", ExtraTitle:=""

                'Make Excel Visible
                .Visible = True
            End With
        Else
            'If Excel not launched successfully, display an error message
            MsgBox "Excel Not Successfully Launched"
        End If
    Else
        'If more than 500 records are in result set, display a message
        MsgBox "Too Many Records to Send to Excel"
    End If

cmdCreateGraph_Exit:
    Set rstData = Nothing
    Set rstCount = Nothing
    Set fld = Nothing
    Set rng = Nothing
    Set objWS = Nothing

    'Turn hourglass off
    DoCmd.Hourglass False
    Exit Sub

cmdCreateGraph_Err:
    'If an error occurs, display a message and return to
    'common exit routine
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume cmdCreateGraph_Exit
End Sub

This routine begins by creating several object variables. It then creates two recordsets and sets the ActiveConnection property of each recordset to the connection associated with the current project. It calls a user-defined function called CreateRecordset, located in the basUtils module. The CreateRecordset function receives three parameters: the two recordset object variables and the name of a query. Listing 22.7 shows the CreateRecordset function.

Function CreateRecordset(rstData As ADODB.Recordset, _
    rstCount As ADODB.Recordset, _
    strTableName As String)
    On Error GoTo CreateRecordset_Err

    'Create recordset that contains count of records in query result
    rstCount.Open "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
        rstData.Open strTableName
        CreateRecordset = True
    End If

CreateRecordset_Exit:
    'Common exit point; destroy the rstCount recordset
    Set rstCount = Nothing
    Exit Function

CreateRecordset_Err:
    'Display error message and resume at common exit point
    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 is passed. If the number of records exceeds 500, the function returns False; otherwise, the function opens a recordset based on the query name that is passed and returns True. This function ensures that only a reasonable number of records are sent to Excel and that a recordset can be opened successfully.

If the CreateRecordset function returns True, the remainder of the code in the Click event of the cmdCreateGraph command button executes. 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 uses the CopyFromRecordset method of the Excel Range object to copy the contents of the recordset rstData to cell A1 in the active worksheet. The data from each row 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 object fields (adLongVarBinary) are excluded from the process.

After all the data in the recordset is 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.

As you discovered in the preceding section, Excel exposes many objects. You can manipulate each of these objects separately, using its own properties and methods. Prior to Office 97, this was not true for Word because Word exposed only one object, called Word.Basic. Microsoft Word 97, Microsoft Word 2000, and Microsoft Word 2002 all sport the Visual Basic for Applications language. Word 97, Word 2000, and Word 2002 expose many objects just as Excel and other Microsoft products do.

Just as with Excel, you can use the Dim statement or Dim as New statement to launch Word. Like Excel, Word launches as a hidden object. The Word application object has a Visible property, which makes the Word object visible. If you create a Word object using automation, Word will not automatically terminate, even if the object variable is destroyed.

Using Word to Generate a Mass Mailing

Figure 22.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 displayed are sent to a Word mail merge and printed. Figure 22.9 shows an example of the resulting document, and Listing 22.8 shows the code that generated this document.

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

Figure 22.9. The result of the mail merge.

Example 22.8. Generating a Word Mail Merge Document

Private Sub cmdMergeToWord_Click()
    On Error GoTo cmdMergeToWord_Err

    'Turn Hourglass on
    DoCmd.Hourglass True

    'Attempt to create a Word object
    If CreateWordObj() Then

        'If Word object created
        With gobjWord

            'Make Word visible
            .Visible = True

            'Open a document called CustomerLetter in the
            'current folder
            .Documents.Open CurrentProject.Path & _
                "customerletter.doc"

            'Give the document time to open
            DoEvents

            'Use the MailMerge method of the document
            'to perform a mail merge
            With gobjWord.ActiveDocument.MailMerge
                .Destination = wdSendToNewDocument
                .SuppressBlankLines = True
                .Execute
            End With

            'Send the result of the merge to the print preview
            'window
            .ActiveDocument.PrintPreview  'Preview

            'Make Word visible
            .Visible = True
        End With
    End If

cmdMergeToWord_Exit:
    'Turn hourglass off
    DoCmd.Hourglass False
    Exit Sub

cmdMergeToWord_Err:
    'Display error message, destroy Word object and go
    'to common exit routine
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Set gobjWord = Nothing
    Resume cmdMergeToWord_Exit
End Sub

The code begins by presenting an hourglass mouse pointer to the user. This helps to ensure that, if the process takes a while, the user knows that something is happening. It then calls the CreateWordObj routine to create a Word object. The CreateWordObj routine is similar to the CreateExcel routine shown earlier in the chapter. The Open method is executed on the Documents collection of the Word object. It opens a document called customerletter in the current folder. The customerletter document already has been set up to do a mail merge with the results of a query called qryMerge. The subroutine sets the Destination property of the MailMerge object to a new document. It sets the SuppressBlankLines property to True, and then executes the mail merge with the Execute method. This merges the results of qryMailMerge and creates a new document with the mail-merged letters. The PrintPreview method is executed on the ActiveDocument object so that the merged document is printed. Finally, the Visible property of the Word object is set to True, making Word visible, and the hourglass vanishes.

Using Word to Overcome the Limitations of Access as a Report Writer

Although in most ways Access is a phenomenal report writer, it does have its limitations. For example, you cannot bold or italicize an individual word or phrase within a text box. This is quite limiting if you need to emphasize something such as a past due amount in a dunning letter. When the document I need to produce appears more like a letter than a report, I often think of Microsoft Word. The document pictured in Figure 22.10 produces a letter that provides information to the recipient of an order. The code shown in Listing 22.9 produces the letter.

Figure 22.10. Order confirmation letter produced in Microsoft Word.

Example 22.9. Word Bookmarks Are Used to Indicate Where Inserted Text Is Placed in the Word Template

Private Sub cmdSendConfirmation_Click()

    Dim objDocument As Word.Document

    'Launch Word
    If CreateWordObj() Then

        'Make Word visible
        gobjWord.Visible = True

        'Point the Document object at a new document
        'based on the Order.dot template
        Set objDocument = gobjWord.Documents.Add _
            (CurrentProject.Path & "Order.dot")

        'Populate all of the bookmarks with the order information
        With objDocument.Bookmarks
              .Item("CompanyNameAddress").Range.Text = Nz(Me.txtShipName)
              .Item("Address").Range.Text = Nz(Me.txtShipAddress)
              .Item("City").Range.Text = Nz(Me.txtShipCity)
              .Item("Region").Range.Text = Nz(Me.txtShipRegion)
              .Item("PostalCode").Range.Text = Nz(Me.txtShipPostalCode)
              .Item("CompanyName").Range.Text = Nz(Me.txtShipName)
              .Item("Shipper").Range.Text = Nz(Me.txtShipName)
              .Item("ShippedDate").Range.Text = Nz(Me.txtShippedDate)
              .Item("FreightAmount").Range.Text = Nz(Me.txtFreight)

        End With
    End If
End Sub

The example first launches Word. It then gets a reference to the a new document based on the Order.dot template. After that, it populates bookmarks in the document with values from the currently displayed order.

Believe it or not, even PowerPoint can be controlled using 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 AppWindow to True. Destroying the PowerPoint object variable does not terminate the PowerPoint application.

The code shown in Listing 22.10 is located under the Click event of the cmdChangePicture command button on frmOLEToPowerPoint, which is shown in Figure 22.11. Figure 22.12 shows the resulting PowerPoint slide.

Figure 22.11. The form used to create a PowerPoint slide.

Figure 22.12. A PowerPoint slide created using automation.

Private Sub cmdChangePicture_Click()
    'Display Open common dialog
    dlgCommon.ShowOpen

    'If the user selected a file, set the SourceDoc
    'property of the OLE control to the selected document
    If Len(dlgCommon.Filename) Then
        olePicture.SourceDoc = dlgCommon.Filename

        'Designate that you wish to link to
        'the selected document
        olePicture.Action = acOLECreateLink
    End If
End Sub

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

Listing 22.11 shows the routine that creates the PowerPoint slide.

Private Sub cmdMakePPTSlide_Click()

    Dim objPresentation As PowerPoint.Presentation
    Dim objSlide As PowerPoint.Slide

    'Ensure that both the title and the picture are selected
    If IsNull(Me.txtTitle) Or Me.olePicture.SourceDoc = "" Then

        MsgBox "A Title Must Be Entered, and a Picture Selected Before Proceeding"

    Else

        'Create instance of PowerPoint application
        Set mobjPPT = New PowerPoint.Application

        'Make instance visible to user
        mobjPPT.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.Shapes.Title.TextFrame.TextRange
            .Text = Me.txtTitle
            .Font.Color.RGB = RGB(0, 0, 255)
            .Font.Italic = True
        End With

        'Add the OLE Picture
        objSlide.Shapes.AddOLEObject _
            Left:=200, Top:=200, Width:=500, Height:=150, _
            Filename:=olePicture.SourceDoc, link:=True

    End If

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 then is added to the PowerPoint object, and 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 automation object, which is added to the slide.

Microsoft Outlook is a very powerful e-mail client. It is also an excellent tool for both task and contact management. As an application developer, I find many opportunities to automate Outlook from the Access applications that I build. For example, one of my clients sends out mass e-mail mailings to selected groups of his or her customers. I use an Access front end to manipulate customers stored in a SQL Server back end. Included in the front end is a feature that enables the users to generate an e-mail message and then enter the criteria that designates which clients receive the e-mail message. This is one of many examples of how you can integrate the rich features of Access and Outlook.

The form pictured in Figure 22.13 allows the user to select an e-mail template used for a mass mailing. The mailing is sent to all users who meet the criteria entered in a query called qryBulkMail. A more sophisticated example would allow the users to build the query on-the-fly, using a custom query-by-form. The code that allows the user to select an Outlook e-mail template appears in Listing 22.12.

Figure 22.13. This form allows the user to select the e-mail template used for a mass mailing.

Private Sub cmdBrowse_Click()

    'Filter the Open dialog to Outlook template files
    dlgCommon.Filter = "*.oft"

    'Display the Open dialog
    dlgCommon.ShowOpen

    'Populate txtTemplate with the selected file
    Me.txtTemplate = dlgCommon.FileName
End Sub

The code first sets the filter of the Common Dialog control to show only files with the .OFT extension. It then displays the Open dialog. After the user selects a file, the name and path of the file is placed in the txtTemplate text box. The code required to send the mailing is shown in Listing 22.13.

Sub CreateMail()
    ' Customize a message for each contact and then send or save the message
    Dim intMessageCount As Integer

    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Open a recordset based on the result of qryBulkMail
    rst.Open "qryBulkMail", CurrentProject.Connection
    intMessageCount = 0

    Set mobjOutlook = CreateObject("Outlook.Application")

    ' Loop through the contacts in the open folder
    Do Until rst.EOF
        ' Check that the contact has an email address.
        If rst("EmailAddress") <> "" Then

            'Create a mail item based on the selected template
            Set mobjCurrentMessage = mobjOutlook.CreateItemFromTemplate(Me.txtTemplate)

            'Add the email address as the recipient for the message
            mobjCurrentMessage.Recipients.Add rst("EmailAddress")

            ' Send the message or save it to the Inbox
            If Me.optSend = 1 Then
                mobjCurrentMessage.Save
            Else
                mobjCurrentMessage.Send
            End If
            intMessageCount = intMessageCount + 1
         End If
         rst.MoveNext
    Loop

    ' Write the number of messages created to the worksheet
    MsgBox intMessageCount & " Messages Sent"

End Sub

First, the code creates a recordset based on qryBulkMail. It then loops through the recordset. As it visits each row in the resultset, it creates an Outlook message based on the designated template. It adds the e-mail address of the current row as a recipient of the e-mail message. It then either saves the message as a draft, or immediately sends it to the designated recipient.

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

You can find an overview of the Access object model 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 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.

The form shown in Figure 22.14 is a UserForm associated with an Excel spreadsheet. It is called frmReportSelect and is part of the Excel spreadsheet called RunAccessReports.xls, included on the sample code CD. The form enables you to select any Access database. It displays a list of all reports in the selected database; you can use this list to preview an Access report or print multiple Access reports.

Figure 22.14. The UserForm that enables you to print Access reports.

Listing 22.14 shows how this UserForm form accomplishes its work.

Private Sub cmdSelectDatabase_Click()
    'Set filter property of the Common Dialog control
    dlgCommon.Filter = "*.mdb"

    'Display the open common dialog
    dlgCommon.ShowOpen

    'Ensure that a file was selected
    If dlgCommon.FileName = "" Then
        MsgBox "You Must Select a File to Continue"
    Else

        'Set the text property of the text box to the
        'file selected in the Open dialog
        Me.txtSelectedDB = _
        dlgCommon.FileName

        'Call the ListReports routine
        Call ListReports
    End If
End Sub

Private Sub ListReports()
    On Error GoTo ListReports_Err
    Dim vntReport As Variant

    'If the Access object is not set, instantiate Access
    If mobjAccess Is Nothing Then
        Set mobjAccess = New Access.Application
    End If

    'Open the database selected in the text box
    mobjAccess.OpenCurrentDatabase (Me.txtSelectedDB)

    'Clear the list box
    lstReports.Clear

    'Loop through each report in the AllReports collection
    'of the selected database
    For Each vntReport In mobjAccess.CurrentProject.AllReports
        lstReports.AddItem vntReport.Name
    Next vntReport

ListReports_Exit:
    Exit Sub

ListReports_Err:
    MsgBox "Error #" & Err.Number & _
    ": " & Err.Description
    Resume ListReports_Exit
End Sub

The cmdSelectDatabase_Click event routine sets the Filter property of the Common Dialog control to Access database files. The ShowOpen method of the Common Dialog control is used to display the File Open dialog to the user. The ListReports routine executes after the user selects a file from the dialog,.

The ListReports subprocedure begins by creating an instance of the Access application. It uses the OpenCurrentDatabase method of the Access object to open the Access database selected by the user in the File Open common dialog box. It then loops through the AllReports collection of the CurrentProject object that is associated with the selected database. It adds the name of each report to the list box.

The routine in Listing 22.15 prints the selected reports.

Private Sub cmdPrint_Click()
    On Error GoTo cmdPreview_Err
    Dim intCounter As Integer
    Dim intPrintOption As Integer

    'Evaluate whether Print or Preview was selected
    If optPreview.Value = True Then
        intPrintOption = acViewPreview
    ElseIf optPrint.Value = True Then
        intPrintOption = acViewNormal
    End If

    'Make Access Visible
    mobjAccess.Visible = True

    'Loop through the list box, printing the
    'selected reports
    For intCounter = 0 To _
        lstReports.ListCount - 1
        If lstReports.Selected(intCounter) Then
            mobjAccess.DoCmd.OpenReport _
            ReportName:=Me.lstReports.List(intCounter), _
            View:=intPrintOption
        End If
    Next intCounter

cmdPreview_Exit:
    Exit Sub

cmdPreview_Err:
    MsgBox "Error #" & Err.Number & _
    ": " & Err.Description
    If Not mobjAccess Is Nothing Then
    mobjAccess.Quit
    End If
    Set mobjAccess = Nothing

    Resume cmdPreview_Exit

End Sub

The cmdPrint Click event routine begins by evaluating whether the print or preview option button is selected. The Access application object is made visible. The code then loops through the lstReports list box, printing or previewing each selected report. The OpenReport method is used along with the constant acViewPreview or the constant acViewNormal in order to accomplish this task.

Using Automation to Extend the Functionality of the Time and Billing Application

Many potential applications of automation exist for the Time and Billing application. One of them is discussed in this section.

The form in Figure 22.15 enables users to select a table or query to send to Excel. The form is called frmSendToExcel.

Figure 22.15. Exporting a table or query to send to Excel.

The Load event of the form is used to add all the table and query names to the list box. The Load event is shown in Listing 22.16. Notice that the function uses the AllTables and AllQueries collections of the current database to populate the list box, excluding all the system tables.

Example 22.16. Adding Table and Query Names to the List Box

Private Sub Form_Load()
    Dim vntObject As Variant

    'Loop through each table, adding its name
    'to the list box
    For Each vntObject In CurrentData.AllTables
        If Left(vntObject.Name, 4) <> "MSys" Then
            Me.lstTables.AddItem vntObject.Name
        End If
    Next vntObject

    'Loop through each query, adding its name to
    'the list box
    For Each vntObject In CurrentData.AllQueries
        Me.lstTables.AddItem vntObject.Name
    Next vntObject
End Sub

The Click event of the cmdSendToExcel command button sends the selected table or query to Excel. Listing 22.17 shows this code.

Example 22.17. Sending a Table or Query to Excel

Private Sub cmdSendToExcel_Click()
    On Error GoTo cmdSendToExcel_Err
    Dim objWS As Excel.Worksheet
    Dim rstData As ADODB.Recordset
    Dim rstCount As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim intColCount As Integer
    Dim intRowCount As Integer

    Set rstData = New ADODB.Recordset
    rstData.ActiveConnection = CurrentProject.Connection
    Set rstCount = New ADODB.Recordset
    rstCount.ActiveConnection = CurrentProject.Connection

    'Invoke Hourglass
    DoCmd.Hourglass True

    'Try to Create Recordset and Create Excel Object
    If CreateRecordset(rstData, rstCount, lstTables.Value) Then
        If CreateExcelObj() Then

            'Add a Workbook
            gobjExcel.Workbooks.Add

            'Create a reference 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 rstData.Fields
                If fld.Type <> adLongVarBinary Then
                    objWS.Cells(1, intColCount).Value = fld.Name
                    intColCount = intColCount + 1
                End If
            Next fld

            'Send Recordset to Excel
            objWS.Range("A2").CopyFromRecordset rstData, 500
            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 rstCount = Nothing
    Set rstData = 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 shown in Listing 22.18. 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 uses the CopyFromRecordset method of the range object to copy all the field values to the rows in the Excel worksheet. Finally, it issues the AutoFilter method so that the user easily can manipulate the data in Excel, filtering it as necessary. (See Figure 22.16.)

Figure 22.16. Using AutoFilter to analyze data sent to Excel.

Caution

Although extremely easy to use, the CopyFromRecordset method of the range object has one major limitation. If the table or query used to populate the recordset being sent to Excel contains an OLE object field, the method will fail. There are two solutions to this problem. The most simple solution is to always base the recordset sent to Excel on a query. Do not include any OLE object fields in the query. A second solution is to use a less elegant alternative to the CopyFromRecordset method. Simply loop through the recordset one record at a time. As each record is visited, send it to the appropriate row and column. Because the first method is not only easier to code, but is more optimized, it should be used wherever possible.

Example 22.18. Checking Recordset Size

Function CreateRecordset(rstData As ADODB.Recordset, _
   rstCount As ADODB.Recordset, _
    strTableName As String)
    On Error GoTo CreateRecordset_Err

    'Create recordset that contains count of records in query result
    rstCount.Open "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
        rstData.Open strTableName
        CreateRecordset = True
    End If

CreateRecordset_Exit:
    'Common exit point; destroy the rstCount recordset
    Set rstCount = Nothing
    Exit Function

CreateRecordset_Err:
    'Display error message and resume at common exit point
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume CreateRecordset_Exit
End Function

This routine, found in basUtils, 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.

Automation enables you to control other applications from your Access application, and it enables other programs to control your Access application. This chapter began by providing an overview of automation and why you might want to use it. It discussed creating an object variable to reference the application you are automating. After the ins and outs of the object variable were explained, you saw numerous examples of manipulating automation objects. You looked at detailed code showing automation involving Excel, Word, Outlook and PowerPoint. Finally, you learned about controlling Access from other applications.

The capability to communicate with other applications has become a prerequisite for successful software development. It is extremely important to be aware of the wealth of tools available. The capability to call on other applications’ features is helping to make the world document-centric, rather than application-centric. This means that users can focus on their tasks and not on how they are accomplishing those tasks. Although automation requires significant hardware and also is rather slow, the benefits it provides often are well worth the price.