Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-11-01
6
Medium Priority
?
3,716 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
Comment
Question by:mani_sai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 12465570
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:
Mikal613 earned 2000 total points
ID: 12465579
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
ID: 12465586
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 8

Author Comment

by:mani_sai
ID: 12466840
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
ID: 12466944
you tried all of the suggestions?
0
 
LVL 8

Author Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

618 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