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: Handle Events for Excel by Using Visual C# .NET


View products that this article applies to.

Summary

This step-by-step article describes how to handle Excel events from an Automation client that is developed with Visual C# .NET.

Overview of Event Handling

Visual C# .NET uses delegates to handle Component Object Model (COM) events. Delegates are a new concept in Visual Studio .NET. In the case of COM events, a delegate listens for events from the COM server, and forwards them to a Visual C# function.

Create the Visual C# .NET Automation Client

The following steps demonstrate how to use delegates to handle Excel events from an Automation client that is developed with Visual C# .NET.
  1. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Under Visual C# Projects, select Windows Application. Form1 is created by default.
  2. Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel 10.0 Object Library and click Select.NOTE: If you have not already done so, Microsoft recommends that you download and 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. 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. In Solution Explorer, double-click Form1.cs to display the form in Design view.
  4. On the View menu, select Toolbox to display the Toolbox and add one button to Form1. Change the Text property of the button to Use Delegates.
  5. On the View menu, select Code to display the code window for the form. Add the following code to the Click event handler for the button:
    private void button1_Click(object sender, System.EventArgs e)
    {
       UseDelegates();
    }
    					
  6. Add the following code below the Click event handler for the button:
    //Excel Automation variables.
    Excel.Application xlApp;
    Excel.Workbook xlBook;
    Excel.Worksheet xlSheet1, xlSheet2, xlSheet3;
    
    //Excel event delegate variables.
    Excel.AppEvents_WorkbookBeforeCloseEventHandler EventDel_BeforeBookClose;
    Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;
    private void UseDelegates()
    {
       //Start Excel and create a new workbook.
       xlApp = new Excel.ApplicationClass();
       xlBook = xlApp.Workbooks.Add( Missing.Value );
       xlBook.Windows.get_Item(1).Caption = "Uses Delegate";
       xlSheet1 = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
       xlSheet2 = (Excel.Worksheet)xlBook.Worksheets.get_Item(2);
       xlSheet3 = (Excel.Worksheet)xlBook.Worksheets.get_Item(3);
       xlSheet1.Activate();
    
       //Add an event handler for the WorkbookBeforeClose Event of the
       //Application object.
       EventDel_BeforeBookClose = 
          new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose);
       xlApp.WorkbookBeforeClose += EventDel_BeforeBookClose;
    
       //Add an event handler for the Change event of both Worksheet objects.
       EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange);
    
       xlSheet1.Change += EventDel_CellsChange;
       xlSheet2.Change += EventDel_CellsChange;
       xlSheet3.Change += EventDel_CellsChange;
    
       //Make Excel visible and give the user control.
       xlApp.Visible = true;
       xlApp.UserControl = true;
    }
    
    private void CellsChange(Excel.Range Target )
    {
       //Called when a cell or cells on a worksheet are changed.
       Debug.WriteLine("Delegate: You Changed Cells " + 
          Target.get_Address( Missing.Value, Missing.Value, 
          Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value ) + 
          " on " + Target.Worksheet.Name);
    }
    
    private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel )
    {
       //This is called when you choose to close the workbook in Excel.
       //The event handlers are removed and then the workbook is closed 
       //without saving changes.
       Wb.Saved = true;
       Debug.WriteLine("Delegate: Closing the workbook and removing event handlers.");
       xlSheet1.Change -= EventDel_CellsChange;
       xlSheet2.Change -= EventDel_CellsChange;
       xlSheet3.Change -= EventDel_CellsChange;
       xlApp.WorkbookBeforeClose -= EventDel_BeforeBookClose;
    }      
    					
  7. Add the following code near the top of the file, below the other using declarations:
    using System.Reflection;
    using System.Diagnostics;
    using Excel = Microsoft.Office.Interop.Excel;
    					

Test the Code

  1. Press CTRL+ALT+O to display the Output window.
  2. Press F5 to build and run the program.
  3. On the form, click Use Delegate. The program starts Excel and creates a workbook with three worksheets.
  4. Add any data to cells on both worksheets. Examine the Output window in Visual Studio to verify that the event handlers are called.
  5. Quit Excel and close the form to end the debug session.

Troubleshooting

When you test the code, you may receive the following error message:
An unhandled exception of type 'System.InvalidCastException' occurred in interop.excel.dll

Additional information: No such interface supported
If you receive this error, see the following article in the Microsoft Knowledge Base:
316653 PRB: Error Using WithEvents or Delegates to Handle Excel Events from Visual Basic .NET or Visual C# .NET

↑ Back to the top


References

For more information, see the following Microsoft Developer Network (MSDN) Web site: For additional information about automating Excel from Visual C# .NET, click the article numbers below to view the articles in the Microsoft Knowledge Base:
302084 HOWTO: Automate Microsoft Excel from Microsoft Visual C# .NET
302096 HOWTO: Automate Excel With Visual C# .NET To Fill or Obtain Data In a Range Using Arrays
302902 HOWTO: Binding for Office Automation Servers with Visual C#


(c) Microsoft Corporation 2002, All Rights Reserved. Contributions by Joel Alley, Microsoft Corporation.

↑ Back to the top


Keywords: KB302815, kbhowtomaster, kbautomation

↑ Back to the top

Article Info
Article ID : 302815
Revision : 8
Created on : 6/29/2007
Published on : 6/29/2007
Exists online : False
Views : 353