15

Interacting with Other Office Applications

The Office application programs: Excel, Word, PowerPoint, Outlook, Access, Publisher, SourceSafe, and FrontPage all use the same VBA language. Once you understand VBA syntax in Excel, you know how to use VBA in all the other applications. Where these applications differ is in their object models.

One of the really nice things about the common VBA language is that all the Office applications are able to expose their objects to each other, and you can program interactions between all of the applications from any one of them. To work with Word objects from Excel, for example, you only need to establish a link to Word and then you have access to its objects as if you were programming with VBA in Word itself.

This chapter explains how to create the link in a number of different ways and presents some simple examples of programming the other Microsoft applications. In all cases, the code is written in Excel VBA, but it could easily be modified for any other Office application. The code is equally applicable to products outside Office that support the VBA language. These include other Microsoft products such as Visual Basic and SQL Server. There is also a growing list of non-Microsoft products that can be programmed in the same way.

We will also have cause to ponder on macro viruses at the end of this chapter.

Establishing the Connection

Once you have made a connection with an Office application, its objects are exposed for automation through a type library. There are two ways to establish such a connection: late binding and early binding. In either case, you establish the connection by creating an object variable that refers to the target application, or a specific object in the target application. You can then proceed to use the properties and methods of the object referred to by the object variable.

In late binding, you create an object that refers to the Office application before you make a link to the Office application's type library. In earlier versions of the Office applications, it was necessary to use late binding and you will still see it used, because it has some advantages over early binding. One advantage is that you can write code that can detect the presence or absence of the required type library on the PC running your code and link to different versions of applications based on decisions made as the code executes.

The disadvantage of late binding is that the type library for the target application is not accessed when you are writing your code. Therefore, you get no Help information regarding the application, you cannot reference the intrinsic constants in the application and, when the code is compiled, the references to the target application may not be correct, as they cannot be checked. The links are only fully resolved when you try to execute the code and this takes time. It is also possible that coding errors may be detected at this point that cause your program to fail.

Early binding is supported by all the Office applications, from Office 97 onwards. Code that uses early binding executes faster than the code using late binding, as the target application's type library is present when you write your code. Therefore, more syntax and type checking can be performed, and more linkage details can be established, before the code executes.

It is also easier to write code for early binding because you can see the objects, methods, and properties of the target application in the Object Browser and, as you write your code you will see automatic tips appear, such as a list of related properties and methods after you type an object reference. You can also use the intrinsic constants defined in the target application.

Late Binding

The following code creates an entry in the Outlook calendar. The code uses the late binding technique:

Public Sub MakeOutlookAppointment()
  Dim Outlook As Object
  Dim Appointment As Object
  Const Item = 1

  Set Outlook = CreateObject(“Outlook.Application”)
  Set Appointment = Outlook.CreateItem(Item)

  Appointment.Subject = “New Years Party”
  Appointment.Start = DateSerial(2003, 12, 31) + TimeSerial(9, 30, 0)

  Appointment.End = DateSerial(2003, 12, 31) + TimeSerial(11, 30, 0)
  Appointment.ReminderPlaySound = True
  Appointment.Save
  Outlook.Quit
  Set Outlook = Nothing
End Sub

The basic technique in programming another application is to create an object variable referring to that application. The object variable in this case is conveniently named Outlook. You then use Outlook (as you would use the Application object in Excel) to refer to objects in the external application's object model. In this case, the CreateItem method of Outlook's Application object is used to create a reference to a new AppointmentItem object.

As Outlook's intrinsic constants are not available in late binding, you need to define your own constants, such as AppointmentItem here, or substitute the value of the constant as the parameter value. Note the times have been defined using the DateSerial and TimeSerial functions to avoid ambiguity or problems in an international context. See Chapter 17 for more details.

By declaring Outlook and Appointment as the generic Object type, you force VBA to use late binding. VBA cannot resolve all the links to Outlook until it executes the CreateObject function.

The CreateObject input argument defines the application name and class of the object to be created. Outlook is the name of the application and Application is the class. Many applications allow you to create objects at different levels in the object model. For example, Excel allows you to create WorkSheet or Chart objects from other applications, using Excel.WorkSheet or Excel.Chart as the input parameter of the CreateObject function.

