excel.exe running in task manager--need to kill the process from my .net code

Hello Experts,

I am having excel .exe still running in task manager.

here is my code:

---------------------------------------------------------------------

Public Class ExcelStuff

    'Place all Code working with Excel here
    Public Sub Export_To_Excel(ByVal dtSource As System.Data.DataTable, ByVal tempFileName As String)

        Dim iRowCount As Integer = dtSource.Rows.Count
        Dim iColCount As Integer = dtSource.Columns.Count
        Dim oData(iRowCount, iColCount) As Object

        Dim iRow As Integer, iCol As Integer
        For iRow = 0 To iRowCount - 1
            For iCol = 0 To iColCount - 1
                oData(iRow, iCol) = dtSource.Rows(iRow).Item(iCol)
            Next
        Next

        ' Start Excel and get Application object
        Dim oExcel As Excel.Application = New Excel.Application


        ' Get a new workbook
        Dim oBook As Excel._Workbook = CType(oExcel.Workbooks.Add(Missing.Value), Excel._Workbook)
        Dim oSheet As Excel._Worksheet = CType(oBook.ActiveSheet, Excel._Worksheet)

        Dim oRange As Excel.Range = oSheet.Range("A1")
        oRange = oRange.Resize(iRowCount, iColCount)
        oRange.Value = oData


        oSheet.SaveAs(tempFileName)
        oExcel.Workbooks.Close()
        oExcel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        System.GC.Collect()

       
        oSheet = Nothing
        oBook = Nothing
        oExcel = Nothing

    End Sub


End Class


---------------------------------------------------------------------------------


calling class code is here:

Dim xlStuff As New ExcelStuff
        Call xlStuff.Export_To_Excel(objDataSet.Tables(0), tempFileName)
        xlStuff = Nothing
        System.GC.Collect()

--------------------------------------------------------------------------------
excel .exe still running in task manager

can anyone help me out

Thanks
LVL 8
mani_saiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mikal613Connect With a Mentor Commented:
and

do you have excel installed?

to your original question, closing excel..

    Private Function closeExcel() As Short
        Dim count As Short = 0
        Dim excelInstance As System.Diagnostics.Process
        Dim excelInstances() As Process = System.Diagnostics.Process.GetProcessesByName("Excel")
        For Each excelInstance In excelInstances
            Try
                excelInstance.Close()      '<-- you can use close or kill .. up to you
                excelInstance.Kill()
                count += 1
            Catch ex As Exception
            End Try
        Next
        return count                 '<--- this is gonna tell you the # of excel instances closes
    End Function
0
 
Mikal613Commented:
First, when you set a reference to something like the Excel Object library, .NET will look for something called the Microsoft.Office.Interop assembly.  If it doesn't find it, it will build that assembly, but Microsoft says that they don't recommend using the assembly as it is built from Visual Studio.  Instead, they have the XP Office Interop Assemblies available for download:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp

Go to that URL and click on the MSDN Download link

After installing the Interop Assemblies as they recommend.  Then try changing your code to the sample as found on this page:

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B317109

If that doesn't work, then there is one final thing to consider.... if the code where you are creating your Excel or other Office object is located in a Global Module, then keep in mind that Global Modules are never unloaded from memory once they have been used in your code.  And, since the module remains in memory, even the local variables inside of your routine are never fully released.

So, what you can do instead is move your excel code into a Class.  Then, instead of calling the function as a function in a global module, call it as a method of the class object.  So, let's say you put your Excel code into a class called MyOffice, in a function called ExportToExcel.

    ' create a new instance of your class
    Dim x as New MyOffice
    ' call the method which creates the excel objects
    x.ExportToExcel("This is a test")
    ' when done, this should release the class from memory
    x = Nothing
    ' this forces the garbage collector to reclaim the memory
    GC.Collect()
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mani_saiAuthor Commented:
I tried as per your suggestion, still having the same problem.

