Microsoft KB Archive/306683

= HOW TO: Run Office Macros by Using Automation from Visual C# .NET =

Article ID: 306683

Article Last Modified on 5/5/2006

-

APPLIES TO


 * Microsoft Visual C# .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 PowerPoint 2003
 * Microsoft Office Word 2003
 * Microsoft Office Excel 2003
 * Microsoft Visual C# .NET 2003 Standard Edition

-



This article was previously published under Q306683





For a Microsoft Visual Basic .NET version of this article, see 306682.

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

IN THIS TASK
SUMMARY
 * Create Office Documents That Contain Macros
 * Create the Visual C# .NET Automation Client
 * Run and Test the Automation Client
 * Troubleshooting

REFERENCES



SUMMARY
This step-by-step article describes how to call Office macros from a Visual C# .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 type String.

back to the top

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 in 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  Close the Visual Basic Editor, save the Word document, and then 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 then quit Access.</li></ol> </li></ol>

back to the top

Create the Visual C# .NET Automation Client
<ol> Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Under Project types click Visual C# Projects, then click Windows Application under Templates. 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 Office XP and have not already done so, Microsoft recommends that you download and then install the Microsoft Office XP Primary Interop Assemblies (PIAs). For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:

328912 INFO: Microsoft Office XP 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.</li></ol> </li> On the View menu, click ToolBox. Add a Combo Box control and a Button control to Form1.</li> Double-click Button1 to generate a definition for the Click event handler of the button.</li>  Paste the following code in the button1_Click handler: private void button1_Click(object sender, System.EventArgs e) { // Object for missing (or optional) arguments. object oMissing = System.Reflection.Missing.Value;

//Switch based on the user selection. switch (comboBox1.SelectedIndex) {       case 0: // Create an instance of Microsoft Access, make it visible, // and open Db1.mdb. Access.ApplicationClass oAccess = new Access.ApplicationClass; oAccess.Visible = true; oAccess.OpenCurrentDatabase(&quot;c:\\db1.mdb&quot;, false, &quot;&quot;);

// Run the macros. RunMacro(oAccess, new Object[]{&quot;DoKbTest&quot;}); RunMacro(oAccess, new Object[]{&quot;DoKbTestWithParameter&quot;,                           &quot;Hello from C# Client.&quot;});

// Quit Access and clean up. oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone); System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess); oAccess = null; break;

case 1: // Create an instance of Microsoft Excel, make it visible, // and open Book1.xls. Excel.ApplicationClass oExcel = new Excel.ApplicationClass; oExcel.Visible = true; Excel.Workbooks oBooks = oExcel.Workbooks; Excel._Workbook oBook = null; oBook = oBooks.Open(&quot;c:\\book1.xls&quot;, oMissing, oMissing,               oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,                 oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

// Run the macros. RunMacro(oExcel, new Object[]{&quot;DoKbTest&quot;}); RunMacro(oExcel, new Object[]{&quot;DoKbTestWithParameter&quot;,                          &quot;Hello from C# Client.&quot;});

// Quit Excel and clean up. oBook.Close(false, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook); oBook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks); oBooks = null; oExcel.Quit; System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel); oExcel = null;

break;

case 2:

// Create an instance of PowerPoint, make it visible, // and open Pres1.ppt. PowerPoint.ApplicationClass oPP = new PowerPoint.ApplicationClass; oPP.Visible = MsoTriState.msoTrue; PowerPoint.Presentations oPresSet = oPP.Presentations; PowerPoint._Presentation oPres = oPresSet.Open(&quot;c:\\pres1.ppt&quot;,               MsoTriState.msoFalse, MsoTriState.msoFalse,                 MsoTriState.msoTrue);

// Run the macros. RunMacro(oPP, new Object[]{&quot;'pres1.ppt'!DoKbTest&quot;}); RunMacro(oPP, new Object[]{&quot;'pres1.ppt'!DoKbTestWithParameter&quot;,                          &quot;Hello from C# Client.&quot;});

// Quit PowerPoint and clean up. oPres.Close; System.Runtime.InteropServices.Marshal.ReleaseComObject (oPres); oPres = null; System.Runtime.InteropServices.Marshal.ReleaseComObject (oPresSet); oPresSet = null; oPP.Quit; System.Runtime.InteropServices.Marshal.ReleaseComObject (oPP); oPP = null;

break; case 3:

// Create an instance of Word, make it visible, // and open Doc1.doc. Word.ApplicationClass oWord = new Word.ApplicationClass; oWord.Visible = true; Word.Documents oDocs = oWord.Documents; object oFile = &quot;c:\\doc1.doc&quot;;

// If the Microsoft Word 10.0 Object Library is referenced // use the following code. Word._Document oDoc = oDocs.Open(ref oFile, ref oMissing,               ref oMissing, ref oMissing, ref oMissing, ref oMissing,                ref oMissing, ref oMissing, ref oMissing, ref oMissing,                ref oMissing, ref oMissing, ref oMissing, ref oMissing,                ref oMissing);

// If the Microsoft Word 11.0 Object Library is referenced comment // the previous line of code and uncomment the following code. //Word._Document oDoc = oDocs.Open(ref oFile, ref oMissing,               //ref oMissing, ref oMissing, ref oMissing, ref oMissing,                //ref oMissing, ref oMissing, ref oMissing, ref oMissing,                //ref oMissing, ref oMissing, ref oMissing, ref oMissing,                //ref oMissing, ref oMissing);

// Run the macros. RunMacro(oWord, new Object[]{&quot;DoKbTest&quot;}); RunMacro(oWord, new Object[]{&quot;DoKbTestWithParameter&quot;,                          &quot;Hello from C# Client.&quot;});

// Quit Word and clean up. oDoc.Close(ref oMissing, ref oMissing, ref oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject (oDoc); oDoc = null; System.Runtime.InteropServices.Marshal.ReleaseComObject (oDocs); oDocs = null; oWord.Quit(ref oMissing, ref oMissing, ref oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject (oWord); oWord = null;

break;

}

