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

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.

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:

For more information, click the following article number to view the article in the Microsoft Knowledge Base: For more information and resources about Office Automation, visit the following Microsoft Web sites: