1

I've read everything here (and some other similar questions) in order to release my COM objects, but i still have a problem.

I create, fill and save Excel files in a loop, something like this :

Private Sub Test() 

    Try
        For i = 0 To SomeIndex            

            Dim excel As Excel.Application = New Excel.Application
            Dim workbook As Excel.Workbook = excel.Application.Workbooks.Add(True)
            Dim worksheet As Excel.Worksheet = DirectCast(workbook.Worksheets("Feuil1"), Excel.Worksheet)

            '
            'Some Excel stuff here 
            '

            'Left the following lines here, maybe it is related to the range ?

            excel.Cells.EntireColumn.AutoFit()
            worksheet.Range("A3:G3").Select()
            excel.Selection.AutoFilter()
            worksheet.Range("A1").Select()


            workbook.SaveCopyAs(NomPJ)
            excel.Cells.ClearContents()

            workbook.Close(False)
            excel.Quit()

            CloseExcel(worksheet)
            CloseExcel(workbook)
            CloseExcel(excel)

        Next

    Catch ex As Exception
        MessageBox.Show(ex.ToString())           
    End Try

End Sub

Now the problem is that the first Excel instance is never released when i call CloseExcel :

Private Sub CloseExcel(ByRef ObjExcel As Object)

    Try       
        Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel)
        ObjExcel = Nothing
    Catch ex As Exception
        ObjExcel = Nothing
    Finally
        Do
            GC.Collect()
            GC.WaitForPendingFinalizers()
        Loop While Runtime.InteropServices.Marshal.AreComObjectsAvailableForCleanup
    End Try

End Sub

All the other instance close correctly, but the first Excel instance will only be closed when the entire application close. Is there something that i do wrong ?

Noob dev
  • 121
  • 10
  • @TnTinMn I've tried that "cargo cult" solution too and had the same issue. I wish the thread you linked was more prevalent https://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable/ – Jacob H Feb 05 '18 at 17:28
  • 2
    @JacobH, also unfortunate is that those that subscribe to that cult are very active here, Note that my linked question was similar to this one in that the OP followed the ordainment's of the cult and it failed, yet the followers closed that question after it was answered as a duplicate of the same bad advise this question points to. – TnTinMn Feb 05 '18 at 19:25
  • @TnTinMn Unfortunately I think it's going to keep coming up as the only "popular" thread relating to this problem has the bad code as the top accepted answer. My single downvote won't take it off the top so it's going to be a long time before that coding pattern is gone from the internet. – Jacob H Feb 05 '18 at 19:40

0 Answers0