Excel remains open in task manager after closing in VB.NET

Question

I've created an application that creates a couple of Excel spreadsheets in VB.NET

Issue I'm running into is that I cannot get Excel to completely quit.

I create and populate the workbooks in VB.NET with Excel hidden, the process (Microsoft Excel) shows in background processes in Task Manager

Once complete I make Excel visible and the process moves to Apps.

Then when I close Excel, the process goes back to being a background process.

Any idea what I am doing wrong?

Code:

    Dim oExcel As Excel.Application = Nothing
    Dim oWorkbook As Excel.Workbook = Nothing
    Dim oWorksheet As Excel.Worksheet = Nothing
    Dim oRange As Excel.Range = Nothing

    oExcel = CreateObject("Excel.Application")
    oExcel.DisplayAlerts = False
    oExcel.Visible = False

    oWorkbook = oExcel.Workbooks.Add
    oWorksheet = oWorkbook.ActiveSheet
    'Populate, format, etc.
    oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales.xlsx")

    oExcel.Visible = True

    oRange = Nothing
    oWorksheet = Nothing
    oWorkbook = Nothing

    ReleaseObject(oExcel)

    Public Sub ReleaseObject(ByVal obj As Object)

         Dim iValue As Integer = 0

         Try
             Do
                 iValue = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
             Loop While iValue > 0
         Catch ex As Exception
             RaiseError("", "modGeneral." & "." & System.Reflection.MethodBase.GetCurrentMethod().Name, Err.Number, Err.Description)
             obj = Nothing
         Finally
             GC.Collect()
         End Try

     End Sub

UPDATE 10/31/2016:

Ok, now I'm really confused.

Taking the suggestions below using this code I can get Excel to completely quit with one caveat:

        GC.Collect()
        GC.WaitForPendingFinalizers()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorksheet) : oWorksheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkbook) : oWorkbook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) : oExcel = Nothing

