Automation
Automation refers to the capability to control one program from within another. For example, you can control Microsoft Excel from within your Access applications. In such a case, Access would be the automation client and Excel the automation server.
In order to use automation, you must first add a reference to the object library for the application that you want to automate. As you learned before, you can add references using Tools 
References from the Visual Basic Editor.
Go ahead and add a reference to Excel, Word, and Outlook, as shown in Figure 11-5. You will be looking at examples of each of these next.
Figure 11-5
Working with Automation Objects
Before you can automate external applications, you need to declare an object variable. The following example shows how you declare an object variable for Excel:
Dim objExcel As New Excel.Application The preceding line of code declares a new object and creates a new instance of Excel in a single line of code. If you want to control the point when a new instance of Excel is created, you can separate the declaration and instantiation into two lines of code, as shown next.
Dim objExcel As Excel.Application Set objExcel = New Excel.Application After you have created an instance of the automation object, you can work with the object’s properties, methods, and events. You have already worked with properties, methods, and events of other objects in earlier chapters. Working with the properties, methods, and events of automation objects is not any different. For example, you can set the Visible property of the objExcel object to True so that it will be visible, as shown here:
objExcel.Visible = True The preceding example implements a technique called early binding. Early binding occurs when you declare the type of object explicitly within your code. Several advantages result from using early binding. For example, when all lines of code referencing the object are resolved at compile time, the code executes faster, and you can view the available properties, methods, and events of the object as you type your code. For these reasons, you should use early binding whenever possible.
Although early binding is preferable, you can also use a technique called late binding. Late binding enables you to instantiate objects at runtime without referencing them at design time. In other words, your VBA application doesn’t know what type of object you want and what you plan to do with the object until it runs the code and works with the object. Below is an example of using late binding with Excel:
'declare a new generic object Dim objExcel as Object 'instantiate the new object as an Excel object Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Notice that the CreateObject method is used to create a new instance of Excel. If you already have an open instance of the Excel object that you need to work with, use the GetObject method, as shown in the following code:
Set objExcel = GetObject(, "Excel.Application") Now that you are familiar with the high-level concepts of declaring and instantiating new instances of automation objects, let’s walk through some actual examples of controlling Excel, Word, and Outlook from Access.
Controlling Microsoft Excel
You have already seen a few examples of how to declare and instantiate Excel objects. Let’s jump right into learning how you can control Excel from your Access application.
Try It Out-Controlling an Excel Spreadsheet from Access
 |
