I am trying to open, edit and then close an Excel workbook.
I am not able to quit the excel workbook properly. (An instance is still visible in Task manager).
Can anyone please let me know the proper commands to quit excel completely ?
Here is the code, how I am opening the excel to edit :
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
The Excel application Quit command 'should' close the application running in the background. It is good practice to test whether there are Excel application objects open prior to closing otherwise an exception will occur.
Don't feel bad Pratik, I have this same problem. I've had it for years, and honestly, I haven't figured out a way to fix it; but at least in my case, the app that does this is used so seldom, it's not worth my time to troubleshoot. I'm going to try sparbag's suggestion to check for a running process of Excel first though. Maybe that's the magic bullet, because I've tried a lot of variations of "xlApp.Quit" (disposing all variables and setting them to Nothing, pausing and DoEvents after the file save/close events to wait for it to finish up writing, etc) and nothing seems to work.
Maybe someone more OCD than myself on here has some concrete routine code that works 100% of the time and would be kind enough to share it with us.
The following goes to extremes to ensure objects are created and disposed of properly and will handle the majority of issues e.g. file does not exists, sheet does not exist, bad cell address. It does not cover all issues but it's a very good beginning and if you spent time to understand the code you can adapt and modify as needed.
The class below does assertion on file name and sheet name. From there try-catch are used to trap exceptions and release objects upon a runtime exception being raised along with no exceptions and properly releasing objects.
Most developers will code in a code module (I am guilty of demos with code modules) but a class is the better way to go. Read all the descriptions on properties and comments in the code instead of simply adapting said code.
The most important parts are done when an exception occurs, we can inspect the property ExcelInfo and note the overridden ToString.
Example
Code:
Option Strict On
Option Infer Off
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Public Class ExcelOpenSimple
''' <summary>
''' Indicates exceptions were raised
''' </summary>
''' <returns></returns>
Public Property HasErrors As Boolean = False
''' <summary>
''' Exception information that can be inspected once
''' we have exited this class.
''' </summary>
''' <returns></returns>
''' <remarks>
''' ExceptionInformation is at bottom of this file
''' </remarks>
Public Property ExceptionInfo As New ExceptionInformation
''' <summary>
''' File name to work on
''' </summary>
''' <returns></returns>
Public Property FileName As String
''' <summary>
''' Sheet to work on
''' </summary>
''' <returns></returns>
Public Property SheetName As String
''' <summary>
''' Provides a way to return a simple list of cell data.
''' </summary>
''' <returns></returns>
''' <remarks>
''' For showing we can access cell data.
''' </remarks>
Public Property Data As String
Public Sub New()
ExceptionInfo = New ExceptionInformation
End Sub
Public Sub Open()
'
' Used to remember used objects that upon an exception
' or successfully completely are work will dispose of objects
'
Dim AnnihilationList As New List(Of Object)
If IO.File.Exists(FileName) Then
' set to true once the sheet is located
Dim Proceed As Boolean = False
' create empty objects to access Excel
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing
xlApp = New Excel.Application
AnnihilationList.Add(xlApp)
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
AnnihilationList.Add(xlWorkBooks)
xlWorkBook = xlWorkBooks.Open(FileName)
AnnihilationList.Add(xlWorkBook)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
AnnihilationList.Add(xlWorkSheets)
'
' Find sheet name user provided
'
For x As Integer = 1 To xlWorkSheets.Count
Try
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Proceed = True
Exit For
Else
AnnihilationList.Add(xlWorkSheet)
End If
Catch ex As Exception
HasErrors = True
ExceptionInfo.UnKnownException = True
ExceptionInfo.Message = $"Error finding sheet: '{ex.Message}'"
ExceptionInfo.FileNotFound = False
ExceptionInfo.SheetNotFound = False
Proceed = False
ReleaseExcelObject(xlWorkSheet)
End Try
Next
If Proceed Then
'
' Do something, in this case collect simple data
'
AnnihilationList.Add(xlWorkSheet)
Dim sb As New Text.StringBuilder
'
' in a real app we might pass in a range of cells
'
Dim Cells As String() = {"A1", "B2", "B3", "B4"}
For Each cell As String In Cells
Try
xlCells = xlWorkSheet.Range(cell)
sb.AppendLine($"{cell} = '{xlCells.Value}'")
ReleaseExcelObject(xlCells)
Catch ex As Exception
HasErrors = True
ExceptionInfo.Message = $"Error reading cell [{cell}]: '{ex.Message}'"
ExceptionInfo.FileNotFound = False
ExceptionInfo.SheetNotFound = False
ReleaseExcelObject(xlCells)
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseObjects(AnnihilationList)
Exit Sub
End Try
Next
Data = sb.ToString
Else
HasErrors = True
ExceptionInfo.SheetNotFound = True
ExceptionInfo.FileNotFound = False
End If
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseObjects(AnnihilationList)
Else
HasErrors = True
ExceptionInfo.FileNotFound = True
ExceptionInfo.SheetNotFound = False
End If
End Sub
''' <summary>
''' Given a list of Excel objects, dispose of each object
''' and while doing so check to ensure an object is not
''' null or nothing.
''' </summary>
''' <param name="AnnihilationList"></param>
<System.Diagnostics.DebuggerStepThrough()>
Private Sub ReleaseObjects(ByVal AnnihilationList As List(Of Object))
For x As Integer = 0 To AnnihilationList.Count - 1
ReleaseExcelObject(AnnihilationList(x))
Next
End Sub
''' <summary>
''' Release a Excel object while first checking to see if
''' the object is in a valid state.
''' </summary>
''' <param name="excelObject"></param>
<System.Diagnostics.DebuggerStepThrough()>
Private Sub ReleaseExcelObject(ByVal excelObject As Object)
Try
If excelObject IsNot Nothing Then
Marshal.ReleaseComObject(excelObject)
excelObject = Nothing
Else
Console.WriteLine()
End If
Catch ex As Exception
excelObject = Nothing
End Try
End Sub
End Class
Public Class ExceptionInformation
Public Property SheetNotFound As Boolean
Public Property FileNotFound As Boolean
Public Property UnKnownException As Boolean
Public Property Message As String
<System.Diagnostics.DebuggerStepThrough()>
Public Overrides Function ToString() As String
Return $"File not exist: {FileNotFound}{Environment.NewLine}Sheet not exists: {SheetNotFound}{Environment.NewLine}Message: '{Message}'"
End Function
End Class
Sample usage
Code:
Dim fileName As String = IO.Path.Combine(Application.StartupPath, "Customers.xlsx")
Dim demo As New ExcelOpenSimple With {.FileName = fileName, .SheetName = "Orders"}
demo.Open()
If demo.HasErrors Then
' in a real app it doesn't make sense to show all this but we are looking anyways
MessageBox.Show(demo.ExceptionInfo.ToString)
Else
MessageBox.Show($"Data: [{demo.Data}]")
End If
MessageBox.Show("Done - inspect Task Manager processes, there will be no instances of Excel")
Beautiful demo Karen, but nope. Even using it, I still have a hanging Microsoft Excel process still sitting in the Task Manager sometimes even after I shut down my program (and actually, the way I implemented your object as a test-bed, I have 25 processes of Excel still running in my task manager since in my case, I'm generating 25 new files based on regional sales data from a database). I was hoping the Marshal.ReleaseComObject(excelObject) was the key too.
For reference, here's my Frankenstein of your code and my existing code (other functions and methods untouched):
Code:
Public Sub GenerateReport(dte As Date, l2 As IEnumerable(Of clsSalesDataReportObject), salesRegion As String)
Dim AnnihilationList As New List(Of Object)
Dim item As clsSalesDataReportObject
' set to true once the sheet is located
Dim Proceed As Boolean = False
' create empty objects to access Excel
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing
xlApp = New Excel.Application
AnnihilationList.Add(xlApp)
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
AnnihilationList.Add(xlWorkBooks)
xlWorkBook = xlWorkBooks.Add
AnnihilationList.Add(xlWorkBook)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
AnnihilationList.Add(xlWorkSheets)
xlWorkSheet = DirectCast(xlWorkBook.Worksheets.Add, Excel.Worksheet)
AnnihilationList.Add(xlWorkSheet)
xlWorkSheet.Cells(3, 1) = String.Format("Sales data from: {0:MMMM, yyyy}", dte)
xlWorkSheet.Cells(5, 1) = String.Format("Sales Region: {0}", salesRegion)
xlWorkSheet.Range("A1:A5").Font.Size = 14
xlWorkSheet.Range("A1:A5").Font.Bold = True
xlWorkSheet.Range("A1:A5").Rows.AutoFit()
Dim intStartData As Integer = 7
xlWorkSheet.Range(String.Format("A{0}:H{0}", intStartData)).Value = {"Customer", "City", "State",
dte.ToString("MMMM"),
dte.ToString("yyyy") & " YTD",
dte.AddYears(-1).ToString("yyyy") & " YTD",
dte.AddYears(-1).ToString("yyyy") & " Total",
dte.AddYears(-2).ToString("yyyy") & " Total"}
xlWorkSheet.Range(String.Format("A{0}:H{0}", intStartData)).Font.Bold = True
Dim i As Integer = intStartData
For Each im In l2
i += 1
item = DirectCast(im, clsSalesDataReportObject)
xlWorkSheet.Cells(i, 1) = item.Customer.CustomerName.ToUpper
xlWorkSheet.Cells(i, 2) = item.City.ToUpper
xlWorkSheet.Cells(i, 3) = item.State.ToUpper
xlWorkSheet.Cells(i, 4) = item.TotalMonth.ToString("c")
xlWorkSheet.Cells(i, 5) = item.TotalYTD.ToString("c")
xlWorkSheet.Cells(i, 6) = item.TotalLastYTD.ToString("c")
xlWorkSheet.Cells(i, 7) = item.TotalLastY.ToString("c")
xlWorkSheet.Cells(i, 8) = item.TotalLastLastY.ToString("c")
Next
For d As Integer = 68 To 72 'ASCII D through H
xlWorkSheet.Range(String.Format("{1}{0}:{1}{0}", i + 1, Chr(d))).Value = String.Format("=SUM({2}{0}:{2}{1})", intStartData + 1, i, Chr(d))
Next
xlWorkSheet.Cells(i + 1, 1) = "Total Sales"
xlWorkSheet.Range(String.Format("A{0}:H{0}", i + 1)).Font.Bold = True
xlWorkSheet.Range(String.Format("A{0}:H{1}", intStartData + 1, i + 1)).Cells.Style = xlWorkBook.Styles("Currency")
Dim dataRange As Excel.Range = xlWorkSheet.Range(String.Format("A{0}:H{1}", intStartData, i))
xlWorkSheet.ListObjects.AddEx(
SourceType:=Excel.XlListObjectSourceType.xlSrcRange,
Source:=dataRange,
XlListObjectHasHeaders:=Excel.XlYesNoGuess.xlYes)
xlWorkSheet.Range(String.Format("A{0}:H{1}", intStartData, i + 1)).Columns.AutoFit()
xlWorkBook.SaveAs(FileName)
xlWorkBook.Close(SaveChanges:=True)
xlApp.UserControl = True
xlApp.Quit()
ReleaseObjects(AnnihilationList)
End Sub
I was once an advocate for the hacks to release Office COM references (i.e. follow the one dot rule and Marshal.FinalReleaseComObject) even though it seemed like a fundamental flaw in the .Net framework to require such machinations. The strange thing is that I never found any publication from MS acknowledging the problem. This never set well with me and after many searches I came across this great explanation on Garbage Collection by Han's Passant on SO and realized that in all my attempts to study the problem using the debugger, it was the fact the debugger keeping references alive and that lead to false conclusions about what is going on. He has a later post that hammer's point home even more.
The short and sweet of it is to make sure that there are no in scope references to the COM objects. The simplest way to accomplish this is to encapsulate all references in a method and call that method as on method completion all in method references are released and available for garbage collection.
The System.__ComObject type itself is not disposable, but if you look at its source code you will see that it has a method called ReleaseAllData. The comment on this method is:
// This method is called from within the EE and releases all the
// cached data for the __ComObject.
There are two other methods, ReleaseSelf and FinalReleaseSelf, that call Marshal.InternalReleaseComObject and Marshal.InternalFinalReleaseComObject respectively. I surmise that these methods are what gets called on the object by the garbage collector to release all COM references.
If you want deterministic collection of the COM objects by the garbage collector, you can call GC.Collect like this:
Code:
Private Sub ReleaseCollectableCOMObjects()
' ref: Marshal.AreComObjectsAvailableForCleanup Method
' https://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.arecomobjectsavailableforcleanup(v=vs.110).aspx
Do
GC.Collect()
GC.WaitForPendingFinalizers()
Loop While Marshal.AreComObjectsAvailableForCleanup
End Sub
So the overall pattern would be:
Code:
Private Sub WorkWithExcel()
DoExcelStuff()
ReleaseCollectableCOMObjects() ' if you need immediate release
End Sub
' This attribute is should not be needed, as the method will probably never be small enough to
' qualify for inlining, but it can not hurt to apply it
<System.Runtime.CompilerServices.MethodImpl(System.Runtime.CompilerServices.MethodImplOptions.NoInlining)>
Private Sub DoExcelStuff()
' create your Excel references and do work with them
End Sub
Beautiful demo Karen, but nope. Even using it, I still have a hanging Microsoft Excel process still sitting in the Task Manager sometimes even after I shut down my program (and actually, the way I implemented your object as a test-bed, I have 25 processes of Excel still running in my task manager since in my case, I'm generating 25 new files based on regional sales data from a database). I was hoping the Marshal.ReleaseComObject(excelObject) was the key too.
For reference, here's my Frankenstein of your code and my existing code (other functions and methods untouched):
Code:
Public Sub GenerateReport(dte As Date, l2 As IEnumerable(Of clsSalesDataReportObject), salesRegion As String)
Dim AnnihilationList As New List(Of Object)
Dim item As clsSalesDataReportObject
' set to true once the sheet is located
Dim Proceed As Boolean = False
' create empty objects to access Excel
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing
xlApp = New Excel.Application
AnnihilationList.Add(xlApp)
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
AnnihilationList.Add(xlWorkBooks)
xlWorkBook = xlWorkBooks.Add
AnnihilationList.Add(xlWorkBook)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
AnnihilationList.Add(xlWorkSheets)
xlWorkSheet = DirectCast(xlWorkBook.Worksheets.Add, Excel.Worksheet)
AnnihilationList.Add(xlWorkSheet)
xlWorkSheet.Cells(3, 1) = String.Format("Sales data from: {0:MMMM, yyyy}", dte)
xlWorkSheet.Cells(5, 1) = String.Format("Sales Region: {0}", salesRegion)
xlWorkSheet.Range("A1:A5").Font.Size = 14
xlWorkSheet.Range("A1:A5").Font.Bold = True
xlWorkSheet.Range("A1:A5").Rows.AutoFit()
Dim intStartData As Integer = 7
xlWorkSheet.Range(String.Format("A{0}:H{0}", intStartData)).Value = {"Customer", "City", "State",
dte.ToString("MMMM"),
dte.ToString("yyyy") & " YTD",
dte.AddYears(-1).ToString("yyyy") & " YTD",
dte.AddYears(-1).ToString("yyyy") & " Total",
dte.AddYears(-2).ToString("yyyy") & " Total"}
xlWorkSheet.Range(String.Format("A{0}:H{0}", intStartData)).Font.Bold = True
Dim i As Integer = intStartData
For Each im In l2
i += 1
item = DirectCast(im, clsSalesDataReportObject)
xlWorkSheet.Cells(i, 1) = item.Customer.CustomerName.ToUpper
xlWorkSheet.Cells(i, 2) = item.City.ToUpper
xlWorkSheet.Cells(i, 3) = item.State.ToUpper
xlWorkSheet.Cells(i, 4) = item.TotalMonth.ToString("c")
xlWorkSheet.Cells(i, 5) = item.TotalYTD.ToString("c")
xlWorkSheet.Cells(i, 6) = item.TotalLastYTD.ToString("c")
xlWorkSheet.Cells(i, 7) = item.TotalLastY.ToString("c")
xlWorkSheet.Cells(i, 8) = item.TotalLastLastY.ToString("c")
Next
For d As Integer = 68 To 72 'ASCII D through H
xlWorkSheet.Range(String.Format("{1}{0}:{1}{0}", i + 1, Chr(d))).Value = String.Format("=SUM({2}{0}:{2}{1})", intStartData + 1, i, Chr(d))
Next
xlWorkSheet.Cells(i + 1, 1) = "Total Sales"
xlWorkSheet.Range(String.Format("A{0}:H{0}", i + 1)).Font.Bold = True
xlWorkSheet.Range(String.Format("A{0}:H{1}", intStartData + 1, i + 1)).Cells.Style = xlWorkBook.Styles("Currency")
Dim dataRange As Excel.Range = xlWorkSheet.Range(String.Format("A{0}:H{1}", intStartData, i))
xlWorkSheet.ListObjects.AddEx(
SourceType:=Excel.XlListObjectSourceType.xlSrcRange,
Source:=dataRange,
XlListObjectHasHeaders:=Excel.XlYesNoGuess.xlYes)
xlWorkSheet.Range(String.Format("A{0}:H{1}", intStartData, i + 1)).Columns.AutoFit()
xlWorkBook.SaveAs(FileName)
xlWorkBook.Close(SaveChanges:=True)
xlApp.UserControl = True
xlApp.Quit()
ReleaseObjects(AnnihilationList)
End Sub
I see a problem why the code presented is not disposing of objects correctly.
Picking a random line of code that is a problem. In plain English having more than one dot operator will cause disposal issues.
Code:
xlWorkSheet.Range(String.Format("A{0}:H{0}", i + 1)).Font.Bold = True
This code I extracted from a demo I did for another post. Note how I broke apart TheFont, only one dot operator per attribute rather than chaining objects and properties together, this allows for proper disposal.
xlCells = xlWorkSheet.Cells
Dim EntireRow As Excel.Range = xlCells.EntireRow
EntireRow.RowHeight = RowHeight
EntireRow.ColumnWidth = ColumnHeight
ReleaseComObject(xlCells)
ReleaseComObject(EntireRow)
Simplified version to destroy objects.
Code:
Private Sub ReleaseComObject(ByVal excelObject As Object)
Try
If excelObject IsNot Nothing Then
Marshal.ReleaseComObject(excelObject)
excelObject = Nothing
End If
Catch ex As Exception
excelObject = Nothing
End Try
End Sub
1. Keep to setting one thing at a time e.g. set one property rather than two or more.
2. Test disposal as your go, begin early on. The article I mentioned above shows how to do just that. Caught the issue early is best especially when dealing with Excel as it will indeed bite you back.
Sample projects I have done, most are fairly complex and always dispose of objects correctly. Hopefully this is helpful :-)
karen, Your sample look very interesting, are they available to view/download?
No it's not. I would need to spend time with the solution which has about 20+ projects and make sure they are in good order e.g. all projects and code where done to answer questions on this forum, Stack Overflow and Microsoft forums and more times then not they are fully functional but others looking at the code may have trouble following them. Never consider it other than one something good comes out of a question I write a MSDN code sample in another solution and since the beginning of 2016 they are mostly C# which is the language I use most often.
Wow... how... thoroughly stupid. I never even heard of the "two dot rule" before, but then again, I rarely if ever use Office Interop.
It makes me wish the system was open source so I could code a proper cleanup system for it... tag all runtime callable wrappers it generates to the parent object, and on disposal, clean up the whole stack.
Yea.. after now thoroughly making my code three times it's length and looking like a college freshman's programming project, I FINALLY got the Excel process to end on completion of the routine. As I mentioned, it's a tiny part of a large data manipulator program for our ERP system, and was only used by our sales guys maybe once a month, but my level of OCD never let it sit straight with me since I could never get that Excel process to close.
Thanks much everyone. I thought I was done with stupid workarounds when I left VB6. I hope the OP figures out his issues as well from this discussion!
Last edited by Jenner; Sep 13th, 2016 at 08:50 AM.
I was once an advocate for the hacks to release Office COM references (i.e. follow the one dot rule and Marshal.FinalReleaseComObject) even though it seemed like a fundamental flaw in the .Net framework to require such machinations.
Wish I had found Hans' answer when I was looking at how to deal with Excel! So much easier than the above hoops to get it to play nicely. Thankfully, it was only one project. I'll need to try the GC route if I touch Excel again.
...
It makes me wish the system was open source so I could code a proper cleanup system for it... tag all runtime callable wrappers it generates to the parent object, and on disposal, clean up the whole stack.
You don't need to do this. The code you showed in post #6 is attempting an in method cleanup. The problem with this technique is that any implicitly created COM reference is still in scope and will sabotage your attempt at manual memory management.
Here is an example that violates all the rules of the manual COM memory management techniques (and a few others as well), yet it follows the pattern of encapsulation that I discussed in post #9, and it successfully shuts down the two Excel instance on collection of the GC cycle. The .Net framework's memory management system works to release the COM references with out any help from user memory management code. Start Task Manager and run the code and observe the Excel instance appear and disappear like they should.
VB.Net Code:
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
' encapsulating all COM references in the test methods allows them to go out of scope
' when the method ends and makes them eligible for garbage colllection
test()
testFail()
' watch the 2 Excel instances disappear from TM after the msgbox is closed
' running the GC releases all the COM references allowing Excel to shutdown
ReleaseCollectableCOMObjects()
End Sub
Sub test()
Dim app As New Excel.Application
app.DisplayAlerts = False
Try
' generate a 100 Range references
' oh-no four dots breaking the 1 or is the 2 dot rule
For Each cell As Excel.Range In DirectCast(app.Workbooks.Add.Worksheets(1), Excel.Worksheet).Range("A1:A100")
cell.Value = 1
Next
Catch ex As Exception
MsgBox(ex.Message)
Finally
app.Workbooks.Close()
app.Quit()
End Try
End Sub
Sub testFail()
Dim app As New Excel.Application
app.DisplayAlerts = False
Try
' there is no named range fred, so Excel will throw an error
For Each cell As Excel.Range In DirectCast(app.Workbooks.Add.Worksheets(1), Excel.Worksheet).Range("fred")
Loop While System.Runtime.InteropServices.Marshal.AreComObjectsAvailableForCleanup
End Sub
End Class
If it does not work for you, I'd sure like know about it.
Edit: I should mention that the pattern of method calling Excel method followed by cleanup, is just a suggestion. The important part is that the Excel method runs and exits before the cleanup code is run. You could just as well queue execution of the cleanup up code at the end of the excel method by using Control.BeginInvoke.
Last edited by TnTinMN; Sep 13th, 2016 at 01:34 PM.
TnTinMN, you're the man! I reverted all my code back to the compact, multi-dot way. Tried your method, worked perfectly. The "two dot rule" is a busted myth in my book, as are a few other headaches!
No stupid declares as "Nothing", no AnnihilationList(Of Object), no Marshal.ReleaseComObject() calls, no Excel.Application.UserControl... nothing.
The only thing I noticed (and you did do this in your example) that I need to exit the subroutine I'm using Excel in before I call ReleaseCollectableCOMObjects(). Put everything out of scope so the GC can do it's job properly.
Thank you, you've restored my faith in .NET. That means a lot to me.
Last edited by Jenner; Sep 13th, 2016 at 02:36 PM.
Do
GC.Collect()
GC.WaitForPendingFinalizers()
Loop While System.Runtime.InteropServices.Marshal.AreComObjectsAvailableForCleanup
The two dot rule is real and explained in the All-In-One Framework put together by Microsoft but they did not call it the two-dot-rule per-say. I collaborated with another developer and has been documented here. One can argue forever about best practices on this yet I mentioned early, the two-dot-rules works for me and those I have presented it too.
The AnnihilationList came from how we test in my company with EntityFramwork, instead of destroying instances of classes we are reversing all database operations once unit test have finished running.
Any ways happy to hear you are fine code-wise now :-)
I know that forcing a GC is typically frowned upon, but I clearly stated in post #9 "If you want deterministic collection of the COM objects by the garbage collector, you can call GC.Collect like this".
The two dot rule is real and explained in the All-In-One Framework put together by Microsoft but they did not call it the two-dot-rule per-say.
A link to this would be appreciated. The only thing that I could find that remotely could be inferred as meaning this, without reading every piece of information in that project is the following section in the All-In-One Code Framework Coding Guideline.
4.13.2 COM Interop
Do not force garbage collections with GC.Collect to release COM objects in performance sensitive APIs. A common approach for releasing COM objects is to set the RCW reference to null, and call System.GC.Collect followed by System.GC.WaitForPendingFinalizers. This is not recommended for performance reasons, because in many situations it can trigger the garbage collector to run too often. Code written by using this approach significantly compromises the performance and scalability of server applications. You should let the garbage collector determine the appropriate time to perform a collection.
You should use Marshal.FinalReleaseComObject or Marshal.ReleaseComObject to manage the lifetime of an RCW manually. It has much better performance than forcing garbage collections with GC.Collect.
...
This guidance is a discussion of a performance optimization technique to be used in favor of forcing a GC for deterministic COM object release and not a Rule that must be followed to ensure release. Now if you need high performance code with deterministic release of COM objects, then I can understand the creation of rules to ensure you reference each COM object so that it can subsequently be released. However, in most cases, a small performance hit of running a few collection cycles is insignificant. Most often, a single collection cycle releases all out of scope COM objects. Again, this is only if you need deterministic release.
It's been years since I came across sparse information on Excel automation and have since moved on to either XML Office for Excel or Aspose Cells. I did locate the readme file shown below. No matter I advocate the following which is in C# being this is my language of recent years Excel patterns for properly releasing memory once finished with Excel operations.
The VBAutomateExcel example demonstrates how to use Visual Basic.NET code to
create a Microsoft Excel instance, create a workbook, fill data into a
specific range, save the workbook, close the Microsoft Excel application and
then clean up unmanaged COM resources.
Office automation is based on Component Object Model (COM). When you call a
COM object of Office from managed code, a Runtime Callable Wrapper (RCW) is
automatically created. The RCW marshals calls between the .NET application
and the COM object. The RCW keeps a reference count on the COM object. If
all references have not been released on the RCW, the COM object of Office
does not quit and may cause the Office application not to quit after your
automation. In order to make sure that the Office application quits cleanly,
the sample demonstrates two solutions.
Solution1.AutomateExcel demonstrates automating Microsoft Excel application
by using Microsoft Excel Primary Interop Assembly (PIA) and explicitly
assigning each COM accessor object to a new varaible that you would
explicitly call Marshal.FinalReleaseComObject to release it at the end.
Solution2.AutomateExcel demonstrates automating Microsoft Excel application
by using Microsoft Excel PIA and forcing a garbage collection as soon as the
automation function is off the stack (at which point the RCW objects are no
longer rooted) to clean up RCWs and release COM objects.
The following steps walk through a demonstration of the Excel automation
sample that starts a Microsoft Excel instance, creates a workbook, fills
data into a specified range, saves the workbook, and quits the Microsoft
Excel application cleanly.
Step1. After you successfully build the sample project in Visual Studio 2008,
you will get the application: VBAutomateExcel.exe.
Step2. Open Windows Task Manager (Ctrl+Shift+Esc) to confirm that no
Excel.exe is running.
Step3. Run the application. It should print the following content in the
console window if no error is thrown.
Excel.Application is started
A new workbook is created
The active worksheet is renamed as Report
Filling data into the worksheet ...
Save and close the workbook
Quit the Excel application
Excel.Application is started
A new workbook is created
The active worksheet is renamed as Report
Filling data into the worksheet ...
Save and close the workbook
Quit the Excel application
Then, you will see two new workbooks in the directory of the application:
Sample1.xlsx and Sample2.xlsx. Both workbooks have a worksheet named "Report".
The worksheet has the following data in the range A1:C6.
First Name Last Name Full Name
John Smith John Smith
Tom Brown Tom Brown
Sue Thomas Sue Thomas
Jane Jones Jane Jones
Adam Johnson Adam Johnson
Step4. In Windows Task Manager, confirm that the Excel.exe process does not
exist, i.e. the Microsoft Excel intance was closed and cleaned up properly.
Step1. Create a Console application and reference the Excel Primary Interop
Assembly (PIA). To reference the Excel PIA, right-click the project and
click the "Add Reference..." button. In the Add Reference dialog, navigate to
the .NET tab, find Microsoft.Office.Interop.Excel 12.0.0.0 and click OK.
Step2. Import and rename the Excel interop namepace:
Imports Excel = Microsoft.Office.Interop.Excel
Step3. Start up an Excel application by creating an Excel.Application object.
Dim oXL As New Excel.Application
Step4. Get the Workbooks collection from Application.Workbooks and call its
Add function to create a new workbook. The Add function returns a Workbook
object.
oWBs = oXL.Workbooks
oWB = oWBs.Add()
Step5. Get the active worksheet by calling Workbook.ActiveSheet and set the
sheet's Name.
oSheet = oWB.ActiveSheet
oSheet.Name = "Report"
Step6. Construct a two-dimensional array containing some first name and last
name data and assign it to the Value2 property of a worksheet range. The
array's content will appear in the range.
Dim saNames(,) As String = {{"John", "Smith"}, _
{"Tom", "Brown"}, _
{"Sue", "Thomas"}, _
{"Jane", "Jones"}, _
{"Adam", "Johnson"}}
Step8. Call workbook.SaveAs method to save the workbook as a local file.
Then, call workbook.Close to close the workbook and call application.Quit to
quit the application.
Step9. Clean up the unmanaged COM resource. To get Excel terminated rightly,
we need to call Marshal.FinalReleaseComObject() on each COM object we used.
We can either explicitly call Marshal.FinalReleaseComObject on all accessor:
' See Solution1.AutomateExcel
If Not oRng2 Is Nothing Then
Marshal.FinalReleaseComObject(oRng2)
oRng2 = Nothing
End If
If Not oRng1 Is Nothing Then
Marshal.FinalReleaseComObject(oRng1)
oRng1 = Nothing
End If
If Not oCells Is Nothing Then
Marshal.FinalReleaseComObject(oCells)
oCells = Nothing
End If
If Not oSheet Is Nothing Then
Marshal.FinalReleaseComObject(oSheet)
oSheet = Nothing
End If
If Not oWB Is Nothing Then
Marshal.FinalReleaseComObject(oWB)
oWB = Nothing
End If
If Not oWBs Is Nothing Then
Marshal.FinalReleaseComObject(oWBs)
oWBs = Nothing
End If
If Not oXL Is Nothing Then
Marshal.FinalReleaseComObject(oXL)
oXL = Nothing
End If
and/or force a garbage collection as soon as the calling function is off the
stack (at which point these objects are no longer rooted) and then call
GC.WaitForPendingFinalizers.
' See Solution2.AutomateExcel
GC.Collect()
GC.WaitForPendingFinalizers()
' GC needs to be called twice in order to get the Finalizers called
' - the first time in, it simply makes a list of what is to be
' finalized, the second time in, it actually is finalizing. Only
' then will the object do its automatic ReleaseComObject.
GC.Collect()
GC.WaitForPendingFinalizers()
I've seen so many ways to do certain bits of programming, I'm not even sure Microsoft knows what the best practice is, nor really care. In my application, I have an asynchronous background thread open Excel, use Excel to generate 30-some odd .xlsx files containing monthly sales data, email that data to the 30 some regional sales managers, and finally, leave a status message to the user who ran the routine that everything happened as it should or shouldn't. After the user hits the "go" button. They're free to close out the module and do other things because the processing thread only needs the main application form to be open in order to drop it's status message in a designated process-queue message area. All the clean-up waiting for Excel to close may be a process hit on the GC waiting for it to happen, but not one the user ever sees as they use the application. The more important bit being Excel's process was getting shut down; where in the past, it was left hanging open indefinitely, even long after the program itself terminated.
I tried both ways to clean up Excel, and in the end, I sided with the one that seemed more "proper" for .NET; the one that didn't force me to change my programming style or significantly alter my existing routine. Would it be better to baby-step through every object reference and discretely clean everything up in the end? Possibly; I have certainly done this in the past using other technologies like DirectX; but the spirit and power of .NET in my opinion is getting results in programming done fast and efficiently. Not have to babysit every unmanaged COM object the language is creating. If I wanted to do that, I'd be programming in C++.
All I can say at this point as I'm not here to discuss the working of how this developer or that developer codes but instead offer my take on the subject which worked fine for me when developing desktop applications in the past and they were not simple applications, as with your's mine did hundreds, still running w/o need to touch them for the past nine years.
Today we have moved on to template based creation of Excel documents on the web either with Open Office.
Reply With Quote
the people who helped you and mark your forum RESOLVED when you're done! 
Originally Posted by Jenner
