CODEWITHSUNDEEP
c#excelinteropexcel-2010excel-interop
Neil Moss
Neil Moss

Reputation: 6848

C# Excel - not releasing through interop - macros and Solver involved

Ah, another "Excel not releasing" post on SO.....

I have an Excel workbook which I load through Excel (2010), invoke the Excel application's Run method to launch a macro in the workbook, save the updated workbook and quit Excel.

And Excel stays behind, even after the calling app is closed.

I haven't fallen into the "two dots" trap, I close all that can be closed, quit all that can be quit and release every reference to every COM object used and given the GC a kick.

If I don't make the call to run the macro, all is well. So something about this particular macro is at fault, as other workbooks don't show this behaviour.

The macro itself loads the Solver add-in and makes some calls to C++ functions in a non .NET win32 dll. It invokes the Solver add-in, and does nothing else out of the ordinary (math, set ranges, set cell values and the like).

Are there any Solver or macro related gotchas I should know about? I've even tried unloading the add-in from C# - no joy.

    static void NAR(object o) // MSDN article suggestion.
    {
        try
        {
            while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
            {
            }
        }
        catch
        {
            // swallow
        }
        finally
        {
            o = null;
        }
    }
    static void Excel(string FilePath, string SaveAsPath)
    {
        Application excelApplication = null;
        Workbooks workbooks = null;
        Workbook workbook = null;

        try
        {
            excelApplication = new Application { DisplayAlerts = false, Visible = false };
            workbooks = excelApplication.Workbooks;
            workbook = workbooks.Open(FilePath);

            excelApplication.Run("SolveEquationWithSolver");

            workbook.CheckCompatibility = false;
            workbook.SaveAs(SaveAsPath);
        }
        finally
        {
            if (workbook != null)
            {
                workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
                NAR(workbook);
            }

            if (workbooks != null)
            {
                workbooks.Close();
                NAR(workbooks);
            }

            if (excelApplication != null)
            {
                excelApplication.Quit();
                NAR(excelApplication);
            }

            // Kick GC, as suggested elsewhere on SO. Not my idea.
            GC.Collect();
            GC.WaitForPendingFinalizers();

            // Double-tap GC, as suggested elsewhere on SO. Not my idea.
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }

UPDATE

Another forum has suggested using excelApplication.ActiveWindow.Close(), and this works!! (if the workbook.Close() call is removed).

However, this breaks the "two dots" rule. So, what is it doing that means we can get away with it in this case, but not in the general case?

Upvotes: 0

Views: 1198

Answers (1)

ikh
ikh

Reputation: 2436

I had similar problem to yours at one point, and eventually gave up and decided to just kill Excel. If you close the workbooks involved before killing the process, you should not have any annoying side effects (like "recovered" workbooks next time you open Excel).

using System.Runtime.InteropServices;

// ...
// Inside a class:

[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(
    IntPtr hWnd, out uint lpdwProcessId);

// Returns "true" on success, "false" on failure.
public static bool KillExcel(Application excel) {
    uint processId;
    GetWindowThreadProcessId((IntPtr)excel.Hwnd, out processID);
    try {
        Process.GetProcessById((int)processID).Kill()

    } catch (Exception /* TODO: catch only relevant exceptions */) {
        return false;
    }

    return true;
}

Upvotes: 1

Related Questions