Ghosete Workbook in VBA Editor

[Peter H]

 I have this code in a Excel-DNA Add-In (Version 1.8.0 in a framework 4.7.2) . This works fine, but when closing the workbook by hand in excel, a ghosted workbook remains in the ProjectExplorer in the VBA Editor. I tried many things - what else can i try :
(ChatGPT does not really help here - it seems a Interop Problem

Dim xlApp = ExcelDnaUtil.Application 
Dim wb As Object = Nothing            wb = xlApp.Workbooks.Open(control.Tag)            If wb IsNot Nothing Then Marshal.ReleaseComObject(wb)            Marshal.ReleaseComObject(xlApp)            

GC.Collect()            

GC.WaitForPendingFinalizers()

(There are more scenarios when this happens. But one - closing a workbook, i could solve via Marshal.ReleaseComObject(wb) but here it will not work. I event tried additional the following method but that did not help either. Calling it with ReleaseComAggressively(xlApp) 

Private Shared Sub ReleaseComAggressively(ByVal comObj As Object)     Try         If comObj IsNot Nothing AndAlso Marshal.IsComObject(comObj) Then             

Dim count As Integer = 0             While Marshal.ReleaseComObject(comObj) > 0                 count += 1                 If count >= 50 Then Exit While             End While         

End If     

Catch         'no act     

End Try     ' 

End Sub

Reagards Peter and tx for any Idea

[Govert van Drimmelen]

Hi Peter,

 

Here is an answer about the COM release I put on StackOverflow long ago:
https://stackoverflow.com/a/38111137/44264

 

You should never have to call Marshal.ReleaseComObject.

You should not run GC.Collect in the same method where the Com object was last referenced from .NET – under the debugger those references are artificially extended.

 

Otherwise, if you still have a problem please write back with a bit more details and I can test.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/aed114ea-06db-4373-b53c-ed5a1dc511d7n%40googlegroups.com.

[Peter H]

Hallo Govert,

I tried now to take your advice and have isolated the Excel Call, see below.

I removed all MarshallReleases

FileOpenBack is called from a button in the ribbon.

What happens:

The file is openend in Excel. 

When the file is closed in excel the file stays in the project explorer in the vba editor.

Would i open the file from a vba routine, this would not happen.

And I now even tried it with the release version. 

I can not add a picture here or? 

But in theProject Explorer one sees the vba project of every workbook.

This would disappear if the workbook is closed. But not the one opened by the procedure.

Is this enough information?

Thank you so much for looking into it. It drives me crazy.

 ' Called from the ribbon
 Public Sub FileOpenBack(control As IRibbonControl)
 ' some code to check but independent Excel so not shown here
OpenWorkbook(control.Tag)
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

End Sub



 Friend Shared Sub OpenWorkbook(fullName As String)
            Dim app = ExcelDnaUtil.Application
            Dim wb As Object
            Try
                app.EnableEvents = False
                wb = app.Workbooks.Open(fullName)

            Catch ex As Exception
                'no act
            Finally
                app.EnableEvents = True
                wb = Nothing
                app = Nothing
            End Try
End Sub

Thanks again 

Peter

[Peter H]

Hi Govert,

sorry, it seems the change does work. I have to check deeper.

TX Peter

You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/cLQ4hc7dBYk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/005dde81-7f62-4161-8c97-ab94f579ea8en%40googlegroups.com.

[Peter H]

Hi Govert,

it works now fine.

Thanks again.

There are so many posts about marshall release, that even all AI take it up :-(

I guess the most important advice was to use the GC Collect and seperate it from the Excel stuff. (Also it seems it will work many times in the same method)

And again thank you for your Excel-DNA it is magic!

Peter (an old man from Germany :-))