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


The information in this article applies to:

  • Microsoft Visual C# .NET (2002)
  • Microsoft Access 2002
  • Microsoft Excel 2002
  • Microsoft PowerPoint 2002
  • Microsoft Word 2002
  • Microsoft Office Access 2003
  • Microsoft Office PowerPoint 2003
  • Microsoft Office Word 2003
  • Microsoft Office Excel 2003
  • Microsoft Visual C# .NET (2003)
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

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

  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.
back to the top

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

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

303872 HOWTO: Create an Excel Macro by Using Automation from Visual C# .NET

177760 VBA: 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://msdn.microsoft.com/library/en-us/odc_2003_ta/html/odc_ancVSTO.asp

FAQs and Highlights for Office Development
http://support.microsoft.com/ofd

back to the top
Modification Type:MajorLast Reviewed:5/5/2006
Keywords:kbAutomation kbHOWTOmaster KB306683 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.