However, my code is creating two workbooks. The code to create each workbook is identical except for the SQL. If a user ticks chkA the clean-up code does NOT work. If they check chkB it does work. And if they check both, it doesn't work. I have included the full code for this below:

    Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

    Dim oExcel As Excel.Application = Nothing
    Dim oWorkbook As Excel.Workbook = Nothing
    Dim oWorksheet As Excel.Worksheet = Nothing
    Dim drSystem As SqlClient.SqlDataReader = Nothing
    Dim sSQL As String = ""
    Dim iRowCount As Integer = 2

    Try
        If Not Me.chkA.Checked And Not Me.chkB.Checked Then
            MsgBox("Select A, B or both before continuing.", vbInformation)
            Exit Try
        End If

        Me.Cursor = Cursors.WaitCursor
        Me.lblStatus.Text = "Exporting sales..."

        oExcel = CreateObject("Excel.Application")
        oExcel.DisplayAlerts = False
        oExcel.Visible = False

        If Me.chkA.Checked Then
            oWorkbook = oExcel.Workbooks.Add
            oWorksheet = oWorkbook.ActiveSheet
            oWorksheet.Cells(1, 1).Value = "Ship date"
            oWorksheet.Cells(1, 2).Value = "Customer"
            oWorksheet.Cells(1, 3).Value = "Invoice"
            oWorksheet.Cells(1, 4).Value = "Purchase order"
            oWorksheet.Cells(1, 5).Value = "Railcar"
            oWorksheet.Cells(1, 6).Value = "Weight"
            oWorksheet.Cells(1, 7).Value = "Total"
            oWorksheet.Cells(1, 8).Value = "Member purchase order"

            sSQL = "SELECT FORMAT(i.ship_date, N'MM/dd/yyyy') AS ship_date, "
            sSQL += "i.customer_no, "
            sSQL += "i.invoice_number, "
            sSQL += "i.customer_purchase_order_no, "
            sSQL += "r.railcar_number, "
            sSQL += "r.weight, "
            sSQL += "r.total, "
            sSQL += "i.member + N'-' + i.member_purchase_order_no AS member_purchase_order_no "
            sSQL += "FROM Invoices i "
            sSQL += "JOIN Railcars r "
            sSQL += "ON i.invoice_number = r.invoice_number "
            sSQL += "WHERE i.ship_date BETWEEN N'" & Format(Me.dtpStartDate.Value, "MM/dd/yyyy") & "' AND N'" & Format(Me.dtpEndDate.Value, "MM/dd/yyyy") & "' AND "
            sSQL += "invoice_type = N'A' "
            sSQL += "ORDER BY i.customer_no, "
            sSQL += "i.ship_date, "
            sSQL += "r.railcar_number"
            drSystem = modGeneral.drRunSQL(sSQL, CommandType.Text)
            Do While drSystem.Read
                oWorksheet.Cells(iRowCount, 1).Value = drSystem("ship_date")
                oWorksheet.Cells(iRowCount, 2).Value = drSystem("customer_no")
                oWorksheet.Cells(iRowCount, 3).Value = drSystem("invoice_number")
                oWorksheet.Cells(iRowCount, 4).Value = drSystem("customer_purchase_order_no")
                oWorksheet.Cells(iRowCount, 5).Value = drSystem("railcar_number")
                oWorksheet.Cells(iRowCount, 6).Value = drSystem("weight")
                oWorksheet.Cells(iRowCount, 7).Value = drSystem("total")
                oWorksheet.Cells(iRowCount, 8).Value = drSystem("member_purchase_order_no")
                iRowCount += 1
            Loop
            drSystem.Close()
            With oWorksheet.Range("A1", "J1")
                .Font.Bold = True
                .EntireColumn.AutoFit()
            End With
            oWorksheet.Range("D1").EntireColumn.HorizontalAlignment = Excel.Constants.xlLeft
            With oWorksheet.Range("F1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            With oWorksheet.Range("G1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales-a.xlsx")
        End If
        If Me.chkB.Checked Then
            iRowCount = 2
            oWorkbook = oExcel.Workbooks.Add
            oWorksheet = oWorkbook.ActiveSheet
            oWorksheet.Cells(1, 1).Value = "Ship date"
            oWorksheet.Cells(1, 2).Value = "Customer"
            oWorksheet.Cells(1, 3).Value = "Invoice"
            oWorksheet.Cells(1, 4).Value = "Purchase order"
            oWorksheet.Cells(1, 5).Value = "Railcar"
            oWorksheet.Cells(1, 6).Value = "Weight"
            oWorksheet.Cells(1, 7).Value = "Total"
            oWorksheet.Cells(1, 8).Value = "Member purchase order"

            sSQL = "SELECT FORMAT(i.ship_date, N'MM/dd/yyyy') AS ship_date, "
            sSQL += "i.customer_no, "
            sSQL += "i.invoice_number, "
            sSQL += "i.customer_purchase_order_no, "
            sSQL += "r.railcar_number, "
            sSQL += "r.weight, "
            sSQL += "r.total, "
            sSQL += "i.member + N'-' + i.member_purchase_order_no AS member_purchase_order_no "
            sSQL += "FROM mxInvoices i "
            sSQL += "JOIN mxRailcars r "
            sSQL += "ON i.invoice_number = r.invoice_number "
            sSQL += "WHERE i.ship_date BETWEEN N'" & Format(Me.dtpStartDate.Value, "MM/dd/yyyy") & "' AND N'" & Format(Me.dtpEndDate.Value, "MM/dd/yyyy") & "' AND "
            sSQL += "invoice_type = N'B' "
            sSQL += "ORDER BY i.customer_no, "
            sSQL += "i.ship_date, "
            sSQL += "r.railcar_number"
            drSystem = modGeneral.drRunSQL(sSQL, CommandType.Text)
            Do While drSystem.Read
                oWorksheet.Cells(iRowCount, 1).Value = drSystem("ship_date")
                oWorksheet.Cells(iRowCount, 2).Value = drSystem("customer_no")
                oWorksheet.Cells(iRowCount, 3).Value = drSystem("invoice_number")
                oWorksheet.Cells(iRowCount, 4).Value = drSystem("customer_purchase_order_no")
                oWorksheet.Cells(iRowCount, 5).Value = drSystem("railcar_number")
                oWorksheet.Cells(iRowCount, 6).Value = drSystem("weight")
                oWorksheet.Cells(iRowCount, 7).Value = drSystem("total")
                oWorksheet.Cells(iRowCount, 8).Value = drSystem("member_purchase_order_no")
                iRowCount += 1
            Loop
            drSystem.Close()
            With oWorksheet.Range("A1", "J1")
                .Font.Bold = True
                .EntireColumn.AutoFit()
            End With
            oWorksheet.Range("D1").EntireColumn.HorizontalAlignment = Excel.Constants.xlLeft
            With oWorksheet.Range("F1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            With oWorksheet.Range("G1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales-b.xlsx")
        End If
        oExcel.Visible = True

        GC.Collect()
        GC.WaitForPendingFinalizers()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorksheet) : oWorksheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkbook) : oWorkbook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) : oExcel = Nothing
    Catch ex As Exception
        RaiseError("", Me.Name & "." & System.Reflection.MethodBase.GetCurrentMethod().Name, Err.Number, Err.Description)
    Finally
        If Not drSystem Is Nothing Then
            If Not drSystem.IsClosed Then drSystem.Close()
        End If
    End Try

    Me.lblStatus.Text = ""
    Me.Cursor = Cursors.Default

