-1

I have a program that creates excel objects, writes to an excel sheet, prints, saves as, and finally quits. Of course however, there is an excel zombie I can see in the task manager that only goes away once the program is completely closed, (but is not there until the writing to excel begins.) It's driving me insane and I've read dozens of examples of how to use garbage collection to solve this issue however none seem to work. I'd prefer not to use kill process either, as that is clearly not the correct way to go about it. I also swear I had it working at one point, maybe when the program didn't, "save as" a new excel sheet. I believe the error could be, I am closing only one excel sheet while the new save as version is staying open. (Using win 10 and Excel 2016)

Dim objExcel As New Microsoft.Office.Interop.Excel.Application()
Dim objWorkbook As Excel.Workbook   'Represents a workbook object
Dim objWorksheet As Excel.Worksheet 'Represents a worksheet object
Dim OrderTemplate As String = "insert file path here"
objWorkbook = objExcel.Workbooks.Open(OrderTemplate)

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close(SaveChanges:=True)
objExcel.DisplayAlerts = True
objExcel.Quit()
objExcel = Nothing


GC.GetTotalMemory(False)
GC.Collect()
GC.WaitForPendingFinalizers()

GC.Collect()
GC.WaitForPendingFinalizers()
GC.GetTotalMemory(False)


System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorksheet)   
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub
Vermonster
  • 25
  • 8
  • Where is the code where you are defining your Excel objects? Are you defining the `WorkBooks` collection as an object? – braX Mar 21 '18 at 13:47
  • Move the GC.Collect call into the caller of this method so you don't have to get upset about the debugger. https://stackoverflow.com/a/17131389/17034 – Hans Passant Mar 21 '18 at 13:48
  • @braX sorry added, please don't vote down due to something so small. – Vermonster Mar 21 '18 at 13:56
  • I did not downvote. – braX Mar 21 '18 at 14:01
  • Maybe unrelated, but all calls would be best put inside a routine, than directly in the body of the class? I usually only see member declarations / initializations in there. I think the debugger will have trouble catching those otherwise. I didn't even think that would work. I'm not sure you can rely on the order of their execution otherwise. I also don't get why you would open one worksheet, then close the active one before quitting. Why not just use the workbook variable to close that object if that's what you're doing. – bitoolean Mar 23 '18 at 20:45

2 Answers2

0

You will want to define the WorkBooks collection as well or it will get orphaned and you wont be able to kill the parent application:

Imports Excel = Microsoft.Office.Interop.Excel

Dim objExcel As New Excel.Application
Dim objWorkbooks As Excel.Workbooks = objExcel.Workbooks
Dim objWorkbook As Excel.Workbook = objWorkbooks.Open(OrderTemplate)
Dim objWorksheet As Excel.Worksheet = DirectCast(objWorkbook.Sheets(1), Excel.Worksheet)

And then kill off the objects as well in this order:

    objWorksheet = Nothing
    objWorkbook.Close(False)
    objWorkbook = Nothing
    objWorkbooks = Nothing
    objExcel.Quit()
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)
    objExcel = Nothing
    GC.Collect()

Another article I read said to also do this to each object (in this order):

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorksheet)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbooks)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)
braX
  • 11,506
  • 5
  • 20
  • 33
  • Gave this a shot today, I saw it remove the excel zombie/open workbook before the save but then another popped up it's in place, (I think it's the orphaned parent?). I think this definitely closer to the solution. It still gets rid of the zombie when the program closes, so right now I just use the application.restart() function, however I still need to figure this out eventually to avoid zombies during exit sub, if then, statements. Thanks for helping! – Vermonster Mar 23 '18 at 18:21
  • There was a tutorial somewhere that shows you how to get a collection of open excel applications, then you run your code, then you kill off all excel applications that are not in that list you made before you ran your code. Can't remember where I found it tho. Good luck! – braX Mar 23 '18 at 19:25
  • thanks, not a bad idea, I'll definitely look into that – Vermonster Mar 23 '18 at 19:30
  • 1
    And you probably know this already, but you can make the "zombies" visible when you create them.... `objExcel.Visible = True` (False is the default) - That may help with some debugging. – braX Mar 23 '18 at 19:44
  • https://stackoverflow.com/questions/11761379/excel-process-still-runs-after-closing-in-vb-net – braX Mar 23 '18 at 20:10
0

I just tackled this earlier... there is no reason to call for release()

Just place your excel code in a private sub. example>

Private sub Excelexc()

'excel code

end sub

And then call it.

Private Sub ButtonPreview_Click(sender As Object, e As EventArgs) Handles ButtonPreview.Click


    ExcelExc()


    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()


End Sub

Also make sure you close the excel objects in the opposite way of opening them.

            objExcel1.Visible = True
            objWorkbook1.Sheets.PrintPreview()
            objWorkbook1.Close(SaveChanges:=False)
            objExcel1.Quit()

this is mine. Well, there are many ways to fix it, but in some cases its hard to even get rid of double dots and stuff like that. O well, good luck.

Kohl
  • 44
  • 7
  • Just tried this and it worked! Thanks a bunch, I suppose this makes perfect sense as to the way garbage collection is designed to work. – Vermonster Mar 23 '18 at 19:42