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.
It is important to be aware of the hardware that automation requires. It is common for a developer using a Pentium III with 512M of RAM to create a really slick application, only to find that it won’t run on the 32M Pentiums owned by the users. Automation craves RAM. The more, the better! I recommend 128M of RAM or more for applications that use automation. It also is important to recognize that automation is not fast, even on the slickest of machines.
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.
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.
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.
To create an instance of an object, you first must create an object variable that holds a reference to the object. You can do this by using a Dim
statement:
Dim objExcel As New 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 is invisible. You can make it visible by using this statement:
objExcel.Visible = True
Alternatively, you can use two statements to declare and instantiate an object. The code looks like this:
Dim objExcel as Excel.Application Set objExcel = New Excel.Application
The Dim
statement declares an object variable that is ready to be associated with a running instance of Excel. The Set
statement launches Excel and points the object variable at the new instance of Excel. The advantage of this method is that you can better control when the instance of Excel is actually created. If, for example, the declaration is in the General Declarations section of a form, you can place the Set
statement under a command button that is used to launch Excel.
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.
The objects you will be talking to through 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"
.
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.
Example 22.1. Early Binding Requires that a Reference Be Made to the Appropriate Type 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
CreateObject
and GetObject
are required when using late binding. Because, with late binding, Access is not aware of the server application and its objects, properties, methods, and events, you cannot use a Dim
statement and a Set
statement to declare and instantiate the server application object. Instead, you must use Dim
to declare a generic object variable. You then use a Set
statement along with the CreateObject
or GetObject
function to work with the server object. The CreateObject
function launches a new instance of the server object. The GetObject
function is similar to CreateObject
, but attempts to point a running instance of the requested application. Furthermore, unlike the CreateObject
function that receives only one argument as a parameter, the GetObject
function receives an optional parameter with the name of the document you want to work with.
Listing 22.2 provides an example of CreateObject
and late binding.
Example 22.2. The CreateObject
Function Is Used to Create a Late-Bound Instance of Excel
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.
Example 22.3. The CreateExcelObj
Subroutine
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
.
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.
To take advantage of the exciting world of automation, you must install all automation server applications on the user’s machine, and the user must possess a full license to the server applications. In fact, you will be unable to compile and run the examples contained in the sample database for this chapter unless you have the server applications loaded on your development machine.
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.
Example 22.4. The FillCells
Subroutine
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.
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.
Example 22.5. The CloseExcel
Subroutine
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.)
Example 22.6. Creating a Graph from Access
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
If the Common Dialog control is not installed on your machine, or the user’s machine, this code will not run. If that is the case, you must remove the reference to the Common Dialog control before running the example. Any examples in the chapter that utilize the Common Dialog control must be modified. The Common Dialog control is included with the Microsoft Office Developer.
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.
Example 22.7. 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.
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.
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.
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.
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.
You can find details of the PowerPoint object model in Microsoft PowerPoint Visual Basic Reference in PowerPoint Help. You should review this object model before attempting to communicate with PowerPoint.
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.
Example 22.10. Select Picture
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.
Example 22.11. Creating 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.
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.
Example 22.13. Sending the Outlook Message to the Recipients in the qryBulkMail ResultSet
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.
Listing 22.14 shows how this UserForm form accomplishes its work.
Example 22.14. Creating a Visual Basic Form to Print Reports
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.
Example 22.15. Creating a New Instance of the Access Application Object
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.
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.
18.225.234.24