How do you replicate changes from one excel sheet to another in two separate excel apps?
- by incognick
This is all in C# .NET Excel Interop Automation for Office 2007.
Say you create two excel apps and open the same workbook for each application:
app = new Excel.ApplicationClass();
app2 = new Excel.ApplicationClass();
string fileLocation = "myBook.xslx";
workbook = app.Workbooks.Open(fileLocation,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook2 = app2.Workbooks.Open(fileLocation,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Now, I want to replicate any changes that occur in workbook2, into workbook. I figured out I can hook up the SheetChanged event to capture cell changes:
app.SheetChange += new Microsoft.Office.Interop.Excel.AppEvents_SheetChangeEventHandler(app_SheetChange);
void app_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
Excel.Worksheet sheetReadOnly = (Excel.Worksheet)Sh;
string changedRange = Target.get_Address(missing, missing,
Excel.XlReferenceStyle.xlA1, missing, missing);
Console.WriteLine("The value of " + sheetReadOnly.Name + ":" +
changedRange + " was changed to = " + Target.Value2);
Excel.Worksheet sheet = workbook.Worksheets[sheetReadOnly.Index] as Excel.Worksheet;
Excel.Range range = sheet.get_Range(changedRange, missing);
range.Value2 = Target.Value2;
}
How do you capture calculate changes? I can hook onto the calculate event but the only thing that is passed is the sheet, not the cells that were updated. I tried forcing an app.Calculate() or app.CalculateFullRebuild() but nothing updates in the other application. The change event does not get fired when formulas change (i.e. a slider control causes a SheetCalculate event and not a SheetChange event)
Is there a way to see what formulas were updated? Or is there an easier way to sync two workbooks programmatically in real time?
