Mani Pazhana
asked on
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(i Col)
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.V alue), 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.InteropServ ices.Marsh al.Release ComObject( 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(ob jDataSet.T ables(0), tempFileName)
xlStuff = Nothing
System.GC.Collect()
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
excel .exe still running in task manager
can anyone help me out
Thanks
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(i
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
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.InteropServ
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(ob
xlStuff = Nothing
System.GC.Collect()
--------------------------
excel .exe still running in task manager
can anyone help me out
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 EmergencyCertifiedPersonne l 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(fi lepath & "\OACallListReportFiles")
End If
Dim tempFileName As String
tempFileName = filepath & "\OACallListReportFiles\" & "PhoneList" & MyDate & ".xls"
''Build a connection string
Dim strConnection As String = ConfigurationSettings.AppS ettings("C onnectionS tring")
Dim objConnection As New SqlConnection(strConnectio n)
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(EmergencyCertifiedPer sonnel) & "',"
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.WaitForPendingFi nalizers()
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 .GetProces sesByName( "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(i Col)
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.InteropServ ices.Marsh al.Release ComObject( o)
Catch
Finally
o = Nothing
End Try
End Sub
End Class
Here is my code:
Private Function ExportPhoneListExcel(ByVal
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(fi
End If
Dim tempFileName As String
tempFileName = filepath & "\OACallListReportFiles\" & "PhoneList" & MyDate & ".xls"
''Build a connection string
Dim strConnection As String = ConfigurationSettings.AppS
Dim objConnection As New SqlConnection(strConnectio
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(EmergencyCertifiedPer
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
'xlStuff = Nothing
System.GC.Collect()
System.GC.WaitForPendingFi
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
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(i
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.InteropServ
Catch
Finally
o = Nothing
End Try
End Sub
End Class
you tried all of the suggestions?
ASKER
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 .GetProces sesByName( "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 Function closeExcel() As Short
Dim count As Short = 0
Dim excelInstance As System.Diagnostics.Process
Dim excelInstances() As System.Diagnostics.Process
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
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()