Excel worksheet change event is not firing

Question

I have created excel workbook using .NET interop. The excel workbook is created successfully through my C# code. When the user makes any changes in the excel, I want to do some stuff. I have used the ExcelWorkSheet.Change event. But this event is not firing. Here is my code-

using Excel = Microsoft.Office.Interop.Excel;    
public class xxx  
{   

    static Excel.Application xlApp;  
    static Excel.Workbook xlWorkBook;  
    static Excel.Worksheet xlWorkSheet;  
    static Excel.Worksheet xlWorkSheet1;
    static Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;   
    public static void ExportToExcel()
    {           
        xlApp = new Excel.ApplicationClass();
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlApp.Workbooks.Add(misValue); 
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);  
        ---------------- data is dumped to the excel here----------------  
        ((Microsoft.Office.Interop.Excel._Worksheet)xlWorkSheet).Activate();
        xlApp.EnableEvents = true;
        EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler(Worksheet_Change);  
        xlWorkSheet.Change += EventDel_CellsChange;
        xlWorkBook.SaveAs("D:\\Test.xlsx", Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);  
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();
        releaseObject(xlWorkSheet1);
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);  

        System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
        response.ClearContent();
        response.Clear();
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment; filename=Test.xlsx;");
        response.TransmitFile(("D:\\Test.xlsx");
        response.Flush();
        response.End();  
    }  

    public static void Worksheet_Change(Excel.Range Target)
    {
        try
        {
            xlApp.EnableEvents = false;                
            Excel.Range range = xlWorkSheet.get_Range("Y2");                
            range.Formula = "=A2";                
        }
        catch (Exception ex)
        {               
        }
        finally 
        {
            xlApp.EnableEvents = true;
        }
    }  
}    

No change is reflected in the Excel file when the user makes some changes. Please help me out. Thanks in advance

Answer 1

The Worksheet_Change event is not global - it only applies to that particular worksheet. In your code you wire up the event handler to the xlSheet1.Change event, then close the workbook and release all the Excel objects.

EDIT: I popped your code behind a Form and adapted it slightly. I could get the event to fire and the formula in cell Y2 is set. I'm not 100% sure of your circumstances, but try this code and then compare with your own. Hope it helps.

public partial class Form1 : Form
{
    private static Excel.Application xlApp;

    private static Excel.Workbook xlWorkBook;

    private static Excel.Worksheet xlWorkSheet;

    private static Excel.Worksheet xlWorkSheet1;

    private static Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        xlApp = new Excel.Application();
        xlApp.Visible = true;
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
        //---------------- data is dumped to the excel here----------------  
        ((Microsoft.Office.Interop.Excel._Worksheet)xlWorkSheet).Activate();
        xlApp.EnableEvents = true;
        EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler(Worksheet_Change);
        xlWorkSheet.Change += EventDel_CellsChange;   
    }

    public static void Worksheet_Change(Excel.Range Target)
    {
        try
        {
            xlApp.EnableEvents = false;
            Excel.Range range = xlWorkSheet.get_Range("Y2");
            range.Formula = "=A2";
        }
        catch (Exception ex)
        {
        }
        finally
        {
            xlApp.EnableEvents = true;
        }
    }
}