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?
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.
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. :)
User contributions licensed under CC BY-SA 3.0