[RESOLVED] Problem releasing Excel COM object

[VB.NETGAL]

Hi.. hope somebody can advise on what I might be doing wrong.. I've had a look on some other forums to no avail.

Basically, after I release my COM objects and close my excel application it's not closing. I know it's just a reference I'm missing somewhere or the order in releasing stuff but I can't figure it out and it's got me sighing now at 300SPM

The release object further down does this

 Public Sub releaseObject(ByVal obj As Object)
        Try
            '''''''''''''''''''''''''''''''''''''''''''
            Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

Please help

     ''''''''''''''''''''''''''''''''''''''''''''''''
        ''''''''''''''''''''''''''''''''''''''''''''''''
        Dim xlApp As excel.Application = Nothing
        Dim xlWorkBooks As excel.Workbooks = Nothing
        Dim xlWorkBook As excel.Workbook = Nothing
        Dim xlWorkSheets As excel.Sheets
        Dim xlWorkSheet As excel.Worksheet = Nothing

        xlApp = New excel.Application
        xlApp.ScreenUpdating = False
        xlWorkBooks = xlApp.Workbooks
        ''''''''''''''''''''''''''''''''''''''''''''''''
        ''''''''''''''''''''''''''''''''''''''''''''''''
        xlWorkBook = xlWorkBooks.Open(mysave)

        xlWorkSheets = xlWorkBook.Sheets
        xlWorkSheet = xlWorkSheets("tblFulfilment")



        'Find last row of table for pivot cache
        Dim LastRow As Integer
        LastRow = xlWorkSheet.Range("A1000000").End(excel.XlDirection.xlUp).Row
        Dim rng As excel.Range
        rng = xlWorkSheet.Range("A1:AB" & LastRow)
        xlWorkSheet.Names.Add(Name:="RANGEFULFILMENT", RefersTo:=rng)


        ' create second sheet for pivot table
        If xlApp.Sheets.Count() < 10 Then
            xlWorkSheet = CType(xlWorkSheets.Add(), excel.Worksheet)
        Else
            xlWorkSheet = xlWorkSheets(2)
        End If
        xlWorkSheet.Name = "SUMMARY"
        '''''''''''''''''''''''''''''''''''''''''''''

        '''''''''''''''''''''''''''''''''''''''''''
        Progress("Creating Pivot Table")
        '''''''''''''''''''''''''''''''''''''''''''

        ''''''''''''''''''''''''''''''''''''''''

        ''''''''''''''''''''''''''''''''''''''''
        ' specify first cell for pivot table on the second sheet
        Dim xlRange2 As excel.Range = CType(xlWorkSheet, excel.Worksheet).Range("A1")

        ' Create pivot cache and table
        Dim ptCache As excel.PivotCache = xlWorkBook.PivotCaches.Add(excel.XlPivotTableSourceType.xlDatabase, "RANGEFULFILMENT")
        Dim ptTable As excel.PivotTable = xlWorkSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="SUMMARY")
        Dim xlRowfield = excel.XlPivotFieldOrientation.xlRowField
        Dim xlDatafield = excel.XlPivotFieldOrientation.xlDataField
        Dim xlColumnfield = excel.XlPivotFieldOrientation.xlColumnField
        Dim xlPagefield = excel.XlPivotFieldOrientation.xlPageField
        Dim xlSum = excel.XlConsolidationFunction.xlSum
        Dim xlCount = excel.XlConsolidationFunction.xlCount
        Dim xlCompactRow = excel.XlLayoutRowType.xlCompactRow

        ' create Pivot Field, note that pivot field name is the same as column name in sheet 1
        Dim FULFILMENT As excel.PivotField = ptTable.PivotFields("FULFILMENT")


        'this formats the layout so rowfields go down the page instead of across
        xlWorkSheet.PivotTables("SUMMARY").RowAxisLayout(xlCompactRow)
        'this line is needed to make all the datafields go across the columns instead of down

        '''''''''''''''''''''''''''''''''''''''''''
        Progress("Adding Row Fields")
        '''''''''''''''''''''''''''''''''''''''''''

        'Adding row fields
        With FULFILMENT
            .Orientation = xlRowfield
        End With

        '''''''''''''''''''''''''''''''''''''''''''
        Progress("Adding Data fields")
        '''''''''''''''''''''''''''''''''''''''''''

        'Adding Data fields
        With FULFILMENT
            .Orientation = xlDatafield
            .Function = xlCount
        End With


        ''''''''''''''''''''''''''''''''''
        ''''''''''''''''''''''''''''''''''


        With ptTable
            '       Apply a style
            .TableStyle2 = "PivotStyleMedium2"
            '       Hide Field Headers
            .DisplayFieldCaptions = False
        End With


        'hide pivottable box which usually shows on the right side of page
        xlWorkBook.ShowPivotTableFieldList = False
        xlWorkSheet.Range("A1").Select()


        ''''''''''''''''''''''''''''''''''''''''''''
        '''''''''''''''''''''''''''''''''''''''''''
        releaseObject(FULFILMENT)
        releaseObject(xlRowfield)
        releaseObject(xlDatafield)
        releaseObject(xlColumnfield)
        releaseObject(xlPagefield)
        releaseObject(xlSum)
        releaseObject(xlCount)
        releaseObject(xlCompactRow)
        releaseObject(ptTable)
        releaseObject(ptCache)
        releaseObject(xlRange2)
        releaseObject(rng)
        releaseObject(LastRow)
        '''''''''''''''''''''''''''''''''''''''''''
        '''''''''''''''''''''''''''''''''''''''''''



        xlWorkBook.Save()

        xlApp.ScreenUpdating = True
        xlApp.Visible = True


        releaseObject(xlWorkSheet)
        releaseObject(xlWorkSheets)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkBooks)

        xlApp.Quit()
        releaseObject(xlApp)

[Shaggy Hiker]

It's been too long since I worked with Excel, but over in the .NET CodeBank there is an Excel base class I posted. There are two things you might find interesting in that. The first is that everything does get released in there, so it may be an example you can compare to what you are currently doing. The second is that it uses late binding, which is something you may want to consider. With late binding, it doesn't matter which version of the Excel PIAs are installed on the target computer, so you don't need a reference to an Excel library, and the code will use whatever is available.

[VB.NETGAL]

I found this answer on another forum and worked perfectly.. no need to release any objects

Sub Main()
DoOfficeStuff()
GC.Collect()
GC.WaitForPendingFinalizers()
'' Excel.exe will now be gone
'' Do more work
''...
End Sub

Sub DoOfficeStuff()
Dim xlApp As Object = CreateObject("Excel.Application")
'' etc..
End Sub

[Shaggy Hiker]

Calling GC.Collect directly is often a sign that there is a better solution, but as I said, it has been too long.

[jayinthe813]

http://stackoverflow.com/questions/1.../158752#158752

Excel does not quit because your app is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);
What I didn't know was that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use 2 dots with com objects.

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- the important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

-VVS

Never use 2 dots for com objects? I guess you learn something new every day