GC.Collect;  //Garbage collection. }                   </li>  Add the following function after the button1_Click handler: private void RunMacro(object oApp, object[] oRunArgs) {   oApp.GetType.InvokeMember(&quot;Run&quot;,        System.Reflection.BindingFlags.Default |        System.Reflection.BindingFlags.InvokeMethod,        null, oApp, oRunArgs); }                   </li> <li>On the View menu, click Designer and then double-click Form1 to generate a definition for the Load event of the form.</li> <li> Paste the following code in the Form1_Load handler: private void Form1_Load(object sender, System.EventArgs e) { comboBox1.DropDownStyle = ComboBoxStyle.DropDownList; comboBox1.Items.AddRange(new object[]       {&quot;Access&quot;, &quot;Excel&quot;, &quot;PowerPoint&quot;, &quot;Word&quot;}); comboBox1.SelectedIndex = 0; }                   </li> <li> Scroll to the top of the code window, and then add the following lines of code to the end of the list of using directives: using System.Reflection; using Access = Microsoft.Office.Interop.Access; using Excel = Microsoft.Office.Interop.Excel; using PowerPoint = Microsoft.Office.Interop.PowerPoint; using Word = Microsoft.Office.Interop.Word; using Microsoft.Office.Core; </li></ol>

back to the top

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

back to the top

Troubleshooting
When you reference the Access 10.0 object library in a Visual C# .NET project, you may receive an error message that states that conversion of the library to a .NET assembly failed.

For additional information about how to resolve this error so that you can successfully reference the Access 10.0 object library, click the article number below to view the article in the Microsoft Knowledge Base:

317157 PRB: Errors When Referencing the Microsoft Access 10.0 Type Library

back to the top

<div class="references_section">