CODEWITHSUNDEEP
c#excelautomation
Hoodahelll
Hoodahelll

Reputation: 31

Unable to close an Excel process within C# code

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

Upvotes: 1

Views: 962

Answers (3)

Compo
Compo

Reputation: 48

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!

Upvotes: 0

user2946806
user2946806

Reputation:

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.

Upvotes: 1

Aycan Yaşıt
Aycan Yaşıt

Reputation: 2104

Add this:

Marshal.ReleaseComObject(wbook);
Marshal.ReleaseComObject(excel);

Upvotes: 0

Related Questions