Quit excel instance as class member on demand

Question

I try to create a class where I want to work with an excel instance and I want to quit that excel instance on demand (as mentioned in this answer. To still be able to debug it properly I seperated the garbage colletion the following way:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim xlsInst As New ExcelInst
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub End Class

Public Class ExcelInst
    Sub New()
        Dim myxlApp As New Excel.Application
        Dim myxlWb As Excel.Workbook
        '~~> Add a new Workbook
        myxlWb = myxlApp.Workbooks.Add
        '~~> Display Excel
        myxlApp.Visible = True
        myxlApp.Quit()
    End Sub End Class

Observing the task manager the excel instance quits properly as soon as the form is loaded. BUT I am not satisfied with the Class. Of cause I want to have private variables of myxlApp and myxlWB. But if I change my class to

Public Class ExcelInst
    Private myxlApp As New Excel.Application
    Private myxlWb As Excel.Workbook

    Sub New()
        '~~> Add a new Workbook
        myxlWb = myxlApp.Workbooks.Add
        '~~> Display Excel
        myxlApp.Visible = True
        myxlApp.Quit()
    End Sub
End Class

excel doesnt close on demand anymore. Doese anyone knows why the first snippet works (according to my requirements and the second is not?

You have to call ReleaseComObject http://stackoverflow.com/questions/15697282/excel-application-not-quitting-after-calling-quit opening and closing excel in the constructor of an object seems weird. Also, in your load, the xlsInst object still exists when calling garbage collection. — the_lotus, Nov 04 '14 at 17:48
ReleaseComObject is excactely what I try to avoid because it is not recommended (see my links). And I am not looking for a workaround I just want to know why the first is working and the second is not. I will clarify that in my post. — ruedi, Nov 04 '14 at 18:41
I would assume that since you still have a reference to xlsInst before the GC.Collect and that object contains reference to excel, the GC will not collect. Try to do xlsInst = Nothing before the Collect, and maybe set the variable in the class to nothing also. — the_lotus, Nov 04 '14 at 19:14
You don't have to call ReleaseComObject. Just set myxlApp and myxlWb to Nothing, so you have no references to Excel COM objects by the time you call GC.Collect(). If you want to set xlsInst to Nothing, you again need to be careful with the debug issue which you refer to. — Govert, Nov 04 '14 at 19:25
Setting myxlApp and myxlWb to nothing solved my problem. Thanks a lot. You can write a short answer if you want, I will confirm it. — ruedi, Nov 04 '14 at 19:50

score 2 · Accepted Answer · answered Nov 04 '14 at 20:04

I would assume that since you still have a reference to xlsInst before the GC.Collect and that object contains reference to excel, the GC will not collect. Try to do xlsInst = Nothing before the Collect, and maybe set the variable in the class to nothing also.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim xlsInst As New ExcelInst
    xlsInst = Nothing
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Sub End Class

Public Class ExcelInst
    Private myxlApp As New Excel.Application
    Private myxlWb As Excel.Workbook

    Sub New()
        '~~> Add a new Workbook
        myxlWb = myxlApp.Workbooks.Add
        '~~> Display Excel
        myxlApp.Visible = True
        myxlApp.Quit()

        myxlApp = Nothing
        myxlWb = Nothing
    End Sub
End Class

I have to add, I find it strange to have that logic inside a New(). I would suggest putting this in a static function or have a .ProcessExcel() or .CloseExcel() sort of function.

Quit excel instance as class member on demand

1 Answers1