-2

As far as I can see, this is not a duplicate question, as the question here is about why the accepted answers of seemingly duplicate questions, do not solve my issue in what appears to be the same circumstances.

For days, I have been struggling with my application failing to end instances of Excel, which have been opened using interops.

My application opens and closes Word applications just fine (i.e. the process disappears from task manager), but not Excel apps. Excel always remains open in the task manager. After much, much searching - this seems to be the definite guide on how to close Excel instances. However, if I copy and paste that exact example code - and run it as a console app, in either .NET Framework, or .NET 6 - Excel still fails to close, exactly as per the behaviour of my application.

Presumably, this must be an issue with the latest/newer versions of Word?

Completely and utterly lost on this one...

Code from the above link that leaves Excel open:

using System;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace TestCsCom
{
    class Program
    {
        static void Main(string[] args)
        {
            // NOTE: Don't call Excel objects in here... 
            //       Debugger would keep alive until end, preventing GC cleanup

            // Call a separate function that talks to Excel
            DoTheWork();

            // Now let the GC clean up (repeat, until no more)
            do
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            while (Marshal.AreComObjectsAvailableForCleanup());
        }

        static void DoTheWork()
        {
            Application app = new Application();
            Workbook book = app.Workbooks.Add();
            Worksheet worksheet = book.Worksheets["Sheet1"];
            app.Visible = true;
            for (int i = 1; i <= 10; i++) {
                worksheet.Cells.Range["A" + i].Value = "Hello";
            }
            book.Save();
            book.Close();
            app.Quit();

            // NOTE: No calls the Marshal.ReleaseComObject() are ever needed
        }
    }
}
jwilo
  • 152
  • 1
  • 9
  • Excel works just as well as Word. We can't debug code we cannot see. Please provide a [mre] that demonstrates the issue. – Ken White Dec 05 '22 at 01:38
  • @KenWhite Hi Ken, thanks. As referenced in my OP, the linked post (here: https://stackoverflow.com/questions/37904483/as-of-today-what-is-the-right-way-to-work-with-com-objects/38170605#38170605) contains the exact code, that when run on my machine, leaves an Excel instance open. – jwilo Dec 05 '22 at 01:55
  • `app.Workbooks.Add`, `worksheet.Cells.Range`, etc. IMHO the old "no two dots" rule is still valid. You should `Marshal.ReleaseComObject` every ole reference. The GC might try to release every object, but this can still cause issues with excel. And if all else fails, kill the process yourself. – Jeremy Lakeman Dec 05 '22 at 02:34
  • In this scenario, the Excel process is released when your application closes. You can accelerate the release of these COM objects simply setting to `null` each object you have declared. You can do it in any order, but if you do it in the reverse order of declaration, you cause the Excel Process to close immediately, otherwise it will persist a bit longer (and your do / while loop will iterate more times) -- You can actually do the same thing with `Marshal.ReleaseComObject()` + `Marshal.FinalReleaseComObject()` + `Marshal.CleanupUnusedObjectsInCurrentContext()`, but it's quite prone to fail – Jimi Dec 05 '22 at 06:14
  • @jimi - thanks for your feedback. However, this is exactly what I'm struggling with - Excel is **not** closed when my application closes. Even in this MRE, it seems like there is either a COM object that the GC is failing to collect (seems unlikely) or as Govert has said below, perhaps there must be a change in Excel itself. Especially given this same example works just fine for Word and Powerpoint. – jwilo Dec 05 '22 at 12:41
  • Depending on the Office version, this: `worksheet.Cells.Range["A" + i].Value = "Hello";` initializes a Range. In Office 16 it's not a problem. In Office 365 it could be. Note that you always need to test it running the executable, never in Visual Studio, even less in debug mode – Jimi Dec 05 '22 at 12:45
  • @HansPassant Hi Hans, I was hoping you would pop in here - having seen many on your posts on this topic. Unfortunately, with your suggestion - Excel still stubbornly refuses to close... and unfortunately in my 'real' application I also need the app visible to users. – jwilo Dec 05 '22 at 14:08
  • If working with `.xlsx` files you might consider using OpenXML libraries like [SpreadSheetLight](https://spreadsheetlight.com/) which do not have the release issue as with interop method to work with Excel. If interested I can post code for this method. – Karen Payne Dec 05 '22 at 14:15
  • @KarenPayne Do you have experience with SpeadSheetLight working with O365? The front page says up to 2013 Office. Would be interested in an alternative for sure! – jwilo Dec 05 '22 at 15:11
  • [ClosedXML](https://github.com/ClosedXML/ClosedXML) – Jimi Dec 05 '22 at 16:00

2 Answers2

0

It does seem a bit weird with the current version of Excel. It depends on how the console program is run. If I double-click in Windows Explorer, it closes perfectly like it used to. But if I run from a console window, then Excel stays open until the console window closes. Similarly when I run from the debugger, Excel only closes when the Debug window is closed and not when the driving process is terminated. It would be good if you can confirm this too. I suspect Excel has added some defensive code to not close in specific situations - maybe watching for a 'parent' window to close, or something similar.

Aaaaand then. . . . just as I try again after posting this, Excel starts closing beautifully every time, however I run it.

Govert
  • 16,387
  • 4
  • 60
  • 70
  • Whether I run a debug build through debugger, or run the debug built .exe from Windows explorer, Excel stays open still. If I build a 'release' executable, and run that - Excel still stays open. What version of Excel are you running, I'm on 2210 (Build 15726.20202 Click-to-Run). No idea what the Click-to-Run means... – jwilo Dec 05 '22 at 12:37
  • Same version. When I debug or run from command prompt, Excel stayed open until the commnad window closed. I did take out the `book.Save()` which was saving to OneDrive, and put `book.Close(saveChanges: false)`. Stuff was not very repeatable, and then came right !? – Govert Dec 05 '22 at 14:30
  • Hmm... to throw yet more confusion into the mix, I if I do as you suggest - and do not save the changes, the whole application dies for me with "Exception thrown: 'System.Runtime.InteropServices.COMException' in interopConsole.dll An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in interopConsole.dll The object invoked has disconnected from its clients. (0x80010108 (RPC_E_DISCONNECTED)) The program '[18400] interopConsole.exe' has exited with code 4294967295 (0xffffffff)." – jwilo Dec 05 '22 at 14:50
  • ...see my answer I just posted - you'll like this. Stumbled upon the solution by chance. Totally forgot I had this add-in enabled, and only when I saw it loading on the splash screen, and I thought "Excel is normally a little faster than this" did I think to disable it. – jwilo Dec 14 '22 at 13:08
0

OK, so I've stumbled across the solution to this. The solution is really rather an unrelated issue.

My instance of Excel had the Analysis Toolpak enabled (spelling mistake included!), from some work I was doing a few months back. The add-in is found at File --> Options --> Add-ins --> Analysis ToolPak.

I've disabled the add-in, and now Excel apps open and close just fine, using only explicit garbage collection, as expected.

I should add - the behaviour is still different to Word. Closed Word files kill Word almost instantly on closure of the document, even before the explicit GC. With Excel, I still have the close the form where the Workbook was opened from, which then calls the explicit GC, and sometimes it takes a few seconds, sometimes 30 seconds, sometimes it takes a minute - but having disabled the add-in, Excel does eventually get stopped.

jwilo
  • 152
  • 1
  • 9