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.
Code:''' <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


Reply With Quote



Bookmarks