C# interop: excel process not exiting after adding new worksheet to existing file

I've read many of the other threads here about managing COM references while using the .Net-Excel interop to make sure the Excel process exits correctly upon exit, and so far the techniques have been working very well, but I recently came across a problem when adding new worksheets to an existing workbook file.

The code below leaves a zombie Excel process.

If I add a worksheet to a newly created workbook file, it exits fine. If I run the code excluding the .Add() line, it exits fine. (The existing file I'm reading from is an empty file created by the commented out code)

Any ideas?

//using Excel = Microsoft.Office.Interop.Excel;
//using System.Runtime.InteropServices;
public static void AddTest()
{
  string filename = @"C:\addtest.xls";
  object m = Type.Missing;
  Excel.Application excelapp = new Excel.Application();
  if (excelapp == null) throw new Exception("Can't start Excel");
  Excel.Workbooks wbs = excelapp.Workbooks;

  //if I create a new file and then add a worksheet,
  //it will exit normally (i.e. if you uncomment the next two lines
  //and comment out the .Open() line below):
  //Excel.Workbook wb = wbs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
  //wb.SaveAs(filename, m, m, m, m, m, 
  //          Excel.XlSaveAsAccessMode.xlExclusive,
  //          m, m, m, m, m);

  //but if I open an existing file and add a worksheet,
  //it won't exit (leaves zombie excel processes)
  Excel.Workbook wb = wbs.Open(filename,
                               m, m, m, m, m, m,
                               Excel.XlPlatform.xlWindows,
                               m, m, m, m, m, m, m);

  Excel.Sheets sheets = wb.Worksheets;

  //This is the offending line:
  Excel.Worksheet wsnew = sheets.Add(m, m, m, m) as Excel.Worksheet; 

  //N.B. it doesn't help if I try specifying the parameters in Add() above

  wb.Save();
  wb.Close(m, m, m);

  //overkill to do GC so many times, but shows that doesn't fix it
  GC();
  //cleanup COM references
  //changing these all to FinalReleaseComObject doesn't help either
  while (Marshal.ReleaseComObject(wsnew) > 0) { } 
  wsnew = null;
  while (Marshal.ReleaseComObject(sheets) > 0) { }
  sheets = null;
  while (Marshal.ReleaseComObject(wb) > 0) { }
  wb = null;
  while (Marshal.ReleaseComObject(wbs) > 0) { }
  wbs = null;
  GC();
  excelapp.Quit();
  while (Marshal.ReleaseComObject(excelapp) > 0) { }
  excelapp = null;
  GC();
}

public static void GC()
{
  System.GC.Collect();
  System.GC.WaitForPendingFinalizers();
  System.GC.Collect();
  System.GC.WaitForPendingFinalizers();
}

From stackoverflow

• I don't have the code to hand, but I did run into a similar problem. If I recall correctly, I ended up retrieving the process id of the excel instance, and killing it (after a suitable wait period, and when the other method failed).

  I think I used:

  GetWindowThreadProcessId (via P/Invoke) on the excel object hwnd property to get the process id, and then used Process.GetProcessById to get a process object. Once I'd done that, I'd call Kill on the process.

  EDIT: I have to admit, this isn't the ideal solution, but if you can't find the rogue interface that isn't being released, then this will fix it in true eggshell/sledgehammer fashion. ;)

  EDIT2: You don't have to call Kill on the process object immediately... You could first try calling Close before resorting to Kill.

• Not very constructive I know but I tested the code exactly as shown above and my Excel process exits as expected, my C:\addtest.xls is sitting with 8 new sheets and no Excel process is running.
  Could the interop version be the cause I wonder? I tested with 11 & 12.

  yoyoyoyosef : hmm... fwiw I'm running v11

  Simon Wilson : Can you get other guys in your shop replicate it? I just had 2 other guys stop there work :) and try with the no orphaned processes.

  Simon Wilson : By "there" I meant "their", me English not gud

  yoyoyoyosef : yep, replicated here on a different machine. Both are using PIA dll version 11.0.0.0, one with Excel 2003 SP2 (11.6560.6568) installed, the other with Excel 2003 SP3 (11.8231.8221).

  Simon Wilson : Just a thought here. Ensure you kill al Excel process running then add a try{}finally{} block after Excel.Application excelapp = new Excel.Application() and stick the excelApp cleanup in he finally and see if it is still happening. I know...but give it a shot.

