How do you replicate changes from one excel sheet to another in two separate excel apps?

Question

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?

Answer 1

I don't think there is any direct event which will tell you which cells are being calculated, as the events propogation is defaulted to false otherwise the calculate function will end into an infinite loop. Also note the sheet change triggers the automatic calculate. And calculate happens on the whole sheet not just the changes, to make sure there is any modification in the formula addresses. Hope this helps.

Answer 2

Hello All

In the event that two users edit the same cells in the Excel file, the second user who saves the workbook is presented with a pop-up question. This notifies the user that she has made changes to a cell altered by another user since her last save. She is given the option of keeping her own changes, and thus deleting the other user's, or deleting her recent changes. This is the common default practice when sharing Excel spreadsheets. However, this behavior may be turned off in the "Advanced" tab of the "Share Workbook" window.

Read more: How to Control Multiple Users on One Excel Spreadsheet | eHow.com http://www.ehow.com/how_5913825_control-users-one-excel-spreadsheet.html#ixzz0xcXAZvP1

Thanks & Regards

Ankit Moradiya