Opening a saved workbook causes the current workbook to throw exceptions

Question

I'm trying to open a save Excel workbook while keeping a reference to the current workbook. The issue is that as soon as I open the saved workbook, the original throws an exception upon access.

Here's a code snippet to demonstrate. I put this in an event handler for a ribbon button to test it.

try
{
    string workbookPath = @"C:\Temp\Test.xlsx";
    Workbook current = Globals.ThisAddIn.Application.ActiveWorkbook;
    Workbook newWorkbook = Globals.ThisAddIn.Application.Workbooks.Open(workbookPath);

    current.Activate();                   // throws an exception
    Sheets sheets = current.Worksheets;   // throws an exception
    string name = current.Name;           // throws an exception
}
catch (Exception ex) {}

If you debug and put watches on the current, sheets and name variables you can see that as soon as newWorkbook is instantiated, the other variables thrown exceptions when accessed.

The exception that's thrown is

System.Runtime.InteropServices.COMException was caught
  Message=Exception from HRESULT: 0x800401A8
  Source=WorkbookTest
  ErrorCode=-2147221080
  StackTrace:
       at Microsoft.Office.Interop.Excel._Workbook.Activate()
       at WorkbookTest.Ribbon1.button1_Click(Object sender, RibbonControlEventArgs e) in C:\Temp\WorkbookTest\WorkbookTest\Ribbon1.cs:line 25
  InnerException: 

The strangest thing is that this only happens on a fresh instance of Excel. If I open Excel, close the first workbook and open a new one, it works just fine. It's only if I have a newly opened instance of Excel that this fails. I really don't understand why that is.

Does anyone know how to fix this? Am I doing something wrong here?

Answer 1

I think this might be the correct behaviour.

If you were to manually start a new Excel session (which automatically creates a new workbook [Book1]) and then, without doing anything to Book1 open an existing workbook, you'll notice that Book1 no longer exists in the Excel session.

I'm guessing you're experiencing the same behaviour via your C# add-in.

Answer 2

This is the correct behavior as described by 'creamyegg'

Excel will discard the temporary workbook if it is not used. To get around this problem you just need to use the sheet before opening another.

Find an empty cell and modify it! Your workbook will remain and you can go about your business. :)