Interacting with Other Office Applications

---

Using VBA to access other Microsoft Office applications is similar to using code to work with objects within the code's host application. To begin, you create an object variable that points to the Application object representing the Office application that contains the objects you want to work with. It's preferable to use an early bound object variable by using the New keyword. Alternatively, you can choose to use the CreateObject function or the GetObject function to create an object variable.

When VBA code manipulates objects within the same application, the reference to the Application object is implicit. However, when you are automating another application, the reference to the Application object must be explicit.

Review the following examples to see the difference between how the Application object is referenced, implicitly and explicitly. The first procedure demonstrates how to create a new Excel workbook and how to reference the Application object implicitly. The second procedure demonstrates how to refer to the Microsoft Word application explicitly and to create a new document. For the second procedure to be executed successfully, the reference to the Microsoft Word Object Library must first be added to the Excel application using the Tools, References command in the Visual Basic Editor.

Sub CreateNewWorkbookFromExcel()
    Dim xlNew As Excel.Workbook
 
    Set xlNew = Workbooks.Add
    ActiveCell.Value = "Created " & Date
End Sub
 
Sub CreateWordDocumentFromExcel()
    Dim wdApp As Word.Application
    Dim docNew As Word.Document
 
    Set wdApp = New Word.Application
    Set docNew = wdApp.Documents.Add
    wdApp.Selection.TypeText "This file was created " & Date
        With docNew
        MsgBox "‘" & .Name & "‘ was created " & Date & "."
        .Close wdDoNotSaveChanges
    End With
    wdApp.Quit
    Set wdApp = Nothing
End Sub 

Typically, you'll create an object variable that refers to the Application object representing the application you want to access through automation. When the Application object has been referenced, you can include additional references to the object's child objects to navigate to the object or method you want to manipulate. Use the Set statement to assign object variables to child objects.

Note 

The top-level object is the Application object. The Application object contains other objects that you can only access if the Application object exists. The objects dependent on the Application objects, are often referred to as the child objects. The child objects may also have children of their own. For example, the Excel Workbook object is the child object to the Excel Application object, and the Worksheets object is the child object of the Excel Workbook object.

However, Microsoft Excel and Word make it possible to create a top-level reference to some child objects of the Application objects. Knowing this fact, you can rewrite the code for the CreateWordDocumentFromExcel procedure to start from a reference to a Word Document object.

Sub CreateWordDocumentFromExcel2()
    Dim docNew As Word.Document
 
    Set docNew = New Word.Document
    Set docNew = Documents.Add
    docNew.Application.Selection.TypeText "This file was created " & Date
    With docNew
        MsgBox "‘" & .Name & "‘ was created " & Date & "."
        .Close wdDoNotSaveChanges
    End With
    Set docNew = Nothing
End Sub

You can use the same theory in Excel using the Workbook object as your top-level reference. You can do this by using the Excel.Sheet class name to create the workbook or by using the Excel.Chart class name to create a workbook that contains a worksheet with an embedded Chart object and a worksheet that contains a default data set for the chart.

To create a Workbook object, you use the CreateObject function because the Excel.Sheet and Excel.Chart class names don't support the New keyword. The following example automates Excel, starting with a Workbook object that contains a single worksheet:

Dim wbkSheet As Excel.Workbook
Set wbkSheet = CreateObject("Excel.Sheet")

To automate Excel starting with the Workbook object that contains a worksheet with a chart and another worksheet, use the following code.

Dim wbkChart As Excel.Workbook
Set wbkChart = CreateObject("Excel.Chart")

When automating Excel starting from a Workbook object or automating Word starting from a Document object, an implicit reference is created to the Application object. If you need to access properties and methods of the Application object, use the appropriate Application property of the Document or Workbook objects. Using the Document or Workbook objects as top-level objects reduces the amount of code you have to write. In most cases your code will be easier to follow and more consistent than when you reference the Application object. Table 21-1 lists all the top-level Office objects that can be referenced and their class names.

