Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Create an Excel Macro by Using Automation from Visual C# .NET


View products that this article applies to.

Summary

This step-by-step article describes how to automate Microsoft Excel by using Microsoft Visual C# .NET to create a workbook that contains a new macro that is associated with a CommandBar button.

↑ Back to the top


More information

Steps to Create the Sample Visual C# .NET Application

  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, click New, and then click Project. Select Windows Application from the Visual C# Project types. Form1 is created by default.
  3. Add a reference to Microsoft Excel Object Library and Microsoft Visual Basic for Applications Extensibility Library. To do this, follow these steps:
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate the Microsoft Excel Object Library, and then click Select. Then locate the Microsoft Visual Basic for Applications Extensibility Library, and then click Select.

      Note Microsoft Office 2003 includes Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they can be downloaded. For additional information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:
      328912 INFO: Microsoft Office XP PIAs Are Available for Download
    3. Click OK in the Add References dialog box to accept your selections.
  4. On the View menu, select Toolbox to display the Toolbox, and add a button to Form1.
  5. Double-click Button1. The code window opens at the Click event for Button1. Add the following lines of code to the using statements at the top of the code window:
    using Office = Microsoft.Office.Core;
    using VBIDE = Microsoft.Vbe.Interop;
    using Excel = Microsoft.Office.Interop.Excel;
    					
  6. In the code window, replace the following code
    private void button1_Click(object sender, System.EventArgs e)
    {
    }
    					
    with:
    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 build and to run the program.
  8. Click Button1 to start Microsoft Excel, insert Microsoft Visual Basic for Applications (VBA) code, and then add a new CommandBar. Click the button on the CommandBar to run the VBA macro.

Additional Notes for Office XP

Microsoft Office 2003 and Microsoft Office XP applications have a security option to allow programmatic access to the VBA object model. If this setting is Off (the default), you may receive an error running the sample code. For additional information on this setting and how you can correct the error, click the following article number to view the article in the Microsoft Knowledge Base:
282830 PRB: Programmatic Access to Office XP VBA Project Is Denied

↑ Back to the top


References

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
303871 Create an Excel Macro Programmatically from Visual Basic .NET
194611 Create and Call an Excel Macro Programmatically from VB

↑ Back to the top


Keywords: KB303872, kbhowto, kbpia

↑ Back to the top

Article Info
Article ID : 303872
Revision : 11
Created on : 1/30/2007
Published on : 1/30/2007
Exists online : False
Views : 391