How to run Office macros by using Automation from Visual Basic .NET

For a Microsoft Visual C# .NET version of this article, see
306683 .
For a Microsoft Visual C++ version of this article, see
306686 .

Summary

This step-by-step article describes how to call Office macros from a Visual Basic .NET Automation client.

You can use Microsoft Office Automation to open a document or create a new document that contains a Visual Basic for Applications (VBA) macro and execute the macro at run time.

More Information

The following sample Automation client manipulates an Office Automation server (Access, Excel, PowerPoint, or Word) based on your selection on a form. After the client starts the Automation server, it opens a document and then calls two macros. The first macro, DoKbTest, has no parameters and the second macro, DoKbTestWithParameter, takes a single parameter of typeString.

Create office documents that contain macros

  1. Create a Word document named C:\Doc1.doc. To do this, follow these steps:
    1. In Word, create a new document.
    2. Press ALT+F11 to open the Visual Basic Editor.
    3. On the Insert menu, click Module.
    4. Paste the following macro code into the new module:
      'Display a message box that displays the application name.
      Public Sub DoKbTest()
      MsgBox "Hello from " & Application.Name
      End Sub

      'Display a message box with the string passed from the
      'Automation client.
      Public Sub DoKbTestWithParameter( sMsg As String )
      MsgBox sMsg
      End Sub
    5. Close the Visual Basic Editor, save the Word document, and quit Word.
  2. Create an Excel workbook named C:\Book1.xls by using steps similar to those that you used to create the Word document.
  3. Create a PowerPoint presentation named C:\Pres1.ppt by using steps similar to those that you used to create the Word document.
  4. Create a new Access database named C:\Db1.mdb. To do this, follow these steps:
    1. On the Insert menu, click Module.
    2. Paste the macro code in the new module.
    3. Save the module and quit Access.

Create the Visual Basic .NET Automation Client

  1. Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Select Windows Application from the Visual Basic Projects types. Form1 is created by default.
  2. Add a reference to the Access, Excel, PowerPoint, and Word object libraries. To do this, follow these steps:
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Word 10.0 Object Library or Microsoft Word 11.0 Object Library, and then click Select.

      Note If you are using Microsoft Office XP and you have not already done so, Microsoft recommends that you download and then install the Microsoft Office XP Primary Interop Assemblies (PIAs).
      For more information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:
      328912 Microsoft Office XP primary interop assemblies (PIAs) are available for download

    3. Repeat the previous step for the Access, Excel, and PowerPoint object libraries.
    4. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.

      Note If you receive an error message when you reference the Access 10.0 object library, see the "Troubleshooting" section.
  3. On the View menu, click ToolBox. Add a combo box and a button to Form1.
  4. Double-click Button1 to generate a definition for the button's Click event handler.
  5. Paste the following code in the Button1_Click procedure:
    Select Case ComboBox1.SelectedItem

    Case "Access"

    Dim oAccess As Access.ApplicationClass

    'Start Access and open the database.
    oAccess = CreateObject("Access.Application")
    oAccess.Visible = True
    oAccess.OpenCurrentDatabase("c:\db1.mdb", False)

    'Run the macros.
    oAccess.Run ("DoKbTest")
    oAccess.Run("DoKbTestWithParameter", "Hello from VB .NET Client")

    'Clean-up: Quit Access without saving changes to the database.
    oAccess.DoCmd().Quit (Access.AcQuitOption.acQuitSaveNone)
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess)
    oAccess = Nothing

    Case "Excel"

    Dim oExcel As Excel.ApplicationClass
    Dim oBook As Excel.WorkbookClass
    Dim oBooks As Excel.Workbooks

    'Start Excel and open the workbook.
    oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    oBooks = oExcel.Workbooks
    oBook = oBooks.Open("c:\book1.xls")

    'Run the macros.
    oExcel.Run ("DoKbTest")
    oExcel.Run("DoKbTestWithParameter", "Hello from VB .NET Client")

    'Clean-up: Close the workbook and quit Excel.
    oBook.Close (False)
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook)
    oBook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks)
    oBooks = Nothing
    oExcel.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel)
    oExcel = Nothing

    Case "PowerPoint"

    Dim oPP As PowerPoint.ApplicationClass
    Dim oPresSet As PowerPoint.Presentations
    Dim oPres As PowerPoint.PresentationClass

    'Start PowerPoint and open the presentation.
    oPP = CreateObject("PowerPoint.Application")
    oPP.Visible = True
    oPresSet = oPP.Presentations
    oPres = oPresSet.Open("c:\pres1.ppt", , , True)

    'Run the macros.
    oPP.Run ("'pres1.ppt'!DoKbTest")
    oPP.Run("'pres1.ppt'!DoKbTestWithParameter", "Hello from VB .NET Client")

    'Clean-up: Close the presentation and quit PowerPoint.
    oPres.Close()
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oPres)
    oPres = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oPresSet)
    oPresSet = Nothing
    oPP.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oPP)
    oPP = Nothing

    Case "Word"

    Dim oWord As Word.ApplicationClass

    'Start Word and open the document.
    oWord = CreateObject("Word.Application")
    oWord.Visible = True
    oWord.Documents.Open ("C:\Doc1.doc")

    'Run the macros.
    oWord.Run ("DoKbTest")
    oWord.Run("DoKbTestWithParameter", "Hello from VB .NET Client")

    'Quit Word.
    oWord.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oWord)
    oWord = Nothing

    End Select

    GC.Collect()
  6. On the View menu, click Designer and double-click Form1 to generate a definition for the form's Load event.
  7. Paste the following code in the Form1_Load procedure:
            ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
    Dim a As String() = {"Access", "Excel", "PowerPoint", "Word"}
    ComboBox1.Items.AddRange(a)
    ComboBox1.SelectedIndex = 0
  8. Add the following code to the top of Form1.vb:
    Imports Access = Microsoft.Office.Interop.Access
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Word = Microsoft.Office.Interop.Word
    Imports PowerPoint = Microsoft.Office.Interop.PowerPoint

Run and test the Automation Client

  1. Press F5 to run the application.
  2. Select an Office application from ComboBox1, and then click Button1. The Office application that you selected is started and the DoKBTest and DoKBTestWithParameter macros are executed.

Troubleshooting

When you reference the Access 10.0 object library in a Visual Basic .NET project, you may receive an error message that states that conversion of the library to a .NET assembly failed.
For more information about how to resolve this problem so that you can successfully reference the Access 10.0 object library, click the following article number to view the article in the Microsoft Knowledge Base:
317157 PRB: Errors when you reference the Access 10.0 type library with Visual Studio .NET

References

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
303871 How to create an Excel macro by using Automation from Visual Basic .NET

177760 ACC97: How to run macros in other Office programs

For more information and resources about Office Automation, visit the following Microsoft Web sites:
Microsoft Office Development with Visual Studio
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx

FAQs and Highlights for Office Development
http://msdn2.microsoft.com/en-us/office/default.aspx
Properties

Article ID: 306682 - Last Review: Mar 23, 2009 - Revision: 1

Feedback