How to close excel in C#?

Question

I am trying to run Excel macro and close excel after run.
Unfortunately it does not work. I tried all solutions from Stackoverflow and could not get reliable solution. Please help. As you can see I am trying to close, quite, release COM object but nothing seems to be working.

public static bool RunMacro(string Path, string MacroName, bool Close, ProgressForm ProgressForm, params object[] Arguments)
{
    Microsoft.Office.Interop.Excel.Application aApplication = null;
    bool aCloseApplication = true;
    bool aResult = false;

    try
    {
        aApplication = (Microsoft.Office.Interop.Excel.Application)Marshal.GetActiveObject("Excel.Application");
        aCloseApplication = false;
    }
    catch (COMException aCOMException)
    {
        aApplication = new Microsoft.Office.Interop.Excel.Application();
        aApplication.Visible = false;
    }

    if (aApplication != null)
    {
        aApplication.ScreenUpdating = false;

        Microsoft.Office.Interop.Excel.Workbook aWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet aWorksheet = null;
        bool aCloseWorkbook = true;

        try
        {
            if (IsEdited(aApplication))
            {
                throw new Exception("Excel is in cell edit mode. Please stop editing cell and run import again");
            }
            else
            {
                for (int i = 0; i < aApplication.Workbooks.Count; i++)
                    if (aApplication.Workbooks[i + 1].FullName == Path)
                    {
                        aWorkbook = aApplication.Workbooks[i + 1];
                        aCloseWorkbook = false;
                        break;
                    }

                if (aWorkbook == null)
                    aWorkbook = aApplication.Workbooks.Open(Path);

                // Run macro here
                aApplication.Run(string.Format("{0}!{1}", System.IO.Path.GetFileName(Path), MacroName), Arguments);

                aResult = true;
            }
        }
        finally
        {
            if (aWorksheet != null)
            {
                Marshal.ReleaseComObject(aWorksheet);
            }

            //does not work here!!! I want to close excel here 
            if (aWorkbook != null)
                aWorkbook.Close();
                aApplication.Quit();
                Marshal.ReleaseComObject(aWorkbook);
                Marshal.ReleaseComObject(aApplication);
        }
    }
    return aResult;
}

Answer 1

This is something I've played around with a lot while using SSIS Script Tasks to refresh Excel files.

I've read mixed things about using Marshal.ReleaseComObject, but I've also found that it isn't necessary. For me, the ultimate solution was found to be the following:

using xl = Microsoft.Office.Interop.Excel;

...

