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.
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.
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.
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.
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
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.
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.
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".
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:
This code uses the Add method to add a workbook to the Excel object.
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
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.
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
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.
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.
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.
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.
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.
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.
Many potential applications of OLE Automation exist for the time and billing application. One of them is discussed in this section.
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.
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.