Table 21-1: Top-Level Office Objects and Their Associated Class Names

Top-Level Office Object	Class Name
Access Application object	Access.Application
Excel Application object	Excel.Application
Excel Workbook object	Excel.Sheet Excel.Chart
FrontPage Application object	FrontPage.Application
Outlook Application object	Outlook.Application
PowerPoint Application object	PowerPoint.Application
Word Application object	Word.Application
Word Document object	Word.Document

Opening a Document in Word

If you want to open a file created using a different Office application than you are currently using, use the GetObject function to directly open the file. However, it's just as easy to open an instance of the application and open the file from the application.

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 at the end of the document. Because the code uses early binding, be sure to set up the reference to the Word Object Library first.

Note 

In the example that follows, be sure to replace the worksheet name and the filename to reference a file located on your computer. If you are referencing files that don't exist, an error will occur when you test this procedure.

Sub CopyFromExcelToWord()
    Dim wdApp As Word.Application
 
    ThisWorkbook.Sheets("Table").Range("A1:B6").Copy
    Set wdApp = New Word.Application
 
    With wdApp.Application
        .Documents.Open Filename:="C:\test.doc"
        With .Selection
            .EndKey unit:=wdStory
            .TypeParagraph
            .Paste
        End With
        .ActiveDocument.Save
        .Quit
    End With
    Set wdApp = Nothing
End Sub

Tip 

Debugging with Hidden Application Objects
When a procedure includes applications that aren't visible, you might run into memory issues when debugging your code. When a procedure opens a hidden copy of an application and the code is stopped before the Quit method has been executed, an instance of the application will continue to run in the background until it has been forced to close. Each time the procedure is run and stopped before the Quit method is executed, an additional copy of the application will be in memory. This can cause serious memory errors with your system, so be sure to force the hidden application to close by using the Task Manager.

The New keyword creates a new instance of Word, even if it is already open. The Open method of the Documents collection is used to open an 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.

Now that you can open an existing Word document and place Excel data into Word, consider the following scenario. The Garden Supply Company has the promotional schedule outlined in a document named Spring Promotion.doc. You need to enter the same information into Excel. The following procedure will open the Word document and will place a copy of the file contents into a new Excel workbook:

Sub CopyWordToExcel()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String
Dim tRange As Word.Range
Dim i As Long
Dim r As Long
    Workbooks.Add
    r = 3
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = GetObject("C:\GSC\Correspondence\Spring Promotion.doc")
    With wrdDoc
        For i = 1 To .Paragraphs.Count
            Set tRange = .Range(Start:=.Paragraphs(i).Range.Start, _
                End:=.Paragraphs(i).Range.End)
            tString = tRange.Text
            tString = Left(tString, Len(tString) - 1)
            ActiveSheet.Range("A" & r).Formula = tString
            r = r + 1
        Next i
    End With
    With Range("A1")
        .Formula = "File Contents of: " & wrdDoc.Name
        .Font.Italic = True
        .Font.Size = 18
    End With
    wrdDoc.Close
    wrdApp.Quit
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    ActiveWorkbook.Saved = True
End Sub

Inside Out
GetObject vs. CreateObject

The GetObject and the CreateObject functions both return references to an object, so how do you choose which one to use?

The CreateObject function is used to create a new instance of the application. Use this function if the application you need to reference is not currently open. If an instance is already running, a second instance of the application will be initiated. Consider the following example. The Garden Supply Company has an Excel Workbook that copies data into a Microsoft Word document for the monthly sales reports. To create a new instance of Microsoft Word, you should use the following code:

Set xlApp = CreateObject("Word.Application")

The GetObject function is used with an application that is already running or to start an application with a file already loaded. The following example starts Excel and opens Myfile.xls:

Set XLBook = GetObject("C:\Myfile.xls")

The object returned in XLBook is a reference to the Workbook object.