It is good programming practice to close the external application when you are finished with it and set the object variable to Nothing. This releases the memory used by the link and the application.

If you run this macro, nothing will happen in Excel at all. However, open up Outlook and in the Calendar you will find that the appointment has been added for the morning of December 31 as shown n Figure 15-1.

Early Binding

If you want to use early binding, you need to establish a reference to the type library of the external application in your VBA project. You do this from the VBE by selecting Tools References, which displays the dialog box shown in Figure 15-2.

You create a reference by checking the box next to the object library. Once you have a reference to an application, you can declare your object variables as the correct type. For example, you could declare Entry as an AddressEntry type as follows:

Dim Entry As AddressEntry

VBA will search through the type libraries, in the order shown from the top down, to find references to object types. If the same object type is present in more than one library, it will use the first one found. You can select a library and click the Priority buttons to move it up or down the list to change the order in which libraries are searched. There is no need to depend on priority, however. You can always qualify an object by preceding it with the name of the main object in the library. For example, instead of using AddressEntry, use Outlook.AddressEntry.

Figure 15-1

The following example uses early binding. It lists all the names of the entries in the Outlook Contacts folder, placing them in column A of the active worksheet. Make sure that you have created a reference to the Outlook object library before you try to execute it:

Public Sub DisplayOutlookContactNames()
   Dim Outlook As Outlook.Application
   Dim NameSpace As Outlook.NameSpace
   Dim AddressList As AddressList
   Dim Entry As AddressEntry
   Dim I As Long

On Error GoTo Finally
   Set Outlook = New Outlook.Application
   Set NameSpace = Outlook.GetNamespace(“MAPI”)
   Set AddressList = NameSpace.AddressLists(“Contacts”)
   For Each Entry In AddressList.AddressEntries
      I = I + 1
      Cells(I, 1).Value = Entry.Name
   Next

Finally:
   Outlook.Quit
   Set Outlook = Nothing
End Sub

Figure 15-2

Outlook will display a warning message that a program is trying to access e-mail addresses. You can allow the operation or cancel it.

Here, we directly declare Outlook to be an Outlook.Application type. The other Dim statements also declare object variables of the type we need. If the same object name is used in more than one object library, you can precede the object name by the name of the application, rather than depend on the priority of the type libraries. We have done this with Outlook.NameSpace to illustrate the point. The New keyword is used when assigning a reference to Outlook.Application to Outlook to create a new instance of Outlook.

The fact that we declare the variable types specifically makes VBA employ early binding. You could use the CreateObject function to create the Outlook object variable, instead of the New keyword, without affecting the early binding. However, it is more efficient to use New.

Opening a Document in Word

If you want to open a file created in another Office application, you can use the GetObject function to directly open the file. However, it is just as easy to open an instance of the application and open the file from the application. We will look at another use of GetObject shortly.

The following code copies a range in Excel to the clipboard. It then starts a new instance of Word, opens an existing Word document, and pastes the range to the end of the document. As the code uses early binding, make sure you establish a reference to the Word object library:

Public Sub CopyChartToWordDocumentCopyTableToWordDocument()
  Dim Word As Word.Application

  ThisWorkbook.Sheets(“Table”).Range(“A1:B6”).Copy

  Set Word = New Word.Application

On Error GoTo Finally

  Word.Documents.Open Filename:=“C:	empchart.doc”
  Word.Selection.EndKey Unit:=wdStory
  Word.Selection.TypeParagraph
  Call Word.Selection.PasteSpecial(Link:=False, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine, DisplayAsIcon:=False)

  Call Word.ActiveDocument.Save

Finally:
  Word.Quit
  Set Word = Nothing
End Sub

(Make sure that you change the path—c: empchart.doc—to reflect the location of Word documents located on your PC.) The New keyword creates a new instance of Word, even if Word is already open. The Open method of the Documents collection is used to open the existing file. The code then selects the end of the document, enters a new empty paragraph, and pastes the range. The document is then saved and the new instance of Word is closed.

Accessing an Active Word Document

