Knowledge Base

HOW TO: Handle Events for Excel by Using Visual C# .NET

Article ID: 823981

Article Last Modified on 1/17/2007

APPLIES TO

Microsoft Office Excel 2003
Microsoft Visual C# .NET 2003 Standard Edition
Microsoft Visual C# .NET 2002 Standard Edition

IN THIS TASK

SUMMARY
REFERENCES

SUMMARY

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

back to the top

Overview of Event Handling

Visual C# .NET uses delegates to handle events from a Component Object Model (COM) server. Delegates are a new concept in Microsoft Visual Studio .NET. With COM events, a delegate is a special object that listens for events from the COM server and then forwards them to a Visual C# function. To use a delegate, you must create an instance of the object, and then add the instance of the object to the event that you want to listen to. Each event has a delegate that is specifically designed to translate the COM event (with native data types) to a standard Microsoft .NET call (with managed data types).

back to the top

Create the Visual C# .NET Automation Client

To use delegates to handle Excel events from an Automation client that is developed with Visual C# .NET, follow these steps:

Start Visual Studio .NET 2002 or Visual Studio .NET 2003. On the File menu, click New, and then click Project. Under Visual C# Projects, select Windows Application. Name the project XLEventTest, and then click OK.

By default, Form1 is created.
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 11.0 Object Library, and then click Select.
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.
In Solution Explorer, double-click Form1.cs to display the form in Design view.
On the View menu, click Toolbox to display the Toolbox, and then add one button to Form1. Change the Text property of the button to Start Excel.
Double-click Start Excel 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)
{
   StartExcelAndSinkEvents();
} 
```

Add the following code near the top of the file, but below the other using statements:

using System.Reflection;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

Add the following code to the Form1 class below the Click event handler from step 5:

 //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 StartExcelAndSinkEvents()
{
   //Start Excel, and then create a new workbook.
   xlApp = new Excel.Application();
   xlBook = xlApp.Workbooks.Add( Missing.Value );
   xlBook.Windows.get_Item(1).Caption = "XL Event Test";
   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 )
{
   //This is called when any cell on a worksheet is 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 the 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;
}

back to the top

Test the Code

Press CTRL+ALT+O to display the Output window.
Press F5 to build and then run the program.
On the form, click the Start Excel button.

The program starts Excel and then creates a workbook with three worksheets.

Add any data to cells on any of the worksheets.

Look at the Output window in Visual Studio to verify that the event handlers are called.

Quit Excel, and then close the form to end the debug session.

back to the top

Troubleshoot

When you compile the code, you may receive the following compiler error message:

Namespace already contains a definition for 'Excel'

You receive this error message if you do not have the Primary Interop Assembly (PIA) for Excel installed. To resolve this problem, follow these steps:

Run Microsoft Office setup, and then install the Excel PIA. In Office setup, the PIA appears as a component under Excel as .NET Programmability Support.
Open your project, remove the reference to the Excel interop assembly, and then repeat step 2 in the "Create the Visual C# .NET Automation Client" section of this article to correctly reference the PIA.

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

For additional information about this error message, click the following article number to view the 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 additional information, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx

For additional information about automating Excel from Visual C# .NET , click the following article numbers 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#

back to the top

Keywords: kbhowto KB823981

Microsoft KB Archive/823981

Contents

HOW TO: Handle Events for Excel by Using Visual C# .NET

IN THIS TASK

SUMMARY

Overview of Event Handling

Create the Visual C# .NET Automation Client

Test the Code

Troubleshoot

REFERENCES