How to properly release Excel objects?

[UncleRonin]

I've used Excel in VB.NET for a while. The problem is that no matter how I manage the Excel objects I always have EXCEL.EXE in my active processes in Task Manager.

'Release all references to the document and close the objects.
If Not IsNothing(Worksheet) Then
Marshal.ReleaseComObject(Worksheet)
End If
If Not IsNothing(Workbook) Then
Marshal.ReleaseComObject(Workbook)
End If
If Not IsNothing(ExcelApp) Then
Marshal.ReleaseComObject(ExcelApp)
End If

This is my latest attempt. It should work and be happy but I still have a niggly EXCEL.EXE floating around while my app is open. When the app closes the EXCEL.EXE goes away.

Does anyone know how to do this correctly? I seen tons of code for this but noone has got it right yet!

 

[aBsOlUt]

Try ExcelApp.Close as well before releasing the COM Object.

 

[UncleRonin]

I use that! I'm pretty sure you should just be able to use either just NET methods or just Release...().

*sigh*

 

[vis781]

Have you closed all instances of the excel objects..

ExcelBook.close
ExcelBook = nothing
 
ExcelApp.Workbooks.close

Then marshall the release of the workbook

Marshall.ReleaseComObject(ExcelApp.WorkBooks)
ExcelApp.WorkBooks = nothing

Then Exit excel

excelapp.quit

Then Marshall the release of the Excel Object

Marshall.ReleaseComObject(ExcelApp)
ExcelApp = nothing

If all this fails, i'd be surprised, but if it does then it may be that the CLR just hasn't finshed releasing the object, this can happen for an indeterminate reason apparently. You could try forcing the GC to collect the memory used...

GC.Collect()

 

[JohnH]

excelapp.quit is what closes the automated Excel process. I do have a habit of closing the documents as I'm finished with them also, but I don't think that matters regarding the Application.Quit method.

I thought they only used ReleaseComObject in Office Add-Ins? Looks like this could be a memory issue with RCW, I just found this article http://www.vbcity.com/forums/topic.asp?tid=76537 I have never done this myself with regular external automation and also never noticed that memory didn't get released. Anything MS info here? Haven't seen this used in MS samples either.

 

[vis781]

I had never considered not using marshall.release.... when i first started out using office automation it just seemed like 'the thing to do' perhaps a bit more luck than judgement . As for the GC.collect part in that article, i have to say that i've got my doubts about that. Com objects of that size are by default longer lived, most likely automatically promoted to the third generation, whether GC.Collect is called or not, if it is garbage then it is garbage and it will be collected at some point.

 

[djjeavons]

Hi

Maybe the following link will explain the Marshal.ReleaseComObject a little more, http://www.vbcity.com/forums/topic.asp?tid=131447&#RID425666 it has a couple of links to resources which I found explained the topic quite well.


HTH

 

[preety108]

Regarding release of excel.exe

hi,
This is my code and I don't know why it's not releasing the excel object.Excel.exe is working on the background.please reply me as soon as possible.

Thanks

Public Function MakeExcelFile() As Boolean

Dim bReturn As Boolean = True

Dim xlApp As New Excel.Application

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet

'xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = xlWorkBook.Worksheets.Add 'Sheets("Sheet1")

Try
Dim dt As Date = System.DateTime.Today()
xlWorkSheet.Cells(1, 5) = "Project Detail"

With xlWorkSheet.Cells(1, 5)

With .Font
.Name = "Arial"

.FontStyle = "Bold"

.Size = 12

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = True

.ColorIndex = 5

End With

End With

xlWorkSheet.Cells(2, 5) = "Created on: " & dt.ToLongDateString

Dim nStartRow As Integer = 16

'Try
' Dim xlRange As Excel.Range = xlWorkSheet.UsedRange

' xlRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Activate()

' nStartRow = xlApp.ActiveCell.Row + 2

'Catch ex As Exception

' nStartRow = 16

'End Try

'Document parameters:
With xlWorkSheet.Rows(nStartRow)
With .Font
.Name = "Arial"

.FontStyle = "Bold"

.Size = 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = False

.ColorIndex = 1
End With
End With

xlWorkSheet.Cells(nStartRow, 2) = "Item ID"
xlWorkSheet.Cells(nStartRow, 2).Interior.ColorIndex = 15

xlWorkSheet.Cells(nStartRow, 3) = "NTP Detail ID"
xlWorkSheet.Cells(nStartRow, 3).Interior.ColorIndex = 15

xlWorkSheet.Cells(nStartRow, 4) = "Item Name"
xlWorkSheet.Cells(nStartRow, 4).Interior.ColorIndex = 15

xlWorkSheet.Cells(nStartRow, 5) = "Unit"
xlWorkSheet.Cells(nStartRow, 5).Interior.ColorIndex = 15

xlWorkSheet.Cells(nStartRow, 6) = "Submit Date"
xlWorkSheet.Cells(nStartRow, 6).Interior.ColorIndex = 15

''Drawing Comments

nStartRow += 2


If Not m_dtReportTable Is Nothing Then

For Each dtRow As DataRow In m_dtReportTable.Rows
xlWorkSheet.Cells(nStartRow, 2) = dtRow("TPItemID")
xlWorkSheet.Cells(nStartRow, 3) = dtRow("NTPDetailID")
xlWorkSheet.Cells(nStartRow, 4) = dtRow("ItemName")
xlWorkSheet.Cells(nStartRow, 5) = dtRow("Unit")
xlWorkSheet.Cells(nStartRow, 6) = Convert.ToDateTime(dtRow("InputDateTime")).Date
nStartRow += 1
Next

End If


'MakeTempFileName(TransmittalFileName, ".XLS")

xlWorkSheet.SaveAs(m_strFileName)

Catch ex As Exception
bReturn = False
Finally

xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(xlApp)

End Try

'Dim proc As System.Diagnostics.Process

'For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
' proc.Kill()
'Next

Return bReturn

End Function

Private Sub releaseObject(ByVal obj As Object)

Try
While 1
If System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) < 0 Then
Exit While
End If

End While


obj = Nothing
Catch ex As Exception
obj = Nothing
Finally

GC.Collect()

End Try

End Sub