Solved

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

Posted on 2004-11-01
3,710 Views
Last Modified: 2013-12-03
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
0
Question by:mani_sai
    6 Comments
     
    LVL 48

    Expert Comment

    by:Mikal613
    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
     
    LVL 48

    Accepted Solution

    by:
    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
     
    LVL 48

    Expert Comment

    by:Mikal613
    0
     
    LVL 8

    Author Comment

    by:mani_sai
    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
     
    LVL 48

    Expert Comment

    by:Mikal613
    you tried all of the suggestions?
    0
     
    LVL 8

    Author Comment

    by:mani_sai
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now