• I have done a similar thing. I create an Excel file or open an existing. I delete all the sheets and add my own. here is the code I use to ensure all references are closed:

              workbook.Close(true, null, null);
              excelApp.Quit();
  
              if (newSheet != null)
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
              }
              if (rangeSelection != null)
              {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeSelection);
              }
              if (sheets != null)
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
              }
              if (workbook != null)
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
              }
              if (excelApp != null)
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
              }
  
              newSheet = null;
              rangeSelection = null;
              sheets = null;
              workbook = null;
              excelApp = null;
  
              GC.Collect();
  

  I have tested this with many different options and not had it fail on me yet.

• I'm using VB.NET 3.5 SP1 and the following code STILL leaves EXCEL.EXE open:

          xlWorkbook.Close(SaveChanges:=False)
          xlApplication.Quit()
  
          System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange)
          System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet)
          System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets)
          System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)
          System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApplication)
  
          xlRange = Nothing
          xlWorksheet = Nothing
          xlSheets = Nothing
          xlWorkbook = Nothing
          xlApplication = Nothing
  
          GC.GetTotalMemory(False)
          GC.Collect()
          GC.WaitForPendingFinalizers()
  
          GC.Collect()
          GC.WaitForPendingFinalizers()
          GC.Collect()
          GC.GetTotalMemory(True)
  

  Anonymous Type : move this code GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() above the ReleaseCOMObject calls change the ReleaseCOMObject calls to FinalReleaseCOMObject calls

• Andrew, here is the code I've found that works. I thought I post post it here for others who come across:

  namespace WindowHandler
  {
  using System;
  using System.Text;
  using System.Collections;
  using System.Runtime.InteropServices;
  
  /// <summary>
  /// Window class for handling window stuff.
  /// This is really a hack and taken from Code Project and mutilated to this small thing.
  /// </summary>
  public class Window
  {
      /// <summary>
      /// Win32 API import for getting the process Id.
      /// The out param is the param we are after. I have no idea what the return value is.
      /// </summary>
      [DllImport("user32.dll")]
      private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId);
  
      /// <summary>
      /// Gets a Window's process Id.
      /// </summary>
      /// <param name="hWnd">Handle Id.</param>
      /// <returns>ID of the process.</returns>
      public static IntPtr GetWindowThreadProcessId(IntPtr hWnd)
      {
          IntPtr processId;
          IntPtr returnResult = GetWindowThreadProcessId(hWnd, out processId);
  
          return processId;
      }
  }
  }
  

• here's my full code to kill the Excel you created with the Office12 .Net interop library: Enjoy, -Alan.

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Runtime.InteropServices;
  using System.Diagnostics;
  using Microsoft.Office.Interop.Excel;
  
  class Program
  {
  
      /// <summary> 
      /// Win32 API import for getting the process Id. 
      /// The out param is the param we are after. I have no idea what the return value is. 
      /// </summary> 
      [DllImport("user32.dll")]
      private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId); 
  
      static void Main(string[] args)
      {
          var app = new Application();
          IntPtr hwnd = new IntPtr(app.Hwnd);
          IntPtr processId;
          IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
          Process proc = Process.GetProcessById(processId.ToInt32());
          proc.Kill(); // set breakpoint here and watch the Windows Task Manager kill this exact EXCEL.EXE
          app.Quit(); // should give you a "Sorry, I can't find this Excel session since you killed it" Exception.
      }
  }