0

I am using C# & VS2013 and VSTO. My code executes to perfection, but the one issue I have is that when I go to Task Manager and look at Details, Excel is still showing as running even after, I feel like I have properly cleaned up.

This is the syntax I use to cleanup and close all references to Excel. Just the references to Excel & VSTO, I left out the meat and gravy that does the leg work or this post would be to long.

Do I have something set-up improperly? Why does Excel still show up in Task Manager?

namespace ReleaseExcel
{
  public partial class Form12 : Form12
  {
    public static Excel.Worksheet newSeet;
    public static Excel._Worksheet currentsheet;
    public static Excel._Workbook oWB;
    public static Excel.Application xlApp;
    public static Excel.Workbook xlWorkBook;
    public static Excel.Worksheet xlWorkSheet;
    public static Excel.Workbook activeworkbook;

    public Form1()
    {
      InitializeComponents();
    }
    private void DoThis()
    {
        //Showing Declerations to Excel
        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //Do some more stuff here

        //Showing a few more Declerations to Excel
        currentsheet = (Excel._Worksheet)(xlWorkBook.ActiveSheet);
        Excel.Range last = currentsheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

        //Do a bit more stuff
        ClearExcel();
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) != 0) { }
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(currentsheet) != 0) { }
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) != 0) { }
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) != 0) { }
    }
    private static void ClearExcel()
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }      
  }
}

EDIT
I added in GC.Collect() & GC.WaitForPendingFinalizers() but now my syntax hangs on 

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) != 0) { }

It has been sitting on this line for about 5 minutes now, what causes the freeze?

EDIT # 2
Still no such luck. This is code

namespace ReleaseExcel
{
   public partial class Form12 : Form12
  {
    public static Excel.Worksheet newSeet;
    public static Excel._Worksheet currentsheet;
    public static Excel._Workbook oWB;
    public static Excel.Application xlApp;
    public static Excel.Workbook xlWorkBook;
    public static Excel.Worksheet xlWorkSheet;
    public static Excel.Workbook activeworkbook;

    public Form1()
    {
      InitializeComponents();
    }

    public void btnPush_Click(object sender, EventArgs e)
    {
        DoThis();
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
    private void DoThis()
    {
      //Showing Declerations to Excel
      xlApp = new Excel.Application();
      xlWorkBook = xlApp.Workbooks.Add(misValue);
      xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

      //Do some more stuff here

      //Showing a few more Declerations to Excel
      currentsheet = (Excel._Worksheet)(xlWorkBook.ActiveSheet);
      Excel.Range last = currentsheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

    }
  }
}
Michael Mormon
  • 539
  • 2
  • 8
  • 19
  • Don't forget `xlApp.Quit()` – stuartd Dec 21 '16 at 15:28
  • `last` needs cleanup, `xlApp.Workbooks` from `xlApp.Workbooks.Add(misValue);` needs a reference so you can clean it up, `(Excel.Worksheet)xlWorkBook.Worksheets` needs a reference so you can clean it up, and I'm going to assume `currentsheet.Cells` needs a reference so you can clean it up too. As the linked duplicate says nearly every "dot" call creates a com object so ~never use 2 dots, such as `xlApp.Workbooks.Add` which creates a hidden `Workbooks` COM object behind the Add call that you can't clean up without a reference. – Quantic Dec 21 '16 at 15:38
  • 2
    See what Hans Passant writes here, he explains it better than I could. http://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable – Equalsk Dec 21 '16 at 15:42
  • @Equalsk - so is Hans Passant saying that if I call GC.Collect(); & GC.WaitForPendingFinalizers(); - it will remove all references to Excel and no need to do it individually like I have listed above?-- I ask bc I tried to call just those 2 methods and Excel still listed in Task Manager – Michael Mormon Dec 21 '16 at 15:59
  • It's not just that, it's also about __where__ you put the code. In his example he wraps it in a helper method and calls GC after the method has exited. – Equalsk Dec 21 '16 at 16:04
  • @Equalsk - see my edit. I did the same thing, or at least I think I did. – Michael Mormon Dec 21 '16 at 16:07
  • Unfortunately I can't post an answer since the topic is on hold. The gist of it is to take out the `while...` lines and any other clearing up from `DoThis`. In your `Main` method whatever it is it should go 1. `DoThis();` 2. `GC.Collect();` 3. `GC.WaitForPendingFinalizers();` one after the other. The freeze is an infinite loop because the conditions can't be met. – Equalsk Dec 21 '16 at 16:09
  • 1
    Here's a DotNetFiddle to convey the idea. Obviously it won't run, it's in lieu of an answer: https://dotnetfiddle.net/gHQuXO – Equalsk Dec 21 '16 at 16:12
  • @Equalsk - this is a C# winforms app so there is no Main() method. I just have public Form1() – Michael Mormon Dec 21 '16 at 16:14
  • Well yes, I assume you have a `Form1_Load()` method which would be the equivalent. This code could just as well go in the `Click` event of a button. You decide. – Equalsk Dec 21 '16 at 16:18
  • @Equalsk - still leaving in Task Manager. See Edit # 2 for how I have setup syntax. – Michael Mormon Dec 21 '16 at 16:28
  • 1
    Might be a side effect of running it in the debugger. Does it work when your application is run outside of Visual Studio? Can be other factors like Excel addins. All I can tell you is that the code looks correct now, although you can remove `ClearExcel()` since it's never used. – Equalsk Dec 21 '16 at 16:37
  • Can I suggest you use "GC.Collect(GC.MaxGeneration, GCCollectionMode.Default, false);" instead of the regular Collect() function. And, seriously, I've never seen anyone wrap the ReleaseComObject() in a "while" before... – Mike Gledhill Dec 21 '16 at 16:39
  • @MikeGledhill - absolutely, I am up for suggestions to get my syntax functioning as it should! I did not know that was incorrecet to wrap the ReleaseComObject() I googled and found that solution. – Michael Mormon Dec 21 '16 at 16:41
  • @Equalsk - so strange. Running my .exe winform it does not leave the instance of Excel open, but running in Debug it does. I have never seen that before. – Michael Mormon Dec 21 '16 at 16:41
  • @MichaelMormon If you read the answer by Hans Passant that equalsk linked he says, "Which is fine, but does not work when you debug the code, a quirk that's explained in [this answer](http://stackoverflow.com/questions/17130382/understanding-garbage-collection-in-net/17131389#17131389)". – Quantic Dec 21 '16 at 16:45
  • @MichaelMormon OK cool, glad it works. Not sure you can do much about it getting stuck in the debugger, it's just par for the course. Glad I could help. – Equalsk Dec 21 '16 at 16:48

0 Answers0