How to properly close/dispose Excel COMs in C# ?(Excel process not closing)

Question

Yes, I know, it's allover the internet and probably here too, and I followed many examples and it STILL doesn't close, I'm seriously getting tired of this problem, please, if anyone could find a solution other than killing the excel process, please help me by sharing it!

My code:

            #region ////////// Declarations and initialisations //////////

            string[] sFilesPath = System.IO.Directory.GetFiles(Path); // Get all files from current Path

            MSExcel.Application xlApp = null;
            MSExcel.Workbook xlWorkBook = null;
            MSExcel.Workbooks xlWorkBooks = null;
            MSExcel.Sheets xlSheets = null;
            MSExcel.Shapes xlShapes = null;
            MSExcel.Worksheet xlWorkSheet = null;
            MSExcel.Worksheets xlWorkSheets = null;

            xlApp = new MSExcel.Application(); xlApp.Visible = false; xlApp.DisplayAlerts = false;
            xlWorkBooks = xlApp.Workbooks;
            xlWorkBook = xlWorkBooks.Open(Path + _xlNamesList[i] + ".xlsx");
            xlSheets = xlWorkBook.Sheets;

            #endregion \\\\\\\\\\ Declarations and initialisations \\\\\\\\\\

            #region ////////// Clear all previous WorkSheets //////////

            foreach (MSExcel.Worksheet Worksheet in xlSheets)
            {
                Worksheet.Cells.Clear();
                foreach (MSExcel.Shape sh in Worksheet.Shapes)
                {
                    sh.Delete();
                    Marshal.ReleaseComObject(sh);
                }
            }
            if (xlSheets != null) Marshal.ReleaseComObject(xlSheets);

            #endregion \\\\\\\\\\ Clear all previous WorkSheets \\\\\\\\\\

            #region ////////// Insert each screenshot at it's respective location //////////

            foreach (string File in sFilesPath)
            {
                string sFileExtension = System.IO.Path.GetExtension(File);
                if (sFileExtension == ".jpg") // Insert each jpg file in it's coresponding place
                {
                    //--->  Declarations and initialisations
                    string sFileNameWitouthExtension = System.IO.Path.GetFileNameWithoutExtension(File),
                           sShiftName = sFileNameWitouthExtension.Substring(sFileNameWitouthExtension.Length - 7),
                           sLineName = sFileNameWitouthExtension.Substring(0, sFileNameWitouthExtension.Length - 8);

                    xlWorkSheet = xlWorkBook.Worksheets[sLineName]; // Get the coresponding worksheet to edit based on LineName

                    //--->  Place the screenshot in the Excel file based on sShiftName
                    if (sShiftName == "Shift 1")
                    {
                        xlWorkSheet.Cells[1, 4] = sFileNameWitouthExtension;
                        xlWorkSheet.Cells[1, 4].Font.Size = 30;
                        xlWorkSheet.Cells[1, 4].Rows.AutoFit();
                        xlWorkSheet.Cells[1, 4].Columns.AutoFit();

                        xlWorkSheet.Shapes.AddPicture(File, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 40, 800, 500);
                    }
                    else
                        if (sShiftName == "Shift 2")
                        {
                            xlWorkSheet.Cells[40, 4] = sFileNameWitouthExtension;
                            xlWorkSheet.Cells[40, 4].Font.Size = 30;
                            xlWorkSheet.Cells[40, 4].Rows.AutoFit();
                            xlWorkSheet.Cells[40, 4].Columns.AutoFit();

                            xlWorkSheet.Shapes.AddPicture(File, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 650, 800, 500);
                        }
                        else
                            if (sShiftName == "Shift 3")
                            {
                                xlWorkSheet.Cells[78, 4] = sFileNameWitouthExtension;
                                xlWorkSheet.Cells[78, 4].Font.Size = 30;
                                xlWorkSheet.Cells[78, 4].Rows.AutoFit();
                                xlWorkSheet.Cells[78, 4].Columns.AutoFit();

                                xlWorkSheet.Shapes.AddPicture(File, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 1245, 800, 500);
                            }
                }
            }
            #endregion \\\\\\\\\\ Insert each screenshot at it's respective location \\\\\\\\\\

            #region ////////// Save EXCEL file and release/close objects //////////

            xlWorkBook.SaveAs(Path + _xlNamesList[i++] + ".xlsx", MSExcel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, MSExcel.XlSaveAsAccessMode.xlNoChange, MSExcel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
            xlWorkBook.Close();
            xlApp.Quit();

            if (xlShapes != null) { Marshal.ReleaseComObject(xlShapes); xlShapes = null; }
            if (xlWorkSheet != null) { Marshal.ReleaseComObject(xlWorkSheet); xlWorkSheet = null; }
            if (xlWorkSheets != null) { Marshal.ReleaseComObject(xlWorkSheets); xlWorkSheets = null; }
            if (xlWorkBook != null) { Marshal.ReleaseComObject(xlWorkBook); xlWorkBook = null; }
            if (xlWorkBooks != null) { Marshal.ReleaseComObject(xlWorkBooks); xlWorkBooks = null; }
            if (xlApp != null) { Marshal.ReleaseComObject(xlApp); xlApp = null; }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            #endregion \\\\\\\\\\ Save EXCEL file and release/close objects \\\\\\\\\\

            #region ////////// Kill EXCEL proccess ---> DON'T WANT THIS!!! //////////

            foreach (Process Proc in Process.GetProcesses())
                if (Proc.ProcessName.Equals("EXCEL"))
                    Proc.Kill();

            #endregion \\\\\\\\\\ Kill EXCEL proccess \\\\\\\\\\

After I pass the GC's, the Excel process is still there :/, it's freakin driving me crazy already. Please help!!!

// EDIT: Thanks to Hans for marking it as duplicate, the other post FINALY answers my question, all the tumb rules and dots and object releases are a piece of crap, you just need to put GC's outside a method not inside...

You may call the `ReleaseComObject`-method as often as references exist on the object. To ensure that there´s no more reference just call it within a loop until it returns 0. And please remove the GC-calls. It does its work regardless of what you say the GC... — MakePeaceGreatAgain, Nov 14 '14 at 08:42
I've worked after this example, http://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/ , I'll try to do as you said, in a loop, but don't know what type should be the com I'm passing to the function, like public int releaseComs(what_argument?) {return 0;} — Code, Nov 14 '14 at 08:54
Altough references exist on the object while it's != null, and in my code from the post you can see I always set them to null after I ReleaseComObject. So even if I put this in a loop it will always run 1 time since I always set them to null (which is indicated to do so after u dereference it) — Code, Nov 14 '14 at 09:01
Setting the references to null only serves to aid automated garbage collection, it won't matter on whether your excel process remains or not (unless you set them to null *before* telling `Marshal.ReleaseComObject()` to release the object, in which case you'd be telling it to release `NULL`) — Timothy Groote, Nov 14 '14 at 09:04
well, I haven't set any try/catch, what difference it makes? I don't have exceptions. // EDIT: how can I check if the object still has references then? I'm guessing this won't work will it? public int DereferenceComs(object myObj) { while(myObj != null) { Marshal.ReleaseComObject(myObj); myObj = null; } return 0; } — Code, Nov 14 '14 at 09:13

