cleaning up memory after launching Excel from button

[cliffd64]

[RESOLVED] - Cleaning up memory after launching Excel from button

Here's my little problem:

Currently I am using a button on a windows form to transfer the contents of a datagrid into an Excel Spreadsheet and view the resulting sheet in Excel:

Each time the button is pressed during the same session (for different queries etc) a new process instance of "EXCEL.EXE" is added to the task manager, eating up about 15 Mb of memory (or more), even after the user has closed the Excel app. These processes go away only when the windows program is closed. Is there a way I can have my application purge these orphaned processes within the same session after the Excel app has been closed so that I don't eat up so much memory for no reason?

I have attached my button code for transfering the data and launching Excel below:

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
' display wait cursor 
Me.Cursor = Cursors.WaitCursor
 
' various variables
Dim row As Integer
Dim col As Integer
Dim rowCount As Integer
Dim colCount As Integer
Dim cell As String
Dim rowcell As Integer
 
' Excel Variables
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
	 CType(excelBook.Worksheets(1), Excel.Worksheet)
 
' get count of rows and count of columns
rowCount = objDataSet.Tables(0).Rows.Count()
colCount = objDataSet.Tables(0).Columns.Count()
 
' add the column headings
For col = 0 To colCount - 1
	 row = 1
	 cell = GetExcelColumn(col) & row.ToString
	 excelWorksheet.Range(cell).Value = grdFieldnetData.TableStyles(0).GridColumnStyles(col).HeaderText
	 excelWorksheet.Range(cell).ColumnWidth = grdFieldnetData.TableStyles(0).GridColumnStyles(col).Width / 4
Next
 
' now add the data elements
For row = 0 To rowCount - 1
	 rowcell = row + 2
	 For col = 0 To colCount - 1
		 cell = GetExcelColumn(col) & rowcell.ToString
		 excelWorksheet.Range(cell).Value = grdFieldnetData.Item(row, col).ToString()
	 Next
Next
 
' turn off wait cursor
Me.Cursor = Cursors.Default
 
' view the spread sheet
excelApp.Visible = True
 
End Sub

 

[JuggaloBrotha]

add this sub anywhere in your project:

	Friend Sub KillProcess(ByVal ProcessName As String)
		Dim prc() As Process
		Dim ERROR_FILE_NOT_FOUND As Integer = 2
		Dim ERROR_ACCESS_DENIED As Integer = 5
		Try
			prc = System.Diagnostics.Process.GetProcessesByName(ProcessName)
			Dim eprc As IEnumerator = prc.GetEnumerator
			eprc.Reset()

			While eprc.MoveNext
			    Dim proc As Process = CType(eprc.Current, Process)
				proc.Kill()
				proc = Nothing
			End While

			eprc = Nothing
			prc = Nothing

		Catch e As System.ComponentModel.Win32Exception
			If e.NativeErrorCode = ERROR_FILE_NOT_FOUND Then
			    'MessageBox.Show(e.Message + ". Process not found.")
			    Throw New UnauthorizedAccessException(e.Message & ". Process not found.")
			Else
			    If e.NativeErrorCode = ERROR_ACCESS_DENIED Then
				    ' Note that if your word processor might generate exceptions 
				    ' such as this, which are handled first. 
				    'MessageBox.Show(e.Message + ". You do not have permission to kill this process.")
				    Throw New UnauthorizedAccessException(e.Message & ". You do not have permission to kill this process.")
				End If
			End If
		End Try
	End Sub

and use:

KillProcess(excel.exe)

this kills all instances of excel mind you so if excel should "leave" any of the spreadsheets open then you would need to re-open those after this sub runs

 

[cliffd64]

Thanks for the speedy response... I will try it out immediately

 

[kulrom]

First off, declare excellApp in module level i.e. right after Windows form designer generated code

Dim excelApp AsNew Excel.Application()

 {...} 
' Excel Variables
   Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
   Dim excelWorksheet As Excel.Worksheet = _
  CType(excelBook.Worksheets(1), Excel.Worksheet)