Suppose you are working in Excel, creating a table. You also have Word open with a document active, into which you want to paste the table you are creating. You can copy the table from Excel to the document using the following code. In this example we are using early binding:

Public Sub CopyTableChartToOpenWordDocument()
  Dim Word As Word.Application

  ThisWorkbook.Sheets(“Table”).Range(“A1:B6”).Copy

  Set Word = GetObject(, “Word.Application”)
  Word.Selection.EndKey Unit:=wdStory
  Word.Selection.TypeParagraph
  Word.Selection.Paste

  Set Word = Nothing
End Sub

The GetObject function has two input parameters, both of which are optional. The first parameter can be used to specify a file to be opened. The second can be used to specify the application program to open. If you do not specify the first parameter, GetObject assumes you want to access a current instance of an object. If you specify a zero length string as the first parameter, GetObject assumes you want to open a new instance of Word.

You can use GetObject, with no first parameter, as in the preceding code, to access a current instance of Word that is in memory. However, if there is no current instance of Word running, GetObject with no first parameter causes a runtime error.

Creating a New Word Document

Suppose you want to use a current instance of Word if one exists, or if there is no current instance, you want to create one. In either case, you want to open a new document and paste the table into it. The following code shows how to do this. Again, we are using early binding:

Public Sub CopyTableToAnyWordDocument()
  Dim Word As Word.Application

  ThisWorkbook.Sheets(“Table”).Range(“A1:B6”).Copy
  On Error Resume Next
    ' Try to open an existing instance of Word
    Set Word = GetObject(, “Word.Application”)

    ' Open new instance if no running instance exists
    If Word Is Nothing Then
      Set Word = GetObject(“”, “Word.Application”)
End If

  On Error GoTo 0
  Word.Documents.Add
  Word.Visible = True

  Word.Selection.EndKey Unit:=wdStory
  Word.Selection.TypeParagraph
  Word.Selection.Paste

  Set Word = Nothing
End Sub

If there is no current instance of Word, using GetObject with no first argument causes a runtime error and the code then uses GetObject with a zero length string as the first argument, which opens a new instance of Word, and then creates a new document. The code also makes the new instance of Word visible, unlike our previous examples where the work was done behind the scenes without showing the Word window. The chart is then pasted at the end of the Word document. At the end of the procedure, the object variable Word is released, but the Word window is accessible on the screen so that you can view the result.

Access and DAO

If you want to copy data from Access to Excel, you can establish a reference to the Access object library and use the Access Object Model. You can also use ADO (ActiveX Data Objects), which is Microsoft's latest technology for programmatic access to relational databases, and many other forms of data storage. For examples of this, see Chapter 11.

Another simple and efficient way to get to Access data is provided by DAO (Data Access Objects). If you use Office 97, you will have DAO available but you might not have ADO, as ADO was released after Office 97. You can still use ADO with Excel 97, but the powerful CopyFromRecordset method, which is used in the following example, is not supported in Excel 97 for ADO recordsets. Here, we will show how to use DAO.

Figure 15-3 shows an Access table named Sales that is in an Access database file FruitSales.mdb.

Figure 15-3

The following code uses DAO to open a recordset based on the Sales table. It uses early binding, so a reference to the DAO object library is required:

Public Sub GetSalesDataViaDAO()
  Dim DAO As DAO.DBEngine
  Dim Sales As DAO.Database
  Dim SalesRecordset As DAO.Recordset
  Dim I As Integer
  Dim Worksheet As Worksheet
  Dim Count As Integer

  Set DAO = New DAO.DBEngine

  Set Sales = DAO.OpenDatabase(ThisWorkbook.Path + “ FruitSales.mdb”)
  Set SalesRecordset = Sales.OpenRecordset(“Sales”)
  Set Worksheet = Worksheets.Add
  Count = SalesRecordset.Fields.Count
  For I = 0 To Count - 1
    Worksheet.Cells(1,    I + 1).Value = SalesRecordset.Fields(I).Name
  Next

  Worksheet.Range(“A2”).CopyFromRecordset SalesRecordset

  Worksheet.Columns(“B”).NumberFormat = “mmm dd, yyyy”
  With Worksheet.Range(“A1”).Resize(1, Count)
    .Font.Bold = True
    .EntireColumn.AutoFit
  End With

  Set SalesRecordset = Nothing
  Set Sales = Nothing
  Set DAO = Nothing
