CODEWITHSUNDEEP
excelvb.net
Tom
Tom

Reputation: 4577

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

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

Upvotes: 1

Views: 1511

Answers (2)

GNMercado
GNMercado

Reputation: 433

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

Upvotes: 2

soohoonigan
soohoonigan

Reputation: 2350

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

Upvotes: 1

Related Questions