score 1 · Answer 1 · answered Nov 14 '14 at 10:09

The problem is that you have to fetch each and every (intermediate) object and call ReleaseComObject on it. Otherwise you have something dangling that won't be released and excel won't close.

Here are some bad examples of your code that leads to those problems:

 xlWorkSheet = xlWorkBook.Worksheets[sLineName];

With that you have a dangling reference to Worksheets. Better write it that way:

 xlWorkSheets = xlWorkBook.Worksheets;
 xlWorkSheet = xlWorkSheets[sLineName];

And another one:

xlWorkSheet.Cells[40, 4].Rows.AutoFit();

This should be better:

var cells = xlWorkSheet.Cells[40,4];
var rows = cells.Rows;
rows.AutoFit();

All this intermediate objects will then be put into an HashSet<object>. At the end i will then iterate over this list and call on all of them the release method.

The rule of thumb is: If you more than one dot in your command something is wrong.

To find such a problem i started my wrapper with only creating and closing an excel instance. Then i tested that excel opens up and immediately closes. Then i started to slowly access some objects and tested it. Between every new two lines I added I tested if excel still closes when I call the quit method. So far this is a very hard and frustrating way, but the only one to reliable clean up really all objects.

lol yeah, in the example you gave that's what I actually intended to do but I forgot, I tried as much as I could to respect the thumb rule but I got lost at some point, I'll re-see the code and try as much as I can to apply it altough I still can't seem to see how can I apply that to, let's say, the part where I add the picture as shape. — Code, Nov 14 '14 at 13:28
@VenomXLR: Comment all the code between `xlApp = new MSExcel.Application()` and `xlWorkBook.Close();`. Then test if excel opens and closes (it really should!). Then start to slowly uncomment your code and test if excel still closes. That's the only way i found to reliable find this f*** errors regarding to interop. — Oliver, Nov 14 '14 at 15:44

How to properly close/dispose Excel COMs in C# ?(Excel process not closing)

1 Answers1