Use automation to create an Excel macro in Visual C #. net
This document describes how to automatically run microsoft excel in Microsoft Visual C #. Net to create a new macroCommandbarButton associated. Procedure for creating a Visual C #. NET application example
| 1. |
Start Microsoft Visual Studio. NET. |
| 2. |
InFileClickNewAnd then clickProject. Select from Visual C # project typeWindows Applications. By default, form1 is created. |
| 3. |
AddMicrosoft Excel Object LibraryAndMicrosoft Visual Basic for Applications extension Library. To do this, follow these steps:
| A. |
InProjectClickAdd reference. |
| B. |
InComTab, findMicrosoft Excel Object LibraryAnd then clickSelect. Then findMicrosoft Visual Basic for Applications extension LibraryAnd clickSelect. Note:: Microsoft Office 2003 contains the master InterOP assembly (PIA ). Microsoft Office XP does not contain Pia, but you can download Pia. For additional information about Office XP Pia, click the following article number to view the article in the Microsoft Knowledge Base: 328912(Http://support.microsoft.com/kb/328912/) info: Microsoft Office xp pia for download |
| C. |
InAdd referenceIn the dialog box, clickOKTo accept your choice. |
|
| 4. |
InViewChooseToolboxTo display the toolbox, and then add a button to form1. |
| 5. |
Double-clickButton1. The code window is displayed.Button1OfClickEvent. Add the following code lines toUsingStatement: using Office = Microsoft.Office.Core;using VBIDE = Microsoft.Vbe.Interop;using Excel = Microsoft.Office.Interop.Excel;
|
| 6. |
In the code window private void button1_Click(object sender, System.EventArgs e){}
private void button1_Click(object sender, System.EventArgs e){ Excel.Application oExcel; Excel.Workbook oBook; VBIDE.VBComponent oModule; Office.CommandBar oCommandBar; Office.CommandBarButton oCommandBarButton; String sCode; Object oMissing = System.Reflection.Missing.Value; // Create an instance of Excel. oExcel = new Excel.Application(); // Add a workbook. oBook = oExcel.Workbooks.Add(oMissing); // Create a new VBA code module. oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule); sCode = "sub VBAMacro()/r/n" + " msgbox /"VBA Macro called/"/r/n" + "end sub"; // Add the VBA macro to the new code module. oModule.CodeModule.AddFromString(sCode); try { // Create a new toolbar and show it to the user. oCommandBar = oExcel.CommandBars.Add("VBAMacroCommandBar",oMissing, oMissing,/); oCommandBar.Visible = true; // Create a new button on the toolbar. oCommandBarButton = (Office.CommandBarButton) oCommandBar.Controls.Add( Office.MsoControlType.msoControlButton, oMissing, oMissing, oMissing, oMissing); // Assign a macro to the button. oCommandBarButton.OnAction = "VBAMacro"; // Set the caption of the button. oCommandBarButton.Caption = "Call VBAMacro"; // Set the icon on the button to a picture. oCommandBarButton.FaceId = 2151; } catch(Exception eCBError) { MessageBox.Show("VBAMacroCommandBar already exists.","Error"); } // Make Excel visible to the user. oExcel.Visible = true; // Set the UserControl property so Excel won't shut down. oExcel.UserControl = true; // Release the variables. oCommandBarButton = null; oCommandBar = null; oModule = null; oBook = null; oExcel = null; // Collect garbage. GC.Collect();}
|
| 7. |
Press F5 to generate and run the program. |
|