In this example, you will create a new Excel workbook and populate some of the cells with data.
-
Add the following code to your module:
Sub ControlExcel() Dim objExcel As New Excel.Application Dim objWorksheet As New Excel.Worksheet 'add a new workbook to the spreadsheet objExcel.Workbooks.Add 'point the worksheet variable at the active sheet Set objWorksheet = objExcel.ActiveSheet 'add various values to the cells With objWorksheet .Cells(1, 1).Value = "First Name" .Cells(1, 2).Value = "Middle Initial" .Cells(1, 3).Value = "Last Name" .Cells(2, 1).Value = "John" .Cells(2, 2).Value = "A." .Cells(2, 3).Value = "Doe" .Cells(3, 1).Value = "Jack" .Cells(3, 2).Value = "D." .Cells(3, 3).Value = "Smith" End With 'show Excel to the user objExcel.Visible = True End Sub -
Execute the ControlExcel procedure from the Immediate Window by typing ControlExcel and pressing Enter.
-
You should see an Excel screen similar to that shown in Figure 11-6.
Figure 11-6
How It Works
The ControlExcel procedure uses an Excel Application object and an Excel Worksheet object:
Sub ControlExcel() Dim objExcel As New Excel.Application Dim objWorksheet As New Excel.Worksheet
After you declare the objects, a new workbook is added to the Excel object:
'add a new workbook to the spreadsheet objExcel.Workbooks.Add
The new workbook is then pointed to the active sheet:
'point the worksheet variable at the active sheet Set objWorksheet = objExcel.ActiveSheet
Using the Cells property of the Worksheet object, you assign values to various cells:
'add various values to the cells With objWorksheet .Cells(1, 1).Value = "First Name" .Cells(1, 2).Value = "Middle Initial" .Cells(1, 3).Value = "Last Name" .Cells(2, 1).Value = "John" .Cells(2, 2).Value = "A." .Cells(2, 3).Value = "Doe" .Cells(3, 1).Value = "Jack" .Cells(3, 2).Value = "D." .Cells(3, 3).Value = "Smith" End With
The last line of code displays the Excel instance to the user:
'show Excel to the user objExcel.Visible = True End Sub
When you run the procedure from the Immediate Window, you see an instance of Excel with the values populated in the cells as specified in the preceding code.
|
Controlling Microsoft Word
You can control Word from Access in the same way you can control Excel. The Word object model is, of course, different because you are working with documents instead of spreadsheets. Let’s walk through how this works.
Try It Out-Creating Word Letters from Access
|
In this example, you will create a contacts table and then a Word document that contains a letter to be sent to each of the contacts in the contacts table.
-
In your Ch11CodeExamples database, create a new table, as shown in Figure 11-7.
Figure 11-7
-
Next, add some sample data to the table, such as the data shown in Figure 11-8.
Figure 11-8 -
Add the following ControlWord procedure to the existing module:
Sub ControlWord() Dim objWord As New Word.Application Dim rsContacts As New ADODB.Recordset Dim strLtrContent As String rsContacts.ActiveConnection = CurrentProject.Connection rsContacts.Open "tblContacts" objWord.Documents.Add 'for each record in the tblContacts table, create a letter in Word Do While Not rsContacts.EOF strLtrContent = rsContacts("FirstName") & " " & rsContacts("LastName") strLtrContent = strLtrContent & vbCrLf & rsContacts("Address") & vbCrLf strLtrContent = strLtrContent & rsContacts("City") & ", " & rsContacts("Region") strLtrContent = strLtrContent & " " & rsContacts("PostalCode") & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Dear " & rsContacts("FirstName") & " " strLtrContent = strLtrContent & rsContacts("LastName") & ":" & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Please note we have moved to a new " & _ "location. " strLtrContent = strLtrContent & "Our new address is 4 Somewhere, Avon, " & _ "IN 46060." strLtrContent = strLtrContent & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & strLtrContent = strLtrContent & vbCrLf & "Your Favorite Store" 'insert the content at the end of the document (which is the beginning 'if it is the first one) objWord.Selection.EndOf objWord.Selection.Text = strLtrContent 'insert a page break objWord.Selection.EndOf objWord.Selection.InsertBreak 'get the next contact record rsContacts.MoveNext Loop 'show word in Print Preview mode objWord.Visible = True objWord.PrintPreview = True End Sub
-
Run the ControlWord procedure from the Immediate Window by typing ControlWord and pressing Enter. A screen similar to the one shown in Figure 11-9 should be displayed.
Figure 11-9
How It Works
First, you created a table called tblContacts and populated the table with some records. Next, you initiated a new procedure that created a new Word document and then you populated the document with specified text. The procedure began by declaring a new Word Application object and an ADO recordset.
Sub ControlWord() Dim objWord As New Word.Application Dim rsContacts As New ADODB.Recordset Dim strLtrContent As String
The ADO recordset was opened and populated with the tblContacts table:
rsContacts.ActiveConnection = CurrentProject.Connection rsContacts.Open "tblContacts"
A new document was then added to the Word object:
objWord.Documents.Add
For each contact record in the ADO recordset, a string containing the contents of the letter was populated. Each string contained the contents of the letter to the particular contact.
'for each record in the tblContacts table, create a letter in Word Do While Not rsContacts.EOF strLtrContent = rsContacts("FirstName") & " " & rsContacts("LastName") strLtrContent = strLtrContent & vbCrLf & rsContacts("Address") & vbCrLf strLtrContent = strLtrContent & rsContacts("City") & ", " & rsContacts("Region") strLtrContent = strLtrContent & " " & rsContacts("PostalCode") & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Dear " & rsContacts("FirstName") & " " strLtrContent = strLtrContent & rsContacts("LastName") & ":" & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Please note we have moved to a new " & _ "location. " strLtrContent = strLtrContent & "Our new address is 4 Somewhere, Avon, " & _ "IN 46060." strLtrContent = strLtrContent & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & strLtrContent = strLtrContent & vbCrLf & "Your Favorite Store" """,,"","Content & "Your Favorite Store" The contents were then placed at the end of the Word document:
'insert the content at the end of the document (which is the beginning 'if it is the first one) objWord.Selection.EndOf objWord.Selection.Text = strLtrContent
A page break was then inserted into the Word document so the next letter appeared on a separate page:
'insert a page break objWord.Selection.EndOf objWord.Selection.InsertBreak
The next record in the recordset was retrieved:
'get the next contact record rsContacts.MoveNext Loop
After all the records in the recordset were processed, the following code displayed Word in Print Preview mode:
'show word in Print Preview mode objWord.Visible = True objWord.PrintPreview = True End Sub
When you run the procedure from the Immediate Window, the Print Preview Mode of the newly created Word document is displayed, and a letter addressed to each contact from the tblContacts table is displayed.
|
Controlling Microsoft Outlook
You can control Microsoft Outlook in various ways in order to manipulate contacts, e-mails, or calendar appointments. In this section, you will look at an example of sending an e-mail through Outlook from Access.
Try It Out-Sending an Outlook E-mail from Access
|
Using the tblContacts table that was created in the prior example, you will now walk through how to send an Outlook e-mail from Access.
-
Revise the data in the tblContacts table so that only one record exists and the record points to your e-mail address. This ensures that you do not send e-mails to people by mistake based on the dummy data that you typed in the table.
-
Add the following ControlOutlook procedure to your module.
Sub ControlOutlook() Dim objOutlook As New Outlook.Application Dim objEmail As Outlook.MailItem Dim strLtrContent As String Dim rsContacts As New ADODB.Recordset rsContacts.ActiveConnection = CurrentProject.Connection rsContacts.Open "tblContacts" 'for each record in the tblContacts table, send an email Do While Not rsContacts.EOF strLtrContent = "Dear " & rsContacts("FirstName") & " " strLtrContent = strLtrContent & rsContacts("LastName") & ":" & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Please note we have moved to a new location. " strLtrContent = strLtrContent & "Our new address is 4 Somewhere, Avon, IN 46060." strLtrContent = strLtrContent & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Your Favorite Store" 'create an email regarding the new business location Set objEmail = objOutlook.CreateItem(olMailItem) objEmail.Recipients.Add rsContacts("Email") objEmail.Subject = "Our address has changed." objEmail.Body = strLtrContent 'send the message objEmail.Send 'move to the next contacts record rsContacts.MoveNext Loop End Sub -
Run the procedure from the Immediate Window by typing ControlOutlook and pressing Enter. You should receive an e-mail similar to the one shown in Figure 11-10 in your Inbox at the account you specified in the tblContacts e-mail field.
Figure 11-10
How It Works
First, you modified the values in the tblContacts table so that only you received an e-mail from running this test procedure. Then, you added the ControlOutlook procedure to your module:
Sub ControlOutlook()
The procedure used an Outlook Application object and an Outlook MailItem object to create and manipulate e-mail messages:
Dim objOutlook As New Outlook.Application Dim objEmail As Outlook.MailItem
Next, a new ADO recordset was created based on the values in the tblContacts table:
Dim strLtrContent As String Dim rsContacts As New ADODB.Recordset rsContacts.ActiveConnection = CurrentProject.Connection rsContacts.Open "tblContacts"
For each record in the recordset, a string message was generated using the e-mail field and some text that indicated the business had changed addresses:
'for each record in the tblContacts table, send an email Do While Not rsContacts.EOF strLtrContent = "Dear " & rsContacts("FirstName") & " " strLtrContent = strLtrContent & rsContacts("LastName") & ":" & vbCrLf & vbCrLf strLtrContent = strLtrContent & "Please note we have moved to a new " & _ "location. " strLtrContent = strLtrContent & "Our new address is 4 Somewhere, Avon, " & _ "IN 46060." strLtrContent = strLtrContent & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & strLtrContent = strLtrContent & vbCrLf & "Your Favorite Store" """"""Content & "Your Favorite Store" An e-mail was generated that was addressed to the e-mail address in the current record in the recordset. The subject of the e-mail was specified and the body assigned to the string message created in the preceding code:
'create an email regarding the new business location Set objEmail = objOutlook.CreateItem(olMailItem) objEmail.Recipients.Add rsContacts("Email") objEmail.Subject = "Our address has changed." objEmail.Body = strLtrContent The Send method of the MailItem object sent the e-mail to the recipient:
'send the message objEmail.Send
The process repeated for each record in the recordset, which in this case should have been only one record:
'move to the next contacts record rsContacts.MoveNext Loop End Sub
When you ran the procedure from the Immediate Window, an e-mail message was sent to the e-mail address you specified in the Email field in the tblContacts table. If your address was specified, you should have received a new e-mail message in your inbox containing the text shown in Figure 11-10, which indicated the business had a new address.
|
EAN: 2147483647
Pages: 143