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

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.

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 in 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 then 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 then quit Access.

Create the Visual C# .NET Automation Client

1. 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.
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 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
   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.
3. On the View menu, click ToolBox. Add a Combo Box control and a Button control to Form1.
4. Double-click Button1 to generate a definition for the Click event handler of the button.
5. 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("c:\\db1.mdb", false, "");
   
   			// Run the macros.
   			RunMacro(oAccess, new Object[]{"DoKbTest"});
   			RunMacro(oAccess, new Object[]{"DoKbTestWithParameter",
                               "Hello from C# Client."});
   
   			// 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("c:\\book1.xls", oMissing, oMissing,
   				oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, 
   				oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
   
   			// Run the macros.
   			RunMacro(oExcel, new Object[]{"DoKbTest"});
   			RunMacro(oExcel, new Object[]{"DoKbTestWithParameter",
                              "Hello from C# Client."});
   
   			// 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("c:\\pres1.ppt",
   				MsoTriState.msoFalse, MsoTriState.msoFalse, 
   				MsoTriState.msoTrue);
   
   			// Run the macros.
   			RunMacro(oPP, new Object[]{"'pres1.ppt'!DoKbTest"});
   			RunMacro(oPP, new Object[]{"'pres1.ppt'!DoKbTestWithParameter",
                              "Hello from C# Client."});
   
   			// 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 = "c:\\doc1.doc";
   
   			// 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[]{"DoKbTest"});
   			RunMacro(oWord, new Object[]{"DoKbTestWithParameter",
                              "Hello from C# Client."});
   
   			// 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.
   }
   					

6. Add the following function after the button1_Click handler:

   private void RunMacro(object oApp, object[] oRunArgs)
   {
       oApp.GetType().InvokeMember("Run",
           System.Reflection.BindingFlags.Default |
           System.Reflection.BindingFlags.InvokeMethod,
           null, oApp, oRunArgs);
   }
   					

7. On the View menu, click Designer and then double-click Form1 to generate a definition for the Load event of the form.
8. 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[]
           {"Access", "Excel", "PowerPoint", "Word"});
       comboBox1.SelectedIndex = 0;
   }
   					

9. 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;
   					

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.

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:

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