none
How do I detect the cell data change on the excel grid

    Question

  • Hi,
         I'm writing an application level add-in with VSTO 2005 SE (C#) for Excel 2007. On the add-in, I had programmatically create a worksheet with data from the database. After the data is populated, user can edit the changes on the excel grid. My question is how do I detect the cell data change? Please advice. Thanks.

    Monday, February 23, 2009 6:17 AM

All replies

  • there are several answers.

    • If you are using a ListObject it will fire an event for you.
    • If your change is within an Excel.NamedRange then that will fire a Change event. (Not an Native Excel Name Range)
    • If you aren't using either of the above, then you have to use the water cannon with wide spray... Worksheet Change event.
    Let me know if you need any code for the above.
    Monday, February 23, 2009 9:46 AM
  • Hi,
        I'm currently using the method below to populated data into the excel grid. How am I able to detect the cell data changed but the user? Is this method using the Excel.NamedRange that you mention?

    Microsoft.Office.Interop.Excel.Range rng = null;  
    DataTable dataTable = ldsetData.Tables[0];  
    Microsoft.Office.Interop.Excel.Worksheet sheetToAddTo = activeSheet;  
    //create the object to store the column names  
    object[,] columnNames;  
    columnNames = new object[1, dataTable.Columns.Count];  
    //add the columns names from the datatable  
    for (int i = 0; i < dataTable.Columns.Count; i++)  
    {  
    columnNames[0, i] = dataTable.Columns[i].ColumnName;  
    }  
    //get a range object that the columns will be added to  
    Microsoft.Office.Interop.Excel.Range columnsNamesRange = (Microsoft.Office.Interop.Excel.Range)sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1], sheetToAddTo.Cells[1, dataTable.Columns.Count]);  
    //a simple assignement allows the data to be transferred quickly  
    columnsNamesRange.Value2 = columnNames;  
    //release the columsn range object now it is finished with  
    columnsNamesRange = null;  
    //create the object to store the dataTable data  
     
    object[,] rowData;  
    rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];  
    //insert the data into the object[,]  
    for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)  
    {  
        for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)  
        {  
            rowData[iRow, iCol] = dataTable.Rows[iRow][iCol];  
        }  
    }  
    //get a range to add the table data into   
    //it is one row down to avoid the previously added columns  
    Microsoft.Office.Interop.Excel.Range dataCells = (Microsoft.Office.Interop.Excel.Range)sheetToAddTo.get_Range(sheetToAddTo.Cells[2, 1],sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);  
     
     
    //assign data to worksheet  
    dataCells.Value2 = rowData;  
     
    //release range  
    dataCells = null;  
     
    //return the range to the new data  
    rng = sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1], sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);  
       
     
    Tuesday, February 24, 2009 5:43 AM
  • if you create a NamedRange, and set it to rng.

    then add a change event to the NamedRange to fire off some code.

    you can then identify what was changed.

    eg
            private void addNamedRanges()  
            {  
                ExcelT.NamedRange anmdRange;  
                ExcelT.Worksheet asht;  
                ExcelT.ControlCollection cc;  
     
                asht = Globals.Sheet1;  
                cc = asht.Controls;  
                anmdRange = cc.AddNamedRange(asht.InnerObject.Range("a1"), "somenamedRange");  
     
                anmdRange.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(anmdRange_Change);  
     
            }  
     
            void anmdRange_Change(Microsoft.Office.Interop.Excel.Range Target)  
            {  
                MessageBox.Show(Target.Value2.ToString());  
            } 
    Tuesday, February 24, 2009 7:56 AM
  • Hi,
        I had tried it. The Target.Value2 did shows the updated new value on the cell. As I don't have the original value and the grid data could able be sorted, hence it's difficult for me to update the values back to the database. Any suggestion how can I handle this problem.

    Scenario:
     I'm writing an application level add-in with VSTO 2005 SE (C#) for Excel 2007. On the add-in, I had programmatically create a worksheet with data from the database. After the data is populated, user can edit the changes on the excel grid. Each time, After the user edit the changes, they can click on the button "Save" and the data is to be save back into the database. But as the data can be quite massive (about 20 columns by 200 rows of data) and user are allow to sort the data, how can I programmatically detect the cell that data change and update to database correspondingly? Thanks.
    Tuesday, February 24, 2009 8:19 AM
  • Hi, nice example. But I want to do the same for Excel 2003 Add-in (using VSTO 2005 SE) I'm getting exception while casting ExcelT.Worksheet to Excel.Worksheet.  I'm using VB.Net and I can't find Globals.Sheet1  only thing I see in my Globals is ThisAddIn.  Can you please help.  I want to add a NamedRange in my 2003 add-in programatically.

    Thanks in advance.
    Friday, February 19, 2010 8:30 PM
  • The real problem is:

    I'm working with excel 2003 add-in.  I want to capture the cell value when ever the value of that cell changes.  I'm using Application_SheetChange event in vb.net it works well when manually change the value of the cell and press enter/tab etc.  But when try copy a value from another cell (for example i'm copying value from $D$10 to $D$20 using =D10 in cell D20 then the sheet change event gets fired for only D10 and I never see the Target as D20.  How can solve this.  Please help.

    Friday, February 19, 2010 8:44 PM