How do you close Excel com object and save the work?

Question

Hi all.

This is driving me nut. I have found similar post regarding Excel com object here but none of the solution that I tried can successful close Excel after my code is finish running. The Excel.dll will linger on the TaskManager.

Can someone take a look at my code below and let me know why the Excel won't close?

Try
        'First get an application instance
        xlApp = New Excel.Application()
        'Second open the workbook you need
        xlBook = xlApp.Workbooks.Open(_FileInfo.FullName)
        xlSheet = xlBook.Worksheets(sheetName)
        'set visibility
        xlApp.Visible = False
        Dim row As Integer = 2
        For Each t In upsRecordList
            While (row < upsRecordList.Count + 2)
                xlSheet.Cells(row, 1) = t.StartDate
                xlSheet.Cells(row, 2) = t.AccountTypeCode
                row += 1
            End While
        Next
    Catch ex As Exception
        Throw ex
    Finally
        If Not xlApp Is Nothing Then
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            NAR(xlSheet)
            If Not xlBook Is Nothing Then
                xlBook.Close(SaveChanges:=True)
                NAR(xlBook)
            End If
            xlApp.Quit()
            NAR(xlApp)
        End If
    End Try

 Private Sub NAR(ByVal o As Object)
    Try
        Marshal.FinalReleaseComObject(o)
    Catch
    Finally
        o = Nothing
    End Try
End Sub

Answer 1

Your code is fine. Excel won't always close completely when you release the object, or even by using the Quit method.

I suppose you could iterate through the running tasks, forcefully terminating it, but I don't recommend doing it that way.

Answer 2

Here's what I've done:

Keep your Excel instance a singleton:

// Our singleton excel instance
private static Excel.Application instance;

/// <summary>
/// Returns an instance of an Excel Application.
/// This instance is a singleton.
/// </summary>
public static Excel.Application GetInstance
{
    get
    {
        if (instance == null)
        {
            try
            {
                instance = new Excel.Application();
                // Give the app a GUID so we can kill it later
                instance.Caption = System.Guid.NewGuid().ToString().ToUpper(CultureInfo.CurrentCulture);
                instance.Visible = false;
            }
            catch (COMException ce)
            {
                ShowMessage(ce.Message, MessageBoxIcon.Error);
            }
        }
        return instance;
    }
}

When you're done, you can destroy it:

   public static void DestroyInstance()
    {
        if (instance != null)
        {
            // Close the workbooks
            object missing = Type.Missing;
            foreach (Excel.Workbook book in GetInstance.Workbooks)
            {
                book.Close(false, book.FullName, missing);
            }

        String appVersion = instance.Version;
        String appCaption = instance.Caption;
        IntPtr appHandle = IntPtr.Zero;
        if (Convert.ToDouble(appVersion, CultureInfo.CurrentCulture) >= 10)
        {
            appHandle = new IntPtr(instance.Parent.Hwnd);
        }

        // Quit and release the object
        instance.Workbooks.Close();
        instance.Quit();
        Release(instance);

        // Force a cleanup.  MSDN documentation shows them making
        // two sets of collect and wait calls.
        System.GC.Collect();
        System.GC.WaitForPendingFinalizers();
        System.GC.Collect();
        System.GC.WaitForPendingFinalizers();

        EnsureProcessKilled(appHandle, appCaption);
    }
}

The EnsureProcessKilled method looks like:

/// <summary>
/// This method contains a number of ways to try and kill
/// the Excel process created when an instance is instantiated.
/// </summary>
/// <param name="windowHandle">Handle of the application window</param>
/// <param name="caption">A custom GUI stored as the app's caption</param>
public static void EnsureProcessKilled(IntPtr windowHandle, String caption)
{
    NativeMethods.SetLastError(0);
    if (IntPtr.Equals(windowHandle, IntPtr.Zero))
    {
        windowHandle = NativeMethods.FindWindow(null, caption);
    }
    if (IntPtr.Equals(windowHandle, IntPtr.Zero))
    {
        // Can't find the window; assumed it's closed
        return;
    }
    int resourceId;
    int processId = 0;
    resourceId = NativeMethods.GetWindowThreadProcessId(windowHandle, ref processId);
    if (processId == 0)
    {
        // Can't get process id
        if (NativeMethods.EndTask(windowHandle) != 0)
        {
            return; // Success
        }
    }
    // Couldn't end it nicely, let's kill it
    System.Diagnostics.Process process;
    process = System.Diagnostics.Process.GetProcessById(processId);
    process.CloseMainWindow();
    process.Refresh();
    if (process.HasExited)
    {
        return;
    }
    // If we get here, it's being really stubborn.  Say goodbye, EXCEL.EXE!
    process.Kill();
}

And you're going to need the NativeMethods class:

public static class NativeMethods
{

    [DllImport("user32.dll")]
    internal static extern int EndTask(IntPtr windowHandle);

    [DllImport("user32.dll", CharSet = CharSet.Unicode)]
    internal static extern IntPtr FindWindow(String className, String windowName);

    [DllImport("user32.dll")]
    internal static extern int GetWindowThreadProcessId(IntPtr windowHandle, ref int processId);

    [DllImport("kernel32.dll")]
    internal static extern IntPtr SetLastError(int errorCode);

}

And one other little helper function:

public static void Release(Object releasable)
{
    System.Runtime.InteropServices.Marshal.ReleaseComObject(releasable);
    releasable = null;
}

Answer 3

Here's my finally block; AFAIK this hasn't failed to release Excel in our applications. We leave Excel open and rely on the user to close it. The calls to Marshal.FinalReleaseComObject will need to be customized for the Excel objects you reference.

        finally
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            // Calls are needed to avoid memory leak
            Marshal.FinalReleaseComObject(sheet);
            Marshal.FinalReleaseComObject(book);
            Marshal.FinalReleaseComObject(excel);
        }

Answer 4

Remove the parens from the Quit statement: xlApp.Quit

Immediately after that line, add this: Set xlApp = Nothing

Answer 5

Me again (now registered). Another thing - don't use parens for Excel parameters, unless you're passing a value into a variable. The code should be this:

   If Not xlApp Is Nothing Then
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        NAR(xlSheet)
        If Not xlBook Is Nothing Then
            xlBook.Close True
            xlBook = Nothing
        End If
        xlApp.Quit
        xlApp = Nothing
    End If