VB.Net - Background Excel process REOPENS after closing file

Question

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

Is your declaration pasted correctly? `Dim xlSheet As Excel.Workbook = Nothing ` shouldn't be `Excel.WorkSheet`? — djv, Dec 13 '21 at 18:37
Sorry, that was a copy paste typo, it is Excel.WorkSheet. I'll fix — RuttleHead, Dec 13 '21 at 19:08

score 1 · Accepted Answer · answered Dec 13 '21 at 19:10

1

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!

answered Dec 13 '21 at 19:10

djv

15,168
7
48
72

1

inspect the call pattern in [this answer](https://stackoverflow.com/a/36578663/2592875) to see it helps clear things up with out following unnecessary rules. – TnTinMn Dec 13 '21 at 20:36
@TnTinMn your solution doesn't work in this case. Try it? I think the difference is this OP doesn't close Excel before starting the cleanup, so when the cleanup happens the objects are actually still in use. OP's Excel is still open then closed by the *user* after all this happens – djv Dec 13 '21 at 20:55
@TnTinMn I should add that your solution does work the same as the individual releasing of objects when the two dot rule is followed (it closes the process just the same). The Collect / WaitForPendingFinalizers is surely neater and for that reason is preferable over my initial solution (similar to OP) – djv Dec 13 '21 at 21:04
1

"Try it?"-initially, I did not. However now that I have, I am not seeing the issue (everything shuts down fine). The old _it works on my machine_ syndrome. That said, if one intends to switch from automation control to user control, you should be informing the Excel application object of that fact by setting the [UserControl property](https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.UserControl) to true (`xlObject.UserControl=True`). – TnTinMn Dec 13 '21 at 22:57
@TnTinMn I will try that myself tomorrow, seems like a better fix – djv Dec 14 '21 at 01:01
@TnTinMn Thank you! I made the change you suggested and when the user closes file, the background process opens for just a second, then it disappears again. I reverted the change to see if the problem was still happening and it wasn't just a coincidence and with the two dot reference the background process does appear and stays. With your change, it does not stay open. So thank you again! – RuttleHead Dec 14 '21 at 14:16

VB.Net - Background Excel process REOPENS after closing file

1 Answers1