'other code
  excelApp.Workbooks.Close()
End Sub
 {...}

And don't forget to put this at the closing routine:

Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

excelApp.Workbooks.Close() 

excelApp.Quit()

End Sub

Cheers

 

[cliffd64]

First off, declare excellApp in module level i.e. right after Windows form designer generated code

Dim excelApp AsNew Excel.Application()

 {...} 
' Excel Variables
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = _
CType(excelBook.Worksheets(1), Excel.Worksheet)
'other code
excelApp.Workbooks.Close()
End Sub
{...}

And don't forget to put this at the closing routine:

Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
 
excelApp.Workbooks.Close() 
 
excelApp.Quit()
 
End Sub

Cheers

Thanks for your reply.

This method still leaves the EXCEL.EXE as a process in memory if the user has already exited excel manually however.

 

[cliffd64]

add this sub anywhere in your project:

	Friend Sub KillProcess(ByVal ProcessName As String)
		Dim prc() As Process
		Dim ERROR_FILE_NOT_FOUND As Integer = 2
		Dim ERROR_ACCESS_DENIED As Integer = 5
		Try
			prc = System.Diagnostics.Process.GetProcessesByName(ProcessName)
			Dim eprc As IEnumerator = prc.GetEnumerator
			eprc.Reset()
 
			While eprc.MoveNext
			 Dim proc As Process = CType(eprc.Current, Process)
				proc.Kill()
				proc = Nothing
			End While
 
			eprc = Nothing
			prc = Nothing
 
		Catch e As System.ComponentModel.Win32Exception
			If e.NativeErrorCode = ERROR_FILE_NOT_FOUND Then
			 'MessageBox.Show(e.Message + ". Process not found.")
			 Throw New UnauthorizedAccessException(e.Message & ". Process not found.")
			Else
			 If e.NativeErrorCode = ERROR_ACCESS_DENIED Then
				 ' Note that if your word processor might generate exceptions 
				 ' such as this, which are handled first. 
				 'MessageBox.Show(e.Message + ". You do not have permission to kill this process.")
				 Throw New UnauthorizedAccessException(e.Message & ". You do not have permission to kill this process.")
				End If
			End If
		End Try
	End Sub

and use:

KillProcess(excel.exe)

this kills all instances of excel mind you so if excel should "leave" any of the spreadsheets open then you would need to re-open those after this sub runs

No luck here... upon executing (I placed the KillProcess call in the closing sub for the form) the process still remained in the taskmanager.

 

[kulrom]

try this:

If Not excelApp Is Nothing Then excelApp.Quit()
If Not excelApp Is Nothing Then excelApp = Nothing

Cheers

 

[cliffd64]

try this:

If Not excelApp Is Nothing Then excelApp.Quit()
If Not excelApp Is Nothing Then excelApp = Nothing

Cheers

Hmmm... it STILL is leaving the process hanging out there when I close that specific form.

I KNOW I am executing the closing handler, but for some reason the process(s) still remains until the entire application shuts down.

 

[kulrom]

Garbage Collection: Managed languages use garbage collector for memory management. This memory model is used to help .NET projects perform and scale better than if a reference model was used. It can also help reduce the number of accidental memory leaks that are caused by circular references. While this is good, the side effect that may be confusing to those who are familiar with Visual Basic 6.0 or VBA, is that the garbage collector is non-deterministic. When you release an object reference in VBA or Visual Basic 6.0, the release immediately frees the object that is referenced and allows it to shut down. This is no longer true in .NET. Because of the garbage collector, when you release an object does not indicate when it is cleaned up, and this can keep deterministic programs (like an Office COM server) in memory longer than you might expect.

this is the link where you can find more about this:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311452

Cheers

edit: However you need to release all Excel related objects ... Kind regards

 

[cliffd64]

Actually, garbage collection WAS the answer.


I had to force the garbage collection to occur in the closing handler of the form as follows:

[size=2]GC.Collect()
 
GC.WaitForPendingFinalizers()
 
[/size]

After that bit of code, the occurrence of EXCEL.EXE is removed from memory.