Detect if Excel file is closed
I've spent the last 3 hours poisoning the internet for answers to no avail, so I hope you can help me. I am writing an application that automates Excel. The app has the ability to "show / hide excel sheet" so you can look at it, make any final changes, etc.
Closing the application will naturally close the Excel instance, however there is a small chance that someone could exit Excel directly without thinking. This breaks my application and I cannot find anywhere "to check if the same book is still open and if not, reopen it" before saving it
I've tried all sorts of things: checking if the Excel application is null (when it is! = Null it will be stored correctly, but when it is "equal" (or at least something other than! = Null it won ' t even hit a breakpoint, so I completely lost :(
Help me please?
Edit: Thanks for all the answers, I'll reply them shortly.
AJ asked me to modify my question to provide more information: I am automating Excel using COM Interop from a C # application. The application allows the user to enter certain statistics, which are then updated in Excel. There is a button that allows you to show / hide Excel in case someone wants to check any other information on the sheet. If someone exits Excel directly, then it is still possible to use the show / hide button (it shows the Excel application without the workbook loaded) and the same instance of Excel is still displayed in the Task Manager, but when I click the Save button ...
I added a try / trick in a slightly different place the last time (didn't catch any errors last time and now it is catching two errors:
Exception from HRESULT: 0x800401A8
and
The callee is disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))
So I feel like I need to "reconnect" the two again, although searching the web with a new error message doesn't mean it might be a problem.
I am wondering if it is better to store all the values in strings (maybe write a temporary file sometimes if the application crashes) and then finally insert them into Excel when the application exits?
a source to share
I've tried all sorts of things: checking the Excel app for null (when it's! = Null it will save right, but when it's "equal" (or at least anything other than! = Null it won't even hit the breakpoint, so that I am completely lost :(
The test can be done like this:
if ( excelApp == null) {
; //set breakpoint here during execution to see if it IS null
}
EDIT:
Type wheresExcel = typeof(excelApp); //this is going to execute if the object has not been GC'd
/ EDIT:
So your code looks like this:
if ( excelApp != null ) {
doSomething();
}
Then why not do this:
if ( excelApp == null ) {
startExcel();
addWorksheetToExcelInstance();
}
doSomething();
a source to share
Microsoft.Office.Interop.Excel.Application actually has an event that you are using that fires just before the workbook is closed. This works even if someone else closes the workbook in excel. The event is called WorkbookBeforeClose ().
m_app = new Microsoft.Office.Interop.Excel.Application() { Visible = false };
m_app.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_app_WorkbookBeforeClose);
m_workbook = m_app.Workbooks.Open(); // removing all arguements here for simplicity
// event handler code
void m_app_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
{
// making sure it is the same file that I opened
if (Wb.FullName == m_workbook.FullName)
{
m_workbook = null;
}
}
// when your app is closing
public void Close()
{
if (m_workbook != null)
{
m_workbook.Close(Type.Missing, Type.Missing, Type.Missing);
}
m_app.Quit();
}
a source to share
I just experienced and fixed this issue, and although it's too late in this thread, I just want to add my fix here, which might be helpful to someone.
Problem: I was closing the Excel application object before closing the Excel worksheet / workbook. So it decoupled the workbook / workbook object from the excel application object.
Solution: Close the excel / workbook worksheet before closing Excel Application, or in worksheet to workbook order for Excel application.
Example: xlwks.Close(); xlwks = null; xlwbk = null; xlapp = null;
a source to share
There is no good way (read: reliable + reliable), but someone came up with a sensible workaround here on the msdn social networking site that might do the trick for you.
This article talks about using a subclass to close a window. He also links to an article on the Microsoft support site that talks in more detail about subclassing from C # and .Net.
The only alternative I have used is a rather sticky workaround - I would let Excel be visible, but I put my own form to stay on top of the title + buttons (not recommended).
Edit:
It's unclear just in case - I don't recommend that you create an add-in, but a similar subclassing approach is potentially possible to influence Excel's behavior during automation. I am making the assumption that you are using interop to "automate excel".
a source to share