Here is my code:

 Private Function ExportPhoneListExcel(ByVal GroupNo As String, ByVal DecisionMakers As String, ByVal RecoveryExperts As String, ByVal OperationalStaff As String, ByVal ExecutiveStaff As String, ByVal EmergencyCertifiedPersonnel As String, ByVal VendorSupport As String, ByVal EPLO As String)


        Dim MyDate As String = TimeStamp()

        'Check for folder if Does not Exists, Create It in("Mydocuments")
        Dim oFile As System.IO.File
        Dim oFolder As System.IO.Directory
        Dim oWrite As System.IO.StreamWriter

        If Not oFolder.Exists(filepath & "\OACallListReportFiles") Then
            oFolder.CreateDirectory(filepath & "\OACallListReportFiles")
        End If

        Dim tempFileName As String
        tempFileName = filepath & "\OACallListReportFiles\" & "PhoneList" & MyDate & ".xls"

        ''Build a connection string
        Dim strConnection As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim objConnection As New SqlConnection(strConnection)


        Dim sqlString As String
        sqlString = "Execute GetCTCPhoneList "
        sqlString = sqlString & "'" & Trim(GroupNo) & "',"
        sqlString = sqlString & "'" & Trim(DecisionMakers) & "',"
        sqlString = sqlString & "'" & Trim(RecoveryExperts) & "',"
        sqlString = sqlString & "'" & Trim(OperationalStaff) & "',"
        sqlString = sqlString & "'" & Trim(ExecutiveStaff) & "',"
        sqlString = sqlString & "'" & Trim(EmergencyCertifiedPersonnel) & "',"
        sqlString = sqlString & "'" & Trim(VendorSupport) & "',"
        sqlString = sqlString & "'" & Trim(EPLO) & "'"


        Dim objCommand As New SqlCommand(sqlString, objConnection)
        objConnection.Open()
        Dim objAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
        Dim objDataSet As DataSet = New DataSet
        objAdapter.Fill(objDataSet)


        ' Dim xlStuff As New ExcelStuff
        Call Export_To_Excel(objDataSet.Tables(0), tempFileName)
        'xlStuff = Nothing
        System.GC.Collect()
        System.GC.WaitForPendingFinalizers()

        Call closeExcel()

        'CleanUp
        objConnection.Close()
        objConnection = Nothing

    End Function

    Private Function closeExcel() As Short
        Dim count As Short = 0
        Dim excelInstance As System.Diagnostics.Process
        Dim excelInstances() As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessesByName("Excel")
        For Each excelInstance In excelInstances
            Try
                excelInstance.Close()      '<-- you can use close or kill .. up to you
                excelInstance.Kill()
                count += 1
            Catch ex As Exception
            End Try
        Next
        Return count                 '<--- this is gonna tell you the # of excel instances closes
    End Function

    Private Sub Export_To_Excel(ByVal dtSource As System.Data.DataTable, ByVal tempFileName As String)

        Dim iRowCount As Integer = dtSource.Rows.Count
        Dim iColCount As Integer = dtSource.Columns.Count
        Dim oData(iRowCount, iColCount) As Object

        Dim iRow As Integer, iCol As Integer
        For iRow = 0 To iRowCount - 1
            For iCol = 0 To iColCount - 1
                oData(iRow, iCol) = dtSource.Rows(iRow).Item(iCol)
            Next
        Next

        Dim oExcel As New Excel.Application
        Dim oBook As Excel.Workbook = oExcel.Workbooks.Add
        Dim oSheet As Excel.Worksheet = oExcel.ActiveSheet

        Dim oRange As Excel.Range = oSheet.Range("A1")
        oRange = oRange.Resize(iRowCount, iColCount)
        oRange.Value = oData

        oSheet.SaveAs(tempFileName)

        NAR(oSheet)
        oBook.Close(False)
        NAR(oBook)
        oExcel.Quit()
        NAR(oExcel)

    End Sub

    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

End Class






0
 
Mikal613Commented:
you tried all of the suggestions?
0
 
mani_saiAuthor Commented:
Thanks Mikal613 , this code works for me at last. I use kill only.


 Private Function closeExcel() As Short
        Dim count As Short = 0
        Dim excelInstance As System.Diagnostics.Process
        Dim excelInstances() As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessesByName("Excel")
        For Each excelInstance In excelInstances
            Try
                excelInstance.Close()      '<-- you can use close or kill .. up to you
                excelInstance.Kill()
                count += 1
            Catch ex As Exception
            End Try
        Next
        Return count                 '<--- this is gonna tell you the # of excel instances closes
    End Function
0
All Courses

From novice to tech pro — start learning today.