CODEWITHSUNDEEP
.netvb.net.net-3.5automationms-office
priyamtheone
priyamtheone

Reputation: 537

Excel process continues running

I'm calling the following method from a button click event to export a datatable to excel. After the export is completed, the excel application object is quit, released and assigned to nothing. But in reality it's not getting released and stays active unless the entire application is closed. So every time the button is clicked for export, a new excel application object keeps on running. How can I solve this? Please help. Regards.

The problem doesn't occur if two of the lines from the method below are not used. But I can't omit them as they are really needed. Check the * marked lines.

''' <summary>
''' Exports data from a datatable to excel.
''' </summary>
Friend Shared Sub ExportToExcel(ByVal dtbl As DataTable)
    Dim exa As Excel.Application = Nothing
    Dim wkb As Excel.Workbook = Nothing
    Dim wks As Excel.Worksheet = Nothing
    Dim intColIndex, intRowIndex As Integer
    intColIndex = 0 : intRowIndex = 2

    Try
        exa = New Excel.Application
        exa.SheetsInNewWorkbook = 1
        wkb = exa.Workbooks.Add
        wks = wkb.ActiveSheet

        For intColIndex = 1 To dtbl.Columns.Count
            wks.Cells(1, intColIndex) = dtbl.Columns(intColIndex - 1).ColumnName
        Next

        For Each row As DataRow In dtbl.Rows
            For intColIndex = 1 To dtbl.Columns.Count
                wks.Cells(intRowIndex, intColIndex) = row(intColIndex - 1)
            Next

            intRowIndex += 1
        Next

        For intColIndex = 1 To dtbl.Columns.Count
            wks.Range(wks.Cells(1, intColIndex), wks.Cells(1, intColIndex)).Font.Bold = True
            wks.Range(wks.Cells(1, intColIndex), wks.Cells(1, intColIndex)).Font.Underline = True
        Next

    '***** The problem doesn't occur if the following two lines are not used *****
        wks.Range(wks.Cells(1, 1), wks.Cells(dtbl.Rows.Count + 1, dtbl.Columns.Count)).Columns.WrapText = False
        wks.Range(wks.Cells(1, 1), wks.Cells(dtbl.Rows.Count + 1, dtbl.Columns.Count)).Columns.AutoFit()
    '*****************************************************************************

        exa.Visible = True
        exa.UserControl = True

        If Not exa Is Nothing Then exa.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wks)
        wks = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wkb)
        wkb = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exa)
        exa = Nothing
    Catch ex As Exception
        wks = Nothing
        wkb = Nothing
        exa = Nothing
        MsgBox("The following error has occurred:" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Error")
    Finally
        GC.Collect()
    End Try
End Sub

Upvotes: 0

Views: 284

Answers (2)

Priyank
Priyank

Reputation: 10623

Here is your solution. Never use 2 dots with com objects. Your Range().Columns creates temp variable which are not being released.

How do I properly clean up Excel interop objects?

http://www.velocityreviews.com/forums/showpost.php?s=f87f0674feda4442dcbd40019cbca65b&p=528575&postcount=2

Upvotes: 1

Brook
Brook

Reputation: 6009

Avoid writing excel files using interop, it's usually riddled with these types of problems.

A preferred method would be to use one of the various excel api's to generate the files such as excelpackage, NPOI, or excellibrary. As an added bonus, the user doesn't have to have excel installed (they could use open office etc)

Upvotes: 0

Related Questions