End Sub

The code opens the Access database file, creates a recordset based on the Sales table, and assigns a reference to the recordset to SalesRecordset. A new worksheet is added to the Excel workbook, and the field names in SalesRecordset are assigned to the first row of the new worksheet. The code uses the CopyFromRecordSet method of the Range object to copy the records in SalesRecordset to the worksheet, starting in cell A2. CopyFromRecordSet is a very fast way to copy the data compared to a looping procedure that copies record by record.

Access, Excel and, Outlook

As a final example of integrating different Office applications, we will extract some data from Access, chart it using Excel, and e-mail the chart using Outlook. The code has been set up as four procedures. The first procedure is a sub procedure named EmailChart that establishes the operating parameters and executes the other three procedures. Note that the code uses early binding and you need to create references to the DAO and Outlook object libraries:

Public Sub EmailChart()
  Dim SQL As String
  Dim Range As Excel.Range
  Dim FileName As String
  Dim Recipient As String

  SQL = “SELECT Product, Sum(Revenue)”
  SQL = SQL & “ FROM Sales”
  SQL = SQL & “ WHERE Date>=#1/1/2004# and Date<#1/1/2005#”
  SQL = SQL & “ GROUP BY Product;”

  FileName = ThisWorkbook.Path + “Chart.xls”
  Recipient = “[email protected]”

  Set Range = GetSalesData(SQL)
  ChartData Range, FileName

  Call SendEmail(Recipient, FileName)
End Sub

SQL is used to hold a string that is a SQL (Structured Query Language) command. SQL is covered in more detail in Chapter 11. In this case, the SQL specifies that we want to select the unique product names and the sum of the revenues for each product from our Access database Sales table for all dates in the year 2000. FileName defines the path and filename that will be used to hold the chart workbook. Recipient holds the e-mail address of the person we are sending the chart to.

The code then executes the GetSalesData function that is listed next. The function accepts the SQL statement as an input parameter and returns a reference to the range containing the extracted data, which is assigned to Range. The ChartData sub procedure is then executed, passing in the data range, as well as the path and filename for the chart workbook. Finally, the SendEMail sub procedure is executed, passing in the recipient's e-mail address and the location of the chart workbook to be attached to the e-mail:

Public Function GetSalesData(ByVal SQL As String) As Excel.Range
  Dim DAO As DAO.DBEngine
  Dim Sales As DAO.Database
  Dim SalesRecordset As DAO.Recordset
  Set DAO = New DAO.DBEngine

  Set Sales = DAO.OpenDatabase _
      (ThisWorkbook.Path + “FruitSales.mdb”)

  Set SalesRecordset = Sales.OpenRecordset(SQL)

  With Worksheets(“Data”)
     .Cells.Clear
     With .Range(“A1”)
       .CopyFromRecordset SalesRecordset
       Set GetSalesData = .CurrentRegion
     End With
  End With

  Set SalesRecordset = Nothing
  Set Sales = Nothing
  Set DAO = Nothing
End Function

The GetSalesData function is similar to the GetSalesDataViaDAO sub procedure presented earlier. Instead of getting the entire Sales table from the database, it uses SQL to be more selective. It clears the worksheet named Data and copies the selected data to a range starting in A1. It does not add the field names to the worksheet, just the product names and total revenue. It uses the CurrentRegion property to obtain a reference to all the extracted data and assigns the reference to the return value of the function:

Public Sub ChartData(ByVal Range As Range, ByVal FileName As String)
  With Workbooks.Add
    With .Charts.Add
      With .SeriesCollection.NewSeries
        .XValues = Range.Columns(1).Value
        .Values = Range.Columns(2).Value
      End With
      .HasLegend = False
      .HasTitle = True
      .ChartTitle.Text = “Year 20 00 Revenue”
    End With
    Application.DisplayAlerts = False
    .SaveAs FileName
    Application.DisplayAlerts = True
    .Close
  End With
End Sub

