How to properly quit Excel

Question

I am trying to print a receipt from Excel but unable to do so. I have tried release in reverse but I seems can't find what is missing. Kindly help! Thank you!

This is what i have done so far :

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

#Region "dim"
    Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
    Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "SampleReceipt.xls")
    Dim app As Excel.Application = Nothing
    Dim books As Excel.Workbooks = Nothing
    Dim book As Excel.Workbook = Nothing
    Dim sheets As Excel.Sheets = Nothing
    Dim sheet As Excel.Worksheet = Nothing
    Dim cell As Excel.Range = Nothing
#End Region

    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
            o = Nothing
        Finally

        End Try
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            app = New Excel.Application()
            books = app.Workbooks
            book = books.Open(xlPath)
            sheets = book.Sheets
            sheet = book.Sheets(1)
            cell = sheet.Range("A1")
            cell.Value = "Lorem Ipsum"
            book.SaveAs("C:\Temp\ExcelBook.xls")
            book.Close()
            app.Quit()
        Finally
            NAR(cell)
            NAR(sheet)
            NAR(sheets)
            NAR(book)
            NAR(books)
            NAR(app)
        End Try
    End Sub
End Class

Answer 1

This is a bit of a guess, but I don't think your NAR method is doing the job perfectly well.

You have this:

Private Sub NAR(ByVal o As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
    Catch ex As Exception
        o = Nothing
    Finally

    End Try
End Sub

Now, if you have an Exception raised by ReleaseComObject then you're just swallowing it - I would think that you want to see what the exception is. You really should only handle exceptions that you can recover from. Handling the top-level Exception really is an anti-pattern. So if ReleaseComObject is raising an exception then find out what it is and deal with that specifically.

Next, if you do have an exception you are then trying to set the reference to Nothing, but you're only setting a copy of the reference to Nothing. You're passing in o using ByVal. The original reference is untouched. You want to pass it in ByRef. Try that.

Also, It might be required that you set the reference to Nothing after releasing the component - so move it out of the Catch.

And finally, the help docs on ReleaseComObject says this:

If you want to call this method to ensure that a COM component is released at a determined time, consider using the FinalReleaseComObject method instead. FinalReleaseComObject will release the underlying COM component regardless of how many times it has re-entered the CLR. The internal reference count of the RCW is incremented by one every time the COM component re-enters the CLR. Therefore, you could call the ReleaseComObject method in a loop until the value returned is zero. This achieves the same result as the FinalReleaseComObject method.

I'd try those three things.

Try this code:

Private Sub NAR(ByRef o As Object)
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o)
    o = Nothing
End Sub

And, only put in exception handling if you have a specific exception to handle, but keep the o = Nothing out of the handling code.