Prevent Excel from quitting

Question

Update: I still don't have a viable solution to this question, new proposals are welcome!

I'm missing an Excel.Application.Quit or an Excel.Application.BeforeQuit event. Does anybody know a workaround to mimic these events?

Given an Excel.Application object (COM Interop in C#), how can I:

1. Preferrably prevent Excel from quitting?
2. If this is not possible, how can I at least notice when Excel is quit?

Please note: Since I have a COM reference to the Excel.Application, the Excel process does not exit when Excel is "quit" by the user. Although this sounds contradictory, that's how it is. By "quit" I mean that the user hits "Quit" or the "cross button" at the top right corner of the window. The window closes, the files are unloaded, the add-ins are unloaded and whatever stuff Excel does apart from that which I have no clue of. But I can still use the Application object to "revive" the process and make Excel visible again, though the add-ins are then missing, and I have no certainty about what else is in an undefined state.

To get rid of this problem, I would like to either Cancel the Quit at the very start (Think of a BeforeQuit Cancel = true if it existed), or at least be notified when Excel is quit, so I can release the COM objects and make the process really exit, and next time I need Excel again, I will know that I need to start it up first.

Unfortunately it's a vicious circle: As long as Excel runs, I need the COM objects. So I can't dispose of them before Excel is quit. On the other hand, as long as the COM objects are there, the process doesn't exit even if Excel pretends to quit, so I cannot wait for a process exit event or similar.

I have the unpleasing feeling that I'm going to bash my head against a brick wall...

Answer 1

Why don't you just execute a System.Diagnostics.Process.Start(@"SomeWorkbook.xlsx"); to ensure that Excel is started. If it already has been started then this will not create a new process.

Answer 2

There is a KB article, How to automate Excel and then know the user closed it, in C++. I haven't ported this to C#, but it's likely not a lot of work.

Answer 3

It's a hack of course, but couldn't you use the Windows SetWindowsHookEx API with WH_SHELL or WH_CBT at least to get notified of the Excel's main window being destroyed?

NOTE: It certainly has security implications, i.e. need some admin rights to do cross-process magic.

Answer 4

The problem you are trying to solve here is not going to be solved by monitoring for program exit. Before you say that I am not answering your question, you state in the question that you are able to revive excel even after the user quits excel. Therefore the excel.exe process is still in play because you have a .net object with a com interop reference to excel.application.

So you have three options:

1. Avoid the user exiting Excel. As you have stated keep Excel from quitting, however I am unaware of a way that you can prevent the user from causing Excel to quit, thus as you have correctly noted unloading your and any other addins, etc. Bare in mind that Microsoft specifically design the user interaction this way, they want users to have the ability to close their apps. your addin needs to be able to deal with this, if it can't I'd say thats a problem with your addin not Excel. I might be wrong since i dont know enough about your apps requirements.

2. Cleanup all unmanaged resources BEFORE the user quits. What you need to do is cleanup your references to alll Excel and Office unmanaged resources before the user manually quits Excel so that when they do quit your application code is not left with any leftover resources that are now pointing to an instance of excel that no longer has addins etc loaded. Step (a) should be performed as you go, as soon as you no longer need a particular resource or even when reusing it for something else (i.e. a Excel.Range type) whereas step (b) should be used less often however if its a win app and not a addin, probably alot more frequently, it all depends on your app and the window of oppurtunity that you have (time) before the user is likely to complete there tasks an shutdown. Obviously with an addin you can just put it in the shutdown event, or arbitarily in your code.

   a. As noted by Otaku, use Marshal.FinalReleaseCOMObject on each unmanaged resource that is != null after use.

       if (ComObject != null)
       {
           Marshal.FinalReleaseComObject(ComObject);
           ComObject = null;
       }
   

   b. use the GC cleanup pattern for COM resources.

       GC.Collect();
       GC.WaitForPendingFinalizers();
       GC.Collect();
       GC.WaitForPendingFinalizers();
   

3. Reload addins If your not interested in fully tracking down and unloading all unmanaged resources due to the complexity of that task, time constraints (although I'd recommend it), you could look at reloading any required addins that you are presumably already aware of in your environment. This only works if you control the environment. there are techniques for loading both Excel and COM addins manually. As for other stuff, I'm not aware of that but perhaps its possible if you are using XLLs or maybe XLT in startup/XLSTART dirs but that would load anyways.

Answer 5

Please note that I haven't tried this.

Create a workbook which has code in it on BeforeClose.
for e.g.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
End Sub

Open this workbook alongwith other workbooks that you have & it doesn't have to be hidden (if the entire application is invisible).

So, if you try to quit the excel instance, it will force closing of this hidden workbook, which will raise its BeforeClose event & you can write code to stop it from closing.

Note that above code is in VB6 (VBA) and it will need converting into c#.
Post a comment, if you find any difficulty converting.

If you want to hide a workbook, you could do

Workbooks("my workbook").Windows(1).Visible = False 

Note: Workbook has a Windows collection. The code above tries to hide the 1st window.
I don't know, can a workbook have more than 1 window? if so, how?