Closing C# excel COM object properly

Question

I am trying to properly close the excel COM object such as excel application workbook and worksheet. I have browsed all the solution on this site and other site. Nothing works. Only way is to run a loop to kill or excel process. is there a better way or a way to kill only single excel file process?

        #region VARIABLE

            int nColumn = 0;
            int nIMPORT = 1;
            int endofsheet = 0;

            string IMPORTfilepath = null;
            string nsheet = null;
            string nCell = null;
            string lastfilename = "";            

            double rRFDS = 0;
            double cRFDS = 0;

            List<String> filename = new List<String>();  

            object misValue1 = System.Reflection.Missing.Value;

            //Opening Central DB
            Excel.Application CentralDB;
            Excel.Workbook CentralDBWorkBook;
            Excel.Worksheet CentralDBWorkSheet;
            Excel.Range CentralDBrange;

            //Opening RFDS
            Excel.Application IMPORT;
            Excel.Workbook IMPORTWorkBook;
            Excel.Worksheet IMPORTWorkSheet;

            //Excel.Range RFDSrange;
            Excel.Range CellAddress;

        #endregion

        CentralDB = new Microsoft.Office.Interop.Excel.Application();
        CentralDBWorkBook = CentralDB.Workbooks.Open(Odl1.FileName, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        CentralDBWorkSheet = CentralDB.Worksheets.get_Item(1);

        IMPORT = new Microsoft.Office.Interop.Excel.Application();
        IMPORTfilepath = Odl2.FileNames[nIMPORT];
        IMPORTWorkBook = IMPORT.Workbooks.Open(IMPORTfilepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        IMPORTWorkSheet = IMPORT.Worksheets.get_Item(1);

        CentralDBrange = CentralDBWorkSheet.UsedRange;        

        filename = Odl2.FileNames.ToList();

        endofsheet = CentralDBrange.Rows.Count;            

        foreach(string fn in filename)
        {
            try
            {
                IMPORTWorkBook = IMPORT.Workbooks.Open(fn, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                CentralDBWorkSheet.Cells[nIMPORT + endofsheet, 1] = IMPORTWorkBook.Name;
                for (nColumn = 3; nColumn <= CentralDBrange.Columns.Count; nColumn++)
                {
                    nsheet = (string)(CentralDBrange.Cells[2, nColumn].Value2);
                    foreach (Excel.Worksheet ws in IMPORTWorkBook.Sheets)
                    {
                        if (ws.Name.Equals(nsheet))
                        {
                            IMPORTWorkSheet = IMPORTWorkBook.Worksheets.get_Item(nsheet);

                            nCell = (string)(CentralDBrange.Cells[3, nColumn].Value2);
                            CellAddress = CentralDBWorkSheet.get_Range(nCell, nCell);

                            rRFDS = CellAddress.Row;
                            cRFDS = CellAddress.Column;
                            CentralDBWorkSheet.Cells[nIMPORT + endofsheet, nColumn] = IMPORTWorkSheet.Cells[rRFDS, cRFDS];
                            label4.Text = nIMPORT.ToString() + "/" + Convert.ToString(Odl2.FileNames.Count());
                        }
                    }
                }
                nIMPORT++;
                nColumn = 3;
                CentralDBWorkBook.Save();
                IMPORTWorkBook.Close(false, null, null);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Unable to release the Object " + ex.ToString());                            
            }
            lastfilename = fn;
        }

        CentralDB.Quit();
        releaseObject(CentralDBWorkSheet);
        releaseObject(CentralDBWorkBook);
        releaseObject(CentralDB);

        IMPORT.Quit();
        releaseObject(IMPORTWorkSheet);
        releaseObject(IMPORTWorkBook);
        releaseObject(IMPORT);
        GC.Collect();  
 }        

            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (System.Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }

            }

Tip: use as little references to Excel as possible and try not to store it in local/temp variables either. I would also look at making a Singleton pattern for an Excel instance. — Ahmed ilyas
– Ahmed ilyas, Commented Dec 8, 2013 at 0:02

noseratio · Accepted Answer · 2013-09-23 04:19:50Z

1

Try this order (plus .Visible = false):

    releaseObject(CentralDBWorkSheet);
    releaseObject(CentralDBWorkBook);

    releaseObject(IMPORTWorkSheet);
    releaseObject(IMPORTWorkBook);

    CentralDB.Visible = false;
    IMPORT.Visible = false;

    CentralDB.Quit();
    IMPORT.Quit();

    releaseObject(CentralDB);
    releaseObject(IMPORT);

answered Sep 23, 2013 at 4:19

noseratio

62.1k37 gold badges226 silver badges509 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yousuf Hossain Over a year ago

No luck using that order

Ahmed ilyas · Accepted Answer · 2013-12-08 00:06:35Z

In addition to my comment... I use this and works just nicely... but again, I don't have references held all over the place.

private void DoReleaseCOMObject(object obj)
        {
            int refCounter = Marshal.ReleaseComObject(obj);
            while (refCounter > 0)
            {
                refCounter = Marshal.ReleaseComObject(obj);
                System.Diagnostics.Debug.WriteLine("ReleaseCOM ref: " + refCounter);
            }

            obj = null;
        }

 private void DoFinalReleaseCOMObject(object obj)
        {
            int refCounter = Marshal.FinalReleaseComObject(obj);
            System.Diagnostics.Debug.WriteLine("FinalReleaseComObject ref: " + refCounter);

            obj = null;
        }

public void Dispose()
        {
            System.Diagnostics.Debug.WriteLine("In Dispose");
            this.Dispose(false); // because we are doing unmanaged
            GC.Collect();
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            System.Diagnostics.Debug.WriteLine("In Dispose (" + disposing + ")");
            if (!disposing) // unmanaged
            {
                #region COM Cleanup

                this.DoFinalReleaseCOMObject(this.headerStartCell);
                this.DoFinalReleaseCOMObject(this.headerEndCell);
                this.DoFinalReleaseCOMObject(this.headerWriteRange);

                this.DoFinalReleaseCOMObject(this.startCell);
                this.DoFinalReleaseCOMObject(this.endCell);
                this.DoFinalReleaseCOMObject(this.writeRange);

                this.DoFinalReleaseCOMObject(this.ws);
                this.DoFinalReleaseCOMObject(this.wb);
                this.DoFinalReleaseCOMObject(this.wbs);

                this.DoFinalReleaseCOMObject(this.excelApp);

                System.Diagnostics.Debug.WriteLine("I've tried to clean");

                #endregion
            }

            // no need for else condition if dispose is true as we are not dealing with managed objects
        }

yes, I implemented the IDisposable interface. I also had a few references of Excel objects referenced/held globally (same instance) and that is what I am cleaning up onces I quit Excel

Collectives™ on Stack Overflow

Closing C# excel COM object properly

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related