C# Proper Way to Close Multiple Excel Objects With

Question

I have a C# console program. When it runs, it instantiates some Excel objects such as: (openExcelO).

When the program is done running, I have a method to close down Excel (closeExcel) and that is supposed to properly clean up the Excel process. In task manager I can see the remnants of Excel that remain. The shut down routines were cobbled together from internet examples:

    private void openExcelO (string dir) {

        try {
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(dir + "PortfolioOptimization5.xlsm",
                0, false, 5, "", "", true, 
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
                "\t", false, false, 0, true, 1, 0);
            xlMarkowitz = (Excel.Worksheet)xlWorkBook.Sheets["Markowitz"];
            xlWeights = (Excel.Worksheet)xlWorkBook.Sheets["Weights"];

        } catch (Exception ex) {
            ReportError("PortfolioOptimization", "openExcel", ex.ToString());
        }

    }


    private void closeExcel () {
        object misValue = System.Reflection.Missing.Value;
        try {

            if (xlWeights != null) releaseObject(xlWeights);
            if (xlMarkowitz != null) releaseObject(xlMarkowitz);

            xlWorkBook.Close(false, misValue, misValue);
            if (xlApp != null) xlApp.Quit();

            if (xlWorkBook != null) releaseObject(xlWorkBook);
            if (xlApp != null) releaseObject(xlApp);

        } catch (Exception ex) {
            ReportError("PortfolioOptimization", "closeExcel", ex.ToString());
        }
    }


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

How can I change the shutdown routine to properly shut down and clean up the Excel objects so there are no lingering Excel processes in the Task Manager?

Thanks!

Answer 1

It looks like you're not releasing your objects in the correct order - xlApp.Quit probably can't exit cleanly because you still hold a reference to the workbook. You need to release the workbook before you invoke Quit:

xlWorkBook.Close(false, misValue, misValue);
releaseObject(xlWorkBook);

xlApp.Quit();
releaseObject(xlApp);

Credit must go to this StackOverflow answer: there are a couple of other implicit references that you're not closing, i.e., in the following lines:

xlWorkBook = xlApp.Workbooks.Open(...)
xlWorkBook.Sheets["Markowitz"];

The "Workbooks" object isn't being released, and neither is the "Sheets" object. You need to release these references too, i.e.:

var books = xlApp.Workbooks;
books.Open(...);
releaseObject(books);

var sheets = xlWorkBook.Sheets;
xlMarkowitz = (Excel.Worksheet)sheets["Markowitz"];
xlWeights = (Excel.Worksheet)sheets["Weights"];
releaseObject(sheets);

Great advice from Bruce Barker: "Never use two dots with COM objects"!