Detecting if Excel file has been closed
I've spent the past 3 hours trawling the web for answers to no avail, so I hope you can help me. I'm writing an application which automates Excel. The application has an option to "show/hide the excel sheet" so you can look at it, make any final changes and so forth.
Closing the application will naturally close the instance of Excel, however, there is a small chance that someone may exit out of Excel directly, without thinking. This breaks my application and I can't seem to find anyway of "checking if the same workbook is still open, and if not, re-opening it", before saving it
I've tried all sorts of things: checking if the Excel Application is null (when it's !=null it will save correctly, but when it "is" null (or at least, something other than !=null it won't even hit the breakpoint so I'm completely lost :(
Help please?
Edit: Thanks for all the replies so far, I'll reply to them in shortly.
AJ asked me to edit my question to provide a bit more information: I'm automating Excel using COM Interop from a C# application. The application allows the user to enter certain statistics which then get updated in Excel. There is a button which allows Excel to be shown/hidden, in case someone wants to check any other information in the sheet If someone exits Excel directly then it is still possible to use the show/hide button (it shows the Excel application with no workbook loaded) and the same Excel instance still shows up in Task Manager but when I click the "Save" button.
I added a try / catch in a slightly different place to last time (wasn't catching any errors last time, and now it catches two errors:
Exception from HRESULT: 0x800401A8
and
The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))
So basically it seems I need to "reconnect" the two again, although looking around on the web with the new error message doesn't seem's to suggest it could be a problem.
I'm wondering whether it would just be best to store all the values in strings (perhaps writing to a temp file sometimes in case of app failure) and then finally pushing them into Excel when the application is being closed?
Charlie7 Answers
Microsoft.Office.Interop.Excel.Application actually has an event that you use that gets fired just before the workbook gets closed. This gets fired 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();
}
JubaI've tried all sorts of things: checking if the Excel Application is null (when it's !=null it will save correctly, but when it "is" null (or at least, something other than !=null it won't even hit the breakpoint so I'm completely lost :(
The test could be done like so:
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:
if ( excelApp != null ) {
doSomething();
}
Then why not do this:
if ( excelApp == null ) {
startExcel();
addWorksheetToExcelInstance();
}
doSomething();
jcolebrandI just experienced and fixed this issue and although its too late on this thread, I just want to add my fix here, which may be helpful to someone.
Issue: I was closing the Excel application object prior to closing Excel Worksheet/Workbook. So, it detached the worksheet/workbook object from the excel app object.
Solution: Close excel worksheet/workbook prior to closing Excel Application object OR in order from worksheet to workbook to excel app.
Example: xlwks.Close(); xlwks = null; xlwbk = null; xlapp = null;
Chirag
josliberHow about using the FindWindow API to see whether the app is still visible?
AngryHackerThere is no good way (read: reliable + robust), but someone has come up with a reasonable workaround here on the msdn social site which might do the trick for you.
The article talks about using subclassing to hook the window close. It also references an article on the microsoft support site which talks more generally about subclassing from C# and .Net.
The only other alternative which I have employed is a pretty tacky workaround - I would allow Excel to be visible, but I put my own stay-on-top form over the title bar + buttons (not recommended).
Edit:
Just in case it is not clear - I am not recommending you create an add-in, but that a similar approach with subclassing is potentially feasible to influence the behavior of Excel while being automated. I make the assumption you are using interop to 'automate excel'.
AJ.Why do you need to reopen the workbook "before saving it?"
Wrap the save in a try...catch block -- if it works, you're good, and if it doesn't, you can handle it any way you see fit in the catch block.
JayReally late but this happens to me everytime I accidentally release the application before i release the actual workbook, so just release the workbook first
User contributions licensed under CC BY-SA 3.0