Microsoft KB Archive/306682

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

Article ID: 306682

Article Last Modified on 6/29/2007

-

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 Standard Edition
 * Microsoft Office Access 2003
 * Microsoft Office Excel 2003
 * Microsoft Office PowerPoint 2003
 * Microsoft Office Word 2003

-



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.



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
 Create a Word document named C:\Doc1.doc. To do this, follow these steps:  In Word, create a new document. Press ALT+F11 to open the Visual Basic Editor. On the Insert menu, click Module.  Paste the following macro code into the new module: 'Display a message box that displays the application name. Public Sub DoKbTest MsgBox &quot;Hello from &quot; & 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 </li> Close the Visual Basic Editor, save the Word document, and quit Word.</li></ol> </li> Create an Excel workbook named C:\Book1.xls by using steps similar to those that you used to create the Word document.</li> Create a PowerPoint presentation named C:\Pres1.ppt by using steps similar to those that you used to create the Word document.</li> Create a new Access database named C:\Db1.mdb. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Insert menu, click Module.</li> Paste the macro code in the new module.</li> Save the module and quit Access.</li></ol> </li></ol>

Create the Visual Basic .NET Automation Client
<ol> 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.</li> Add a reference to the Access, Excel, PowerPoint, and Word object libraries. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Project menu, click Add Reference.</li> 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

</li> Repeat the previous step for the Access, Excel, and PowerPoint object libraries.</li> 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 &quot;Troubleshooting&quot; section.</li></ol> </li> On the View menu, click ToolBox. Add a combo box and a button to Form1.</li> Double-click Button1 to generate a definition for the button's Click event handler.</li>  Paste the following code in the Button1_Click procedure: Select Case ComboBox1.SelectedItem

Case &quot;Access&quot;

Dim oAccess As Access.ApplicationClass

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

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

'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 &quot;Excel&quot;

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

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

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

'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 &quot;PowerPoint&quot;

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

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

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

'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 &quot;Word&quot;

Dim oWord As Word.ApplicationClass

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

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

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

End Select

GC.Collect </li> <li>On the View menu, click Designer and double-click Form1 to generate a definition for the form's Load event.</li> <li> Paste the following code in the Form1_Load procedure: ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList Dim a As String = {&quot;Access&quot;, &quot;Excel&quot;, &quot;PowerPoint&quot;, &quot;Word&quot;} ComboBox1.Items.AddRange(a) ComboBox1.SelectedIndex = 0 </li> <li> 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 </li></ol>

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

<div class="references_section">