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

[GremlinSA]

This is a well know problem with Excel, and some other MS Office products..

Problem is it's often difficult to find out exactly why Excel stays running.. The top reason that we've come up with at work is that it's possibly sitting with a 'Do You want to save changes' dialog box that is not showing to close... We've moved to using CSV or XML docs..

[dglienna]

Seems like the hard way... Here's some VB6 code that is well commented

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
    ' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

MSDN has samples here

[Erik Wiggins]

I've got the simple fix for you. Just add the following lines to your code. Just before setting exa=nothing

exa.ActiveWorkbook.Save()
        exa.ActiveWorkbook.Close()

[GremlinSA]

@priyamtheone : try the options posted here... However they still do not guarantee that Excel will close..

Remember that Excel was not Written to be a remotely accessed system like SQL or Access, but as an Application with FULL user interaction.. It likes even less to be called by a Service application..

It's unfortunate as Excel files have become the norm for transmitting User Captured and processed information back and forth. and many companies now want to automate the processing of these files, it puts a huge load on the system..

Many think that because it has built in VB Scripting support that one can use VB to access the data, however the VB script in Excel runs within excel and not with the VB runtime files...