Excel process continues running (.Net Framework 3.5, VS.Net 2008, VB.Net 2008).

[priyamtheone]

Hi, 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

[R MacDonald]

Hello, priyamtheone,

This is a problem that occurs because of an implicit reference to Excel. The application holds on to this reference until it closes. In your case, I believe that you can fix the problem by using an explicit reference within the "offending" lines. Here is an example:

            '***** The problem doesn't occur if the following two lines are not used ***** 
            Dim ColumnCollection As Excel.Range = wks.Range(wks.Cells(1, 1), wks.Cells(dtbl.Rows.Count + 1, dtbl.Columns.Count)).Columns
            ColumnCollection.WrapText = False
            ColumnCollection.AutoFit()
            ''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()
            '***************************************************************************** 
 

Cheers,
Randy