Opening a saved book causes the current book to throw exceptions

Question

I am trying to open an Excel workbook for saving while keeping a link to the current workbook. The problem is that as soon as I open the saved book, the original throws an exception on access.

Here is a code snippet for demonstration. I put this in an event handler for the 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 the clock in the current , sheets and name variables, you can see that as soon as a newWorkbook is created, other variables raise access exceptions.

The exception 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 in a new instance of Excel. If I open Excel, close the first workbook and open a new one, it works great. This is only if I have a recently opened instance of Excel that is not working. I really don’t understand why this is so.

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

Answer 1

I think this may be the right behavior.

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

I assume that you experience the same behavior through your C # add-in.

Answer 2

This is the correct behavior described by "creamyegg"

Excel will cancel the temporary workbook if not in use. To get around this problem, you just need to use the sheet before opening another.

Find an empty cell and change it! Your book will remain, and you can do your business. :)