Releasing COM and quitting Excel32.exe
Thanks BobRodes for that indepth explaination but as I am very new to VB.net I am still having some problems. I released my com object correclty but excel is still running in the background. Any way of shutting it down after releasing my com objects? and in a relate topic. Am I releasing them correctly? Got this from another thread.
Code:
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
Re: Releasing COM and quitting Excel32.exe
prior to releasing the objects, you need to make sure you .Close your workbook and .Quit the Excel.Application....
Usually the pattern is like this:
release worksheet object
close workboox
release workbook
quit Excel
release Excel
-tg
Re: Releasing COM and quitting Excel32.exe
I have a working example Excel automation properly using objects primer which shows how to properly access objects and dispose of them.
The following code requires a OpenDialog, button and Checkbox. If the CheckBox is unchecked then we access the active worksheet which was the last sheet accessed via opening Excel and selecting a sheet which is not what you really want. If checked you can pass an numeric but suppose you want Sheet1 which has an index of 3 instead of 1? Also xlWorkSheet when passed in a numeric will not release unless calling the GC.
Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim InitialPath As String = "C:\DotnetLand2010\Excel1\bin\Debug"
OpenFileDialog1.Title = "Please select a file to open"
OpenFileDialog1.FileName = ""
If IO.Directory.Exists(InitialPath) Then
OpenFileDialog1.InitialDirectory = InitialPath
Else
OpenFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
End If
OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)
xlApp.Visible = False
If CheckBox1.Checked Then
xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)
Else
xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
End If
Dim xlCells As Excel.Range = Nothing
Dim xlRangeToRemove = xlWorkSheet.Range("A1:B1")
Dim MyRange = xlRangeToRemove.EntireColumn
MyRange.Delete(Nothing)
xlWorkBook.SaveAs(OpenFileDialog1.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook)
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
If Not MyRange Is Nothing Then
Marshal.FinalReleaseComObject(MyRange)
MyRange = Nothing
End If
If Not xlRangeToRemove Is Nothing Then
Marshal.FinalReleaseComObject(xlRangeToRemove)
xlRangeToRemove = Nothing
End If
If Not xlWorkSheet Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
End If
If Not xlWorkBook Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBook)
xlWorkBook = Nothing
End If
If Not xlWorkBooks Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBooks)
xlWorkBooks = Nothing
End If
If Not xlApp Is Nothing Then
Marshal.FinalReleaseComObject(xlApp)
xlApp = Nothing
End If
If CheckBox1.Checked Then
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
End If
MessageBox.Show("Done")
End If
End Sub
Another thing, if you use more than one "." this will cause objects not to release.
Wrong
Code:
xlWorkSheet.Range("A1").Value
Correct
Code:
Dim SingleCellToRead = xlWorkSheet.Range("A1")
Dim A1_Value As String = String.Format("A1 = '{0}'", SingleCellToRead.Value)
Re: Releasing COM and quitting Excel32.exe
Another link which can help you in interacting with Excel and Cleaning up after you are done. See Point 24 in that link.