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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Mikal613Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.