and besides, this is duplicate of [How to properly clean up Excel interop objects?](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects) — T.S., Oct 28 '16 at 22:01

score 2 · Answer 1 · answered Oct 29 '16 at 04:05

You need to dispose and release the excel object after using it.

A simple code I use in order to close excel even in task manager:

Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub

It works on my application

score 1 · Answer 2 · answered Oct 28 '16 at 23:29

Unfortunately, setting the variables to nothing does not release the excel process handle in the case of interop...But if you explicitly release each excel com object reference you have instantiated, the process should not be left hanging once excel has been closed by the user. When I ran the following code and closed excel, the process was no longer hanging (I left out oRange since it was still set to nothing, and releasing an object that is nothing throws an exception):

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim oExcel As Excel.Application = Nothing
        Dim oWorkbook As Excel.Workbook = Nothing
        Dim oWorksheet As Excel.Worksheet = Nothing

        oExcel = CreateObject("Excel.Application")
        oExcel.DisplayAlerts = False
        oExcel.Visible = False

        oWorkbook = oExcel.Workbooks.Add
        oWorksheet = oWorkbook.ActiveSheet
        'Populate, format, etc.
        'oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales.xlsx")

        oExcel.Visible = True

        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorksheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkbook)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        Me.Close()
    End Sub
End Class

I threw your edited code into a program and ran it (without the sql of course) and I wasn't able to reproduce the problem, the excel processes would close as soon as I closed excel. I'm not sure why they are hanging on your PC... — soohoonigan, Oct 31 '16 at 17:10
Thanks for trying, I have no idea why I'm experiencing this behavior. Hoping someone else can chime in. — Tom, Oct 31 '16 at 18:06
No problem, sorry I couldn't help more. I'll bump the question back up — soohoonigan, Oct 31 '16 at 18:07
I've encountered this problem and is very annoying, be sure to delete recovered workbooks, and if you're debugging, stop your project in the IDE, don't close excel... Stop the debugging rather. And of course, release your objects by " = nothing " — James Heffer, Nov 02 '16 at 09:33
@JamesHeffer I opened a ticket with Microsoft so they are looking at the code, will post what they come back with. — Tom, Nov 02 '16 at 12:34

Excel remains open in task manager after closing in VB.NET

2 Answers2