Accessing an Active Word Document

Now that you can open an Office application, what if you simply need to access a program that is already open? If you have a current instance of an application running, you don't need to create a new instance and use additional resources. You can activate the running application by using the GetObject function.

The following example uses early binding and the GetObject function to copy a table to an open Word document:

Note 

This function requires Microsoft Word to be open for the information to be pasted into the active document.

Sub CopyFromExcelToOpenWordDocument()
    Dim wdApp As Word.Application
 
    ThisWorkbook.Sheets("Table").Range("A1:B6").Copy
 
    Set wdApp = GetObject(, "Word.Application")
    With wdApp.Selection
        .EndKey unit:=wdStory
        .TypeParagraph
        .Paste
    End With
    Set wdApp = Nothing
End Sub

The GetObject function has two input parameters, both of which are optional. The first parameter specifies a file to be opened. The second parameter specifies the application used to open the file. If you don't specify the first parameter, the GetObject function assumes you want to access a currently open instance of Word. 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 the GetObject function, to access a current instance of Word that's in memory. But if there is no current instance of Word running, the GetObject function with no first parameter causes a run-time error.

Tip 

When any program is opened, it creates an instance of the application in the computer's memory. If the same application is opened multiple times, you will see multiple entries for the application in the Task Manager. For the best performance, it's preferable to use an open instance of an application, rather than creating a new instance.

The following example accomplishes the same task. However, the Word window is visible, and the text is inserted at the insertion point:

Sub AccessActivateApp()
    Application.ActivateMicrosoftApp xlMicrosoftWord
    Dim appWord As Word.Application
    Dim doc As Word.Document
 
    Set appWord = GetObject(, "Word.Application")
 
    appWord.Visible = True
    appWord.Activate
 
    Set doc = appWord.ActiveDocument
 
    appWord.ShowMe
 
    With doc
        doc.Activate
        doc.Application.Selection.TypeText "This file was created " & Date
    End With
End Sub  

Creating a New Word Document

There will be times when you want to create a new file instead of working with an existing Word document. To accomplish this task, you need to have an open instance of Word and then you'll create a new document.

The following example uses early binding, so the code to create a new document is the same as if you were creating a new document from Word. Before you test this procedure, add the Word Object Library to your procedure.

Sub CopyFromExcelToNewWordDocument()
    Dim wdApp As Word.Application
 
    ThisWorkbook.Sheets("Table").Range("A1:B6").Copy
 
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
 
    If wdApp Is Nothing Then
        Set wdApp = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
 
    With wdApp
        .Documents.Add
        .Visible = True
    End With
 
    With wdApp.Selection
        .EndKey unit:=wdStory
        .TypeParagraph
        .Paste
    End With
 
    Set wdApp = Nothing
End Sub 

If there isn't a current instance of Word, using the GetObject function with no first argument causes a run-time error. The On Error Resume Next line will allow the code to continue and use the value in the wdApp variable to determine if a new instance of the application will be opened. If the Word application is not loaded into memory, the code then uses the GetObject function with a zero-length string as the first argument, which opens a new instance of Word. Once the instance of Word has been identified, the procedure 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 copied data is then pasted at the end of the Word document. At the end of the procedure, the object variable wdApp is released, but the Word window is accessible on the screen so that you can view the result.

The CreateNewWordDoc procedure demonstrates how to create a new document, but rather than paste contents into the document the procedure enters the creation date of the file. The program window is also closed, and the file is saved as NewWordDoc.doc.

Sub CreateNewWordDoc() 
    Dim wrdApp As Word.Application
     Dim wrdDoc As Word.Document
     Dim i As Integer
     Set wrdApp = CreateObject("Word.Application" 
    wrdApp.Visible = True
     Set wrdDoc = wrdApp.Documents.Add
     With wrdDoc
         .Content.InsertAfter "This document was created " & Date & " " &                Time & "." 
        .Content.InsertParagraphAfter
         .Content.InsertParagraphAfter
         If Dir("C:\NewWordDoc.doc") <> "" Then 
            Kill "C:\NewWordDoc.doc"
         End If
         .SaveAs ("C:\NewWordDoc.doc") 
        .Close
     End With 
    wrdApp.Quit 
    Set wrdDoc = Nothing 
    Set wrdApp = Nothing
 End Sub

Controlling Excel from Other Office Applications

Now to go full circle, you should know how to reference the Excel Application object from other Office applications. The same concepts apply; you must start by adding the Excel Object Library to the procedure of the host application, if using early binding. Then you must create an Excel object as outlined in Table 21-2.

Table 21-2: Excel Objects and Their Functions

Top-Level Office Object	Class Name
Application object	CreateObject('Excel.Application')
Workbook object	CreateObject('Excel.Sheet')
Chart object	CreateObject('Excel.Chart')

The following procedure was created in Word 2003. A reference was included to reference the Excel Object Library. To execute this procedure, you must add the Excel reference, enter the procedure into Word, and then create a new workbook in the root directory of your C: drive named NewExcelWbk.xls.

Sub OpenWriteExcelWbkContents()
 
 Dim xlApp As Excel.Application
 Dim xlWbk As Excel.Workbook
 Dim tString As String, r As Long
     Documents.Add 
    Set xlApp = CreateObject("Excel.Application") 
    Set xlWbk = Excel.Workbooks.Open("C:\NewExcelWbk.xls") 
    r = 1
    With xlWbk.Worksheets(1) 
        While .Cells(r, 1).Formula <> ""
             tString = Cells(r, 1).Formula
             With ActiveDocument.Content 
                .InsertAfter "Contents of file: " & xlWbk.Name
                 .InsertParagraphAfter
                 .InsertParagraphAfter 
                .InsertAfter tString
                 .InsertParagraphAfter 
            End With
             r = r + 1
         Wend
     End With
     xlWbk.Close
     xlApp.Quit 
    Set xlWbk = Nothing 
    Set xlApp = Nothing
 End Sub

The previous example created a basic Excel workbook. Now that you can create a basic workbook, you can copy data and a chart into a Word document. One such workbook is shown in Figure 21-5.

Figure 21-5: Here's a sample workbook, displaying the basic setup of data used to create the chart.

Tip 

Setup Requirements
The workbook requires some basic setup to run the procedure effectively. The procedure references the filename projection.xls; if you choose to use a different filename, modify your code to reflect the filename you've chosen. You need to define the range names: StartingValue and PctChange. Each month is calculated based on the previous month multiplied by the PctChange value. These values won't be saved in the Excel workbook before it's closed. The information used to create the chart has also been assigned the range name Data. Finally, create an embedded chart to see the full scope of this procedure.

The CreateExcelChart procedure prompts the user for two values and inserts the values into the worksheet. When the new data is entered into the worksheet the chart is automatically updated.

Sub CreateExcelChart()
    Dim XLSheet As Object
 
    Documents.Add
 
    StartVal = InputBox("Starting Value?")
    PctChange = InputBox("Percent Change?")
 
    WBook = "C:\GSC\Financial\projections.xls"
    Set XLSheet = GetObject(WBook, "Excel.Sheet").ActiveSheet
 
    XLSheet.Range("StartingValue") = StartVal
    XLSheet.Range("PctChange") = PctChange
    XLSheet.Calculate
 
    With Selection
        .Font.Size = 14
        .Font.Bold = True
        .TypeText "Monthly Increment: " & Format(PctChange, "0.0%")
        .TypeParagraph
        .TypeParagraph
    End With
 
    XLSheet.Range("data").Copy
    Selection.Paste
 
    XLSheet.ChartObjects(1).Copy
    Selection.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture, _
        Placement:=wdInLine, DisplayAsIcon:=False
    Set XLSheet = Nothing
End Sub

---