C# Proper Way to Close Multiple Excel Objects With Excel Process Destroyed

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!

You should probably try releasing the workbook immediately after you close it, then close excel, then release the application. Does Quit have a boolean overload to allow exit while ignoring all errors? The Word interop object does. — Simon MᶜKenzie
– Simon MᶜKenzie, Commented Mar 21, 2013 at 1:15
possible duplicate of Proper way of closing Excel application — Ken White
– Ken White, Commented Mar 21, 2013 at 1:18
Note also that in addition to the fact that you should always think very hard before invoking GC.Collect, your calls to GC.Collect won't actually help your object get collected, as your main class still holds a reference to each of your COM objects. To make releaseObject also clear the primary reference, change it to releaseObject(ref object obj), because then when you invoke obj = null, the field itself will be set to null. — Simon MᶜKenzie
– Simon MᶜKenzie, Commented Mar 21, 2013 at 3:03
@KenWhite I see the similarity but feel this question and answer are more complete than the one referenced. I didn't see any other question that addressed the issue of sheet, workbook and app objects with order of destruction. I have marked Simon's answer so if you need to close the topic it is your call. — Patrick White
– Patrick White, Commented Mar 21, 2013 at 3:10

Community · Accepted Answer · 2017-05-23 11:52:32Z

9

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"!

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Mar 21, 2013 at 1:26

Simon MᶜKenzie

8,82213 gold badges54 silver badges82 bronze badges

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

1 Comment

Patrick White Over a year ago

This is a very good answer Simon! It addresses the two problems in my code (order of object destruction and the catch with implicit references.) Thanks!

Collectives™ on Stack Overflow

C# Proper Way to Close Multiple Excel Objects With Excel Process Destroyed

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related