VB.Net - Background Excel process REOPENS after closing file-CodePudding

I have been searching all over for this problem, but I can't seem to find one that quite matches my issue.

I am creating a new excel file, filling it with data, and showing it to user. While monitoring the task manager i can see that once the file has been created and open for the user, the background process disappears as it is supposed to. (The main process is still running up top because the file is open.)

The problem I have is that once the user closes the file, Excel background process pops back up in list and won't go away until program (that generated the file) is closed.

This is the clean up that I am using;

Dim xlObject As New Excel.Application
Dim xlBook As Excel.Workbook = Nothing 
Dim xlSheet As Excel.Worksheet = Nothing 

xlBook = xlObject.Workbooks.Add
xlSheet = xlBook.Worksheets(1)

'Fill data and do some formatting 

xlBook.SaveAs("FileName")
xlObject.Visible = True 
    
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
xlSheet = Nothing 
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
xlBook = Nothing 
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlObject)
xlObject = Nothing

Is there something that I am missing? Why is the background process continuing and only go away once the creating program is closed?

This is using Visual Studios 2013, Office 365 Excel, and Windows 10 Pro

CodePudding user response：

You see, in Interop, you should avoid accessing an object through another object such as

xlBook = xlObject.Workbooks.Add

This is called the two dot rule. The reasoning is that the Workbooks object is then referenced in .NET and orphaned (and should also be killed as you have the other three)

So make a reference to the Workbooks, then kill that as well.

Dim xlApp As New Excel.Application()
Dim xlBooks = xlApp.Workbooks
Dim xlBook = xlBooks.Add
Dim xlSheet = DirectCast(xlBook.Worksheets(1), Excel.Worksheet)

Try
    xlBook.SaveAs("FileName.xlsx")
    xlApp.Visible = True
Finally
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
    xlSheet = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
    xlBook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
    xlBooks = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
    xlApp = Nothing
End Try

And, I always struggle with this, because according to the preeminent expert in vb.net on StackOverflow, HansPassant, the two dot rule is just superstition, as he said "Feel free to continue to ignore [the two dot rule], it is nonsense and causes nothing but grief" but in the case of your question, I can only make it work by following the two dot rule, and NOT by using Hans' solution:

GC.Collect()
GC.WaitForPendingFinalizers()

Perhaps Hans will see this and set us straight!