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

Article translations Article translations
Article ID: 306682 - View products that this article applies to.
This article was previously published under Q306682
For a Microsoft Visual C# .NET version of this article, see 306683.
For a Microsoft Visual C++ version of this article, see 306686.
Expand all | Collapse all

On This Page

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: June 29, 2007 - Revision: 4.6
APPLIES TO
  • 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
Keywords: 
kbautomation kbhowto KB306682

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com