public void Main()
{
    DoExcelWork();

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

private void DoExcelWork()
{
    xl.Application app = null;
    xl.Workbooks books = null;
    xl.Workbook book = null;

    try
    {
        app = new xl.Application() { DisplayAlerts = false };

        books = app.Workbooks;
        book = books.Open("file path goes here");

        book.RefreshAll();
        // this is where you would do your Excel work

        app.DisplayAlerts = false; // This is for reinforcement; the setting has been known to reset itself after a period of time has passed.
        book.SaveAs("save path goes here");
        app.DisplayAlerts = true;

        book.Close();
        app.Quit();
    }
    catch
    {
        if (book != null) book.Close(SaveChanges: false);
        if (app != null) app.Quit();
    }
}

I'm not sure how your application is laid out, but when using SSIS I found it was necessary to call GC.Collect outside of the scope where the Excel Interop objects were declared in order to avoid having the Excel instances left open on some occasions, hence the two methods.

You should be able to adapt this code to suit your requirements.

Answer 2

Three things:

• Close Excel, either manually or programmatically. Excel might popup a question box asking if you would like to save (even if Excel is currently hidden). It has to actually get closed for this to work; if you choose cancel it won't have been closed. There should be an overload that allows you to ignore unsaved changes.

• Set the reference to null.

• Call GC.Collect();.

According to MSDN one way to prevent a box from blocking the close attempt is to set DisplayAlerts to false. (Though you would lose changes.)

As far as I know the Marshall stuff you're doing is unnecessary (according to this answer, it's in fact dangerous). What I specifically do is wrap the interop object in my own Excel object which implements IDisposable. My Dispose method looks basically like this (though I do some other things too, like always Show Excel if it wasn't closed, always turn back on rendering and auto-calculation, etc.)

void Dispose() {
   if (mExcel != null) {
      mExcel = null;
      GC.Collect();
   }
}

And have static methods which implement the correct usage pattern:

public static void UseAndClose(Action<Excel> pAction) {
   using (var excel = new Excel()) {
      pAction(excel);
      excel.Close(false); // closes all workbooks and then calls `Quit`
   }
}

Answer 3

If you do decide to use the Marshal.ReleaseCOMObject technique (as opposed to GC.Collect) then the key is to ensure you are releasing every single RCW (COM Object), not just some of them.

An example, based on your original code:

public static bool RunMacro(string Path, string MacroName, bool Close, params object[] Arguments)
{
    Microsoft.Office.Interop.Excel.Application aApplication = null;
    bool aCloseApplication = true;
    bool aResult = false;

    try
    {
        aApplication = (Microsoft.Office.Interop.Excel.Application)Marshal.GetActiveObject("Excel.Application");
        aCloseApplication = false;
    }
    catch (COMException aCOMException)
    {
        aApplication = new Microsoft.Office.Interop.Excel.Application();
        aApplication.Visible = false;
    }

    if (aApplication != null)
    {
        aApplication.ScreenUpdating = false;

        Microsoft.Office.Interop.Excel.Workbook aWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet aWorksheet = null;
        bool aCloseWorkbook = true;

        try
        {
            if (IsEdited(aApplication))
            {
                throw new Exception("Excel is in cell edit mode. Please stop editing cell and run import again");
            }
            else
            {
                var workbooks = aApplication.Workbooks;
                for (int i = 0; i < workbooks.Count; i++)
                {
                    var workbook = aApplication.Workbooks.Item[i + 1];
                    if (workbook.FullName == Path)
                    {
                        aWorkbook = workbook;
                        aCloseWorkbook = false;
                        break;
                    }
                    else
                    {
                        Marshal.ReleaseComObject(workbook);
                    }
                }

                if (aWorkbook == null)
                    aWorkbook = workbooks.Open(Path);

                Marshal.ReleaseComObject(workbooks);

                // Run macro here
                aApplication.Run(string.Format("{0}!{1}", System.IO.Path.GetFileName(Path), MacroName),
                    Arguments);

                aResult = true;
            }
        }
        finally
        {
            if (aWorksheet != null)
            {
                Marshal.ReleaseComObject(aWorksheet);
            }

            //does not work here!!! I want to close excel here 
            if (aWorkbook != null)
                aWorkbook.Close();
            aApplication.Quit();
            Marshal.ReleaseComObject(aWorkbook);
            Marshal.ReleaseComObject(aApplication);
        }
    }
    return aResult;
}

Note that I have introduced some extra variables. workbooks and workbook to point to objects that were not RCOing in your original code.

In terms of deciding between this technique (using ReleaseCOMObject) and the other technique (using GC.Collect) there are a number of factors to consider:

• ReleaseCOMObject requires more discipline (to ensure that you release everything, consider the double dot rule, realise that application.Workbooks["something]" is actually shorthand for application.Workbooks.Item["something"] (and thus has two dots) etc etc)
• ReleaseCOMObject is particularly hard if the lifetime of the object involved is hard to track in your code (not the case in your code, since the lifetime you want is basically the function scope - but it can apply in some scenarios). As Chris Brumme states :

  If you are a client application using a modest number of COM objects that are passed around freely in your managed code, you should not use ReleaseComObject.

• GC.Collect() is simpler (basically just call GC.Collect(); and GC.WaitForPendingFinalizers(); until Marshal.AreComObjectsAvailableForCleanup() returns false).
• GC.Collect is generally slower (since it needs to GC everything not just the RCWs involved).
• GC.Collect (well, more specifically Marshal.AreComObjectsAvailableForCleanup) can be problematic if multiple threads are involved (since it may return true due to other RCWs (from other threads), not just the ones you are specifically interested in in this scope).
• GC.Collect may reduce your overall system performance since it may contribute to mid life crisis for all of your objects (by pushing them into a later GC generation than they would have been in otherwise).

Note that if you do decide to use the GC.Collect, the other answers here are excellent in giving some guidance as to how to go about doing that.

Answer 4

You should never ever call Marshal.ReleaseComObject and Marshal.FinalReleaseComObject methods within Excel Interop; it's a bad practice.

No one (including Microsoft) explicitly indicates that you have to release COM references manually since your application and the interop library are supposed to handle this automatically. And even if you want to keep on releasing COM references manually, once it's done you have to ensure that they are cleaned up when the process ends by calling GC.Collect() and GC.WaitForPendingFinalizers() (on a side note, the GC may fail if you run the method from the debugger since local references are kepy alive until the end of a method).

This is all you have to do:

// let's make sure that the process doesn't hang because of alerts
aApplication.DisplayAlerts = false;

aWorkbook.Save();
aWorkbook.Close();

aApplication.Quit()
aApplication = null;