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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q306682
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Visual C# .NET version of this article, see 306683.
For a Microsoft Visual C++ version of this article, see 306686.
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.NameEnd Sub'Display a message box with the string passed from the'Automation client.Public Sub DoKbTestWithParameter( sMsg As String )   MsgBox sMsgEnd 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 = NothingEnd SelectGC.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.AccessImports Excel = Microsoft.Office.Interop.ExcelImports Word = Microsoft.Office.Interop.WordImports 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.


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
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

FAQs and Highlights for Office Development

Article ID: 306682 - Last Review: 12/06/2015 05:05:42 - Revision: 4.6

Microsoft Visual Basic .NET 2003 Standard Edition, Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Access 2002 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft PowerPoint 2002 Standard Edition, Microsoft Word 2002, Microsoft Office Access 2003, Microsoft Office Excel 2003, Microsoft Office PowerPoint 2003, Microsoft Office Word 2003

  • kbnosurvey kbarchive kbautomation kbhowto KB306682