1

I'm aware of the many similar questions, raised here and I've gone through most of them without finding a solution to satisfy my curiosity (I think my case is a bit more specific).
I'm trying to populate a collection from the names of the Named Ranges of an Excel workbook within my WPF application. The outcome is an Excel process running even after my application has closed. I've localized the problem to the following piece of code. Without it, all works fine:

foreach (Excel.Name name in names)
{
      namedRanges.Add(name.Name);                            
}

So, obviously something here doesn't get released. I haven't found a way to close/dispose/quit the "Excel.Name" as with the other Excel objects. Please suggest how to do that. The workaround I'm using is killing the specific process, but I'd rather understand the problem behind this and try to come up with a more relevant solution. Here's the full code extract:

     Excel.Application excel = new Excel.Application();
     excel.Visible = false;
     Excel.Workbooks wbooks = excel.Workbooks;
     Excel.Workbook wbook = wbooks.Open(Proc.ExpenseFullPath);

     ObservableCollection<string> namedRanges = new ObservableCollection<string>();
     Excel.Names names = wbook.Names;

     foreach (Excel.Name name in names)
     {
          namedRanges.Add(name.Name);                            
     }                        

     wbook.Close();
     wbooks.Close();
     excel.Quit();

Edit: I found out how to make it work. I replaced the problematic "foreach" with:

for (int i = 1; i <= names.Count; i++)
{
     namedRanges.Add(names.Item(i).Name);
     Marshal.FinalReleaseComObject(names.Item(i));
}
Hoodahelll
  • 21
  • 1
  • 3
  • There's a COM iterator being used in your foreach loop. You can't get to it, excel.Quit() won't stop Excel from running until that iterator gets garbage-collected. Which certainly should happen when your program exits, that forces one last pass through all the finalizers. You'll need to find out why your program is not exiting normally. Don't use Debug + Stop Debugging, for example. – Hans Passant Nov 02 '13 at 14:10
  • Thanks for the idea. I had to take care of each Excel.Name because they're COM as well. Not sure why, but ReleaseComObject didn't work, while FinalReleaseComObject did work. – Hoodahelll Nov 02 '13 at 14:42

3 Answers3

1

Here is an excellent post about com object not getting released properly in situations like yours.

  1. Changed build mode of the project to "Release" (in DEBUG mode, COM objects have a hard time disposing of their references.
  2. Removed all double dot expressions (all COM objects should be tied to a variable so they can be released)
  3. Calling GC.Collect(), GC.WaitForPendingFinalizers(), and Marshal.FinalReleaseComObject() explicitly in a finally block

You might want to try this:

Marshal.ReleaseComObject(names);

Sorry, I could leave this as a comment as I don't have enough reputation yet.

Community
  • 1
  • 1
0

Add this:

Marshal.ReleaseComObject(wbook);
Marshal.ReleaseComObject(excel);
Aycan Yaşıt
  • 2,106
  • 4
  • 34
  • 40
0

I ran into similar issues a few years back and have used a couple of techniques to combat it. Our use case might be a bit different than yours - we had a long running service that spawned multiple excel instances to do work before (trying to) close them.

I found out about the Win32 jobs api from here http://www.xtremevbtalk.com/showpost.php?p=1335552&postcount=22, and pretty much copied the code verbatim. It registers the Excel instance to your host application's PID, and if your application dies then excel dies with it - even if the host crashes, we use it as follows:

uint pid = 0;
GetWindowThreadProcessId(new IntPtr(_excel.Hwnd), out pid);
_job = new Job();
_job.AddProcess(Process.GetProcessById((int) pid).Handle);

Secondly, we run a function called "NAR" against anything allocated by Excel:

private static void NAR(object o)
{
    if (o == null) return;
    try
    {
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o);                
    }
    catch { }
    finally
    {
        o = null;
    }
 }

And finally there is the garbage collection incantation which you'll want to perform as you're closing down:

private static void GcCleanup()
{
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
}

I can't recall why its done twice, I do recall there was a reason though. I can't profess that either of these techniques is a good / correct way of taming Excel, but it worked for me!

Compo
  • 48
  • 5