For Microsoft Visual Basic .NET versions of this article, see
CHS306682.
For Microsoft Visual C versions of this article, please see
CHS306686.
This task content
summary
Create an Office document containing macros to create a Visual C # .NET Automation client runs and tests automation client reference
Summary Use Microsoft Office Automation, you can open or create a document containing the Visual Basic for Applications (VBA) macro and execute the macro at runtime. This article describes how to call the Office macro from the Visual C # .NET Automation client. The following automation client samples operates the Office Automation Server (Access, Excel, PowerPoint or Word) based on your choice on the form. After the client starts the automation server, a document will open and then call two macros. The first macro is dokbtest, which has no parameters. The second macro is dokbtestwithParameter, only one
String type parameters.
Back to top
Create a Office document with macro
Create a Word document with a name C: /Doc1.doc. To create, follow these steps:
In Word, create a new document. Press the Alt F11 combination to open the Visual Basic Editor. In the Insert menu, click Module. Paste the following macro code into a 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 Close the Visual Basic Editor, save the Word document, and exit Word. Use the steps in creating a Word document to create an Excel workbook name C: /book1.xls. Use the steps in creating a Word document to create a PowerPoint presentation with a name C: /PRES1.PPT. Create a new ACCESS database named c: /db1.mdb. To do this, follow these steps:
On the Insert menu, click Module. Paste your macro code into this new module. Save the module and exit Access.
Back to top
Create a Visual C # .NET Automation client
Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Under Project Type, click Visual C # item, then click Windows Applications under Templates. Create Form1 by default. Add a reference to Access, Excel, PowerPoint, and Word object libraries. To do this, follow these steps:
On the project menu, click Add Reference. On the COM tab, find the Microsoft Word Object Library and click Select. Note: The Microsoft Word object library contains a version number; version used for Word 2000 is 9.0, and the version used for Word 2002 is 10.0. Repeat the previous step for Access, Excel and PowerPoint object library. In the Add Reference dialog box, click OK to accept your choice. If you prompt you to generate a package for the selected library, click Yes. On the View menu, click Toolbox. Add a ComboBox control to Form1 and a Button control. Double-click Button1 to generate a definition for the button's CLICK Event handler. Paste the following code to 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.Selected ")
{
Case 0:
// Create An Instance of Microsoft Access, Make It Visible,
// and open db1.mdb.
Access.applicationclass oaccess = new access.Applicationclass ();
Oaccess.visible = true;
// Access9:
Oaccess.opencurrentDatabase ("C: //db1.mdb", false);
// Access10:
///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 Obools = OEXCEL.WORKBOOKS;
Excel._Workbook Obook = NULL;
// Excel9:
Obook = OBOOKS.OPEN ("C: //Book1.xls", OMISSING, OMISING,
Omissing, Omissing, Omissing, Omissing, Omissing, Omissing, Omiissing, OMISSING
// Excel10:
// Obook = OBOOKS.OPEN ("C: //book1.xls", OMISING, OMISING,
// 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.presentation;
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'! DOKBTESTWITHPARETER", "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";
// Word9:
Word._Document Odoc = ODOCS.Open (Ref ofile, Ref Omiusing,
Ref omissing, ref omissing, ref omissing, ref omings,
Ref omissing, ref omissing, ref omissing, ref omings,
Ref omissing, ref omissing;
// Word10:
//Word._document odoc = odocs.open (Ref ofile, Ref Omiusing,
// 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.
}
Note: If you have written, this code uses Office 9.0 Object Library. When you add a reference to the project, if you select the Office 10.0 object library without selecting the Office 9.0 object library, search in the code containing "// access10", "// Excel10" and "// Word10". The comment for one or more lines of code referenced by this comment is then canceled. Similarly, search on the code containing "// access9", "// Excel9", "// Word9", and turn the one or more lines of code referenced by the comment to the comment line. : As written, this code uses Office 9.0 object library. When you add a reference to the project, if you select the Office 10.0 object library without selecting the Office 9.0 object library, search in the code containing "// access10", "// Excel10" and "// Word10". The comment for one or more lines of code referenced by this comment is then canceled. Similarly, search on the code containing "// access9", "// Excel9", "// Word9", and turn the one or more lines of code referenced by the comment to the comment line. Add the following function to 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;
}: If you have written, this code uses Office 9.0 object library. When you add a reference to the project, if you select the Office 10.0 object library without selecting the Office 9.0 object library, search in the code containing "// access10", "// Excel10" and "// Word10". The comment for one or more lines of code referenced by this comment is then canceled. Similarly, search on the code containing "// access9", "// Excel9", "// Word9", and turn the one or more lines of code referenced by the comment to the comment line. On the View menu, click the Designer, then double-click Form1 to generate a definition for the form's LOAD event. : As written, this code uses Office 9.0 object library. When you add a reference to the project, if you select the Office 10.0 object library without selecting the Office 9.0 object library, search in the code containing "// access10", "// Excel10" and "// Word10". The comment for one or more lines of code referenced by this comment is then canceled. Similarly, search on the code containing "// access9", "// Excel9", "// Word9", and turn the one or more lines of code referenced by the comment to the comment line. Add the following code to 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;
}
: As written, this code uses Office 9.0 object library. When you add a reference to the project, if you select the Office 10.0 object library without selecting the Office 9.0 object library, search in the code containing "// access10", "// Excel10" and "// Word10". The comment for one or more lines of code referenced by this comment is then canceled. Similarly, search on the code containing "// access9", "// Excel9", "// Word9", and turn the one or more lines of code referenced by the comment to the comment line. Scroll to the top of the code window, then add the following row to the USING instruction list: for office xp: use system.reflection; using microsoft.office.core; // office xp For Office 2000: using system.reflection;
Using office; // office 2000
Back to top
Run and test the automation client
Press the F5 key to run the app. Select Office Apps from ComboBox1, and then click Button1. Start your Selected Office app and run the DokbTest and DokbTestwithParameter.
Back to top
Refer to more information, see the Knowledge Base article below:
303872 HOWTO: CREATE An Excel Macro Using Automation from Visual C # .NET (from Visual C # .NET Using Automation to create an Excel Macro)
177760 VBA: How to Run Macros In Other Office Programs (How to Run Macro in other Office programs) For more information and resources for Office Automation, please visit the following Microsoft Web site:
Use Visual Studio to perform Microsoft Office development http://msdn.microsoft.com/library/techart/vsofficedev.htm Office Developing FAQs and Points http://support.microsoft.com/support/officeDev/
Back to top
The information in this article applies to:
Microsoft Visual C # .NET (2002) Microsoft Access 2000 Microsoft Excel 2000 Microsoft PowerPoint 2000 Microsoft Word 2000 Microsoft Access 2002 Microsoft Excel 2002 Microsoft PowerPoint 2002 Microsoft Word 2002
Recent Updated: 2003-10-24 (1.2) Keywords: _ik11561 Kbaccess Kbautomation Kbexcel KBGRPDSO KBHOWTO KBHOWTOMASTER KBWORD KB306683