ChartData has input parameters to define the range containing the data to be charted and the destination for the file it creates. It creates a new workbook and adds a chart sheet to it. It creates a new series in the chart and assigns the values from the data range as arrays to the axes of the series. DisplayAlerts is set to False to prevent a warning if it overwrites an old file of the same name.

When Is a Virus not a Virus?

The following version of SendEmail, created in all innocence, was identified as a virus by Norton AntiVirus. In fact, this identification of the code as the X97.OutlookWorm.Gen virus, set us back a few hours. When Excel was closed and the work saved, Norton AntiVirus informed us that it had found a virus in the workbook and had eliminated the problem. It had deleted all the modules from the workbook. We had to turn off the AutoProtect option in Norton and start again.

Here is the code that caused the problem:

Public Sub SendEmail(ByVal Recipient As String, ByVal Attachment As String)
  Dim Outlook As Outlook.Application
  Dim NameSpace As Outlook.NameSpace
  Dim MailItem As Outlook.MailItem

  Set Outlook = New Outlook.Application
  Set MailItem = Outlook.CreateItem(olMailItem)
  With MailItem
    .Subject = “Year 2004 Revenue Chart”
    .Recipients.Add Recipient
    .Body = “Workbook with chart attached”

    '.Attachments.Add Attachment
    .Send
  End With

  Set MailItem = Nothing
  Set Outlook = Nothing
End Sub

After some trial and error, we came up with the following code that is not identified as a virus. It escapes identification both with and without a reference to the Outlook object library. As it uses late binding, it does not require a reference to Outlook:

Public Sub SendEmail2(ByVal Recipient As String, ByVal Attachment As String)
  Dim Outlook As Object
  Dim NameSpace As Object
  Dim MailItem As Object
  Set Outlook = CreateObject(“Outlook.Application”)
  Set MailItem = Outlook.CreateItem(0)

  With MailItem
    .Subject = “Year 2004 Revenue Chart”
    .Recipients.Add Recipient
    .Body = “Workbook with chart attached”
    .Attachments.Add Attachment
    .Send
  End With
End Sub

SendEMail has input parameters for the e-mail address of the recipient and the filename of the attachment for the e-mail. If your Outlook configuration requires you to logon, you will need to uncomment the lines that get a reference to the Namespace and supply the username and password. A new mail item is created using the CreateItem method. Text is added for the subject line and the body of the e-mail and the recipient and attachment are specified. The Send method sends the e-mail.

You will need to respond to three dialog boxes when executing this code in Office XP. The first two warn you that Outlook is being accessed and the second forces a five second delay and warns you that a program is sending an e-mail. The techniques you are using, while being very useful to achieve legitimate ends, are also, obviously, employed by virus writers.

While Office 2003 has strong protection against e-mail viruses, earlier versions of Office are more vulnerable. Patches are available from Microsoft to add protection to earlier versions of Outlook, but they might make it impossible to send e-mail programmatically. It can be very difficult to allow the legitimate use of programmatically generated e-mail and prevent viruses doing the same thing. The best answer is to have the latest virus protection software installed and keep it up-to-date.

Summary

To automate the objects in another application, you create an object variable referring to the target application or an object in the application. You can use early binding or late binding to establish the link between VBA and the other application's objects. Early binding requires that you establish a reference to the target application's type library and you must declare any object variables that refer to the target objects using their correct type. If you declare the object variables as the generic Object type, VBA uses late binding.

Early binding produces code that executes faster than late binding and you can get information on the target applications objects using the Object Browser and the shortcut tips that automatically appear as you type your code. Syntax and type checking is also performed as you code, so you are less likely to get errors when the code executes than with late binding where these checks cannot be done until the code is run.

You must use the CreateObject or GetObject function to create an object variable reference to the target application when using late binding. You can use the same functions when early binding, but it is more efficient to use the New keyword. However, if you want to test for an open instance of another application at runtime, GetObject can be usefully employed with early binding.

The techniques presented in this chapter allow you to create powerful programs that seamlessly tap into the unique abilities of different products. The user remains in a familiar environment such as Excel while the code ranges across any product that has a type library and exposes its objects to VBA.

You need to be aware that virus writers can use the information presented here to wreak havoc on unprotected systems. Make sure that your system is adequately covered.