Share via


Controlling One Microsoft Office Application from Another

If you want to run code in one Microsoft Office application that works with the objects in another application, follow these steps.

  1. Set a reference to the other application's type library in the References dialog box (Tools menu). After you have done this, the objects, properties, and methods will show up in the Object Browser and the syntax will be checked at compile time. You can also get context-sensitive Help on them.
  2. Declare object variables that will refer to the objects in the other application as specific types. Make sure you qualify each type with the name of the application that is supplying the object. For example, the following statement declares a variable that points to a Microsoft Word document and another that refers to a Microsoft Excel workbook:
    
    

    Dim appWD As Word.Application, wbXL As Excel.Workbook

    Bb211352.vs_note(en-us,office.12).gif  Note
    You must follow the preceding steps if you want your code to be early bound.
    3. Use the **CreateObject** function with the [OLE Programmatic Identifiers](https://msdn.microsoft.com/en-us/library/Bb211384) of the object you want to work with in the other application, as shown in the following example. If you want to see the session of the other application, set the **Visible** property to **True**.
      Dim appWD As Word.Application
    

    Set appWD = CreateObject("Word.Application") appWd.Visible = True

    4. Apply properties and methods to the object contained in the variable. For example, the following instruction creates a new Word document.
      Dim appWD As Word.Application
    

    Set appWD = CreateObject("Word.Application") appWD.Documents.Add

    5. When you are done working with the other application, use the **Quit** method to close it, as shown in the following example.
      appWd.Quit
    
    MVP Logo The following example was provided by MVP Bill Jelen. Bill is the author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.

    This example creates a new Microsoft Office Word file for each row of data in a spreadsheet.

      ' You must pick Microsoft Word 12.0 Object Library from Tools>References
    ' in the VB editor to execute Word commands.
    Sub ControlWord()
        Dim appWD As Word.Application
        ' Create a new instance of Word & make it visible
        Set appWD = CreateObject("Word.Application.12")
        appWD.Visible = True
    
    'Find the last row with data in the spreadsheet
    FinalRow = Range("A9999").End(xlUp).Row
    For i = 1 To FinalRow
        ' Copy the current row
        Worksheets("Sheet1").Rows(i).Copy
        ' Tell Word to create a new document
        appWD.Documents.Add
        ' Tell Word to paste the contents of the clipboard into the new document
        appWD.Selection.Paste
        ' Save the new document with a sequential file name
        appWD.ActiveDocument.SaveAs Filename:="File" & i
        ' Close this new word document
        appWD.ActiveDocument.Close
    Next i
    ' Close the Word application
    appWD.Quit
    

    End Sub