| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
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.
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.
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.
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.
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:
objExcel.Workbooks.Add
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
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.
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.
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.
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.