Search code examples
c#excelexcel-interop

Excel side by side comparison with C# Excel Interop

Attempting to load two Excel workbooks side by side with C# using Excel interop.

I've tried a variety of ways, but I have been unable to get this to work. I've gotten it to work with Word documents.

Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook doc1 = ExcelApp.Workbooks.Open(fileToOpen.ToString());
Microsoft.Office.Interop.Excel.Workbook doc2 = ExcelApp.Workbooks.Open(file2ToOpen.ToString());
var result = doc1.Windows.CompareSideBySideWith(doc2.Windows[1].Caption);

Both workbooks are opening fine, but then I'm getting below exception on the CompareSideBySideWith method:

'Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))'

I think I have the wrong string for the parameter, but I can't figure out what to put for WindowName. I've tried the document name, sheet name, full path, with or without the extension - not sure what else to try.

Updated - revised working code, with assistance from Slai:

Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook doc1 = ExcelApp.Workbooks.Open(fileToOpen.ToString());
Microsoft.Office.Interop.Excel.Workbook doc2 = ExcelApp.Workbooks.Open(file2ToOpen.ToString());
doc1.Activate(); 
var result = ExcelApp.Windows.CompareSideBySideWith(doc2.Windows[1].Caption);
Solution

  • doc2.Windows is not in the doc1.Windows collection. ExcelApp.Windows can be used instead:

    var wb2 = ExcelApp.Workbooks.Open(file2ToOpen.ToString());
var wb1 = ExcelApp.Workbooks.Open(fileToOpen.ToString()); // wb1 is now active

bool result = ExcelApp.Windows.CompareSideBySideWith(wb2.Windows[1].Caption);

    The Record Macro feature can be used to compare the generated VBA code for hints.