Detecting if Excel file has been closed

Question

Hi StackOverflow, 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?

Answer 1

How about using the FindWindow API to see whether the app is still visible?

Answer 2

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 :(

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();

Answer 3

There 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'.

Answer 4

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.