Cannot fully dispose Application and Temp file after iPartFactory.ExcelWorkSheet

[josh.nieman]

I have an iPartFactory object "ScrewFactory"
I pass the contents of that ScrewFactory.ExcelWorkSheet to an array.

Imports Inventor
Imports System.Runtime.CompilerServices
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

...

Dim getSheet As Excel.Worksheet = ScrewFactory.ExcelWorkSheet
Dim getRange As Excel.Range = getSheet.Range("A2:K2659")
Marshal.ReleaseComObject(getSheet)

Dim excArr = getRange.Value2

For row As Integer = 1 To excArr.GetUpperBound(0)
'<doing work here>
Next

Marshal.ReleaseComObject(getRange)

1 - I end up with files in "C:\Users\<user>\AppData\Local\Temp\" such as "tt2ybmciapzyhdpa3s2thbdqbiIff.xls" which is a copy of the contents of the factory table.

2 - I end up with a residual process of Excel running in the background that I can only kill via Task Manager.

How can I properly dispose of these files and application?
For the files I can brute force delete files in that directory if the file type is .xlsx and the filesize is <value> which will pretty well cover it, even if it's super crude.  I'd rather do it "cleanly" and better than that, though.
For the application, I'm clueless because I never really create a deliberate instance of Excel.exe - that happens within Inventor's guts, as best I can tell, to deal with the factory table.  So I have no assigned object to kill/quit/dispose of.

Ideas?

[tobias.orlow]

Hi @josh.nieman ,

First - you shouldn't have to call Marshal.ReleaseComObject(...) or Marshal.FinalReleaseComObject(...) when doing Excel interop. Fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references.

Second - if you want to ensure that the COM references to an out-of-process COM object is cleaned up when your process ends (so that the Excel process will close), you need to ensure that the Garbage Collector runs. You do this correctly with calls to GC.Collect() and GC.WaitForPendingFinalizers().

Third - in case you are running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So GC.Collect() calls are not effective for cleaning objects like your "getRange" from the same method. You should split the code doing the COM interop from the GC cleanup into separate methods.

I'm not sure exactly either what cleanup is done internally by Inventor but you can give the above points a try to see if it helps you.

Please let me know if we have to look into it in more detail.

Thank you,

Tobias

[josh.nieman]

Yea, the Marshal functions were more of a desperate attempt 🙂

I appreciate your input.  I will try your advise and report back.  I'm not terribly familiar with garbage collection practices.  I can certainly separate out these tasks to a separate method and see if that helps with proper disposal.

[josh.nieman]

@tobias.orlow , thanks for leading me to learn about garbacecollection, more.

I'm still not really getting it, I think.  I've tried implementing the methods you suggested in multiple ways, and no luck so far.  Both the excel process and temp .xls files remain.

Here's a basic outline of what I'm doing.  These are methods during the Form_Load even for a form that is left open (in-house design-assistance application)

I've deleted my calls to garbage collection - where would you suggest I make the call to force GC? (code at bottom)

Am I doing something that is otherwise not letting my application release the running background instance of Excel?  I am also getting residual .xls files in the C:\Users\jnieman\AppData\Local\Temp\ folder, such as "keivxecbmctrcopmvn3nu6vennIff.xls"

A problem this creates is with another button on my application that opens an existing .xls on our server.  It opens that file, but some users are reporting that the "keivxecbmctrcopmvn3nu6vennIff.xls" temp file it created earlier is being opened as well.

    Public ScrewFactory As iPartFactory
    Public ScrewObjList As New List(Of Screw)

    Dim ScrewDoc As Inventor.PartDocument
    Dim excArr As Array    

<Extension()>
    Public Function XLStoArray(ipart As iPartFactory) As Array
        Dim getSheet As Excel.Worksheet = ipart.ExcelWorkSheet
        Dim getRange As Excel.Range = getSheet.Range("A2:K2659")
        excArr = getRange.Value2
        getSheet = Nothing
        getRange = Nothing
        Return excArr
    End Function

Public Sub GetScrews()
        InvApp = InventorTools.GetInvApp()
        Try
            ScrewDoc = InvApp.Documents.ItemByName(ScrewSource)
        Catch ex As Exception
            ScrewDoc = InvApp.Documents.Open(ScrewSource, False)
        End Try
        Dim PCD As PartComponentDefinition = ScrewDoc.ComponentDefinition
        ScrewFactory = PCD.iPartFactory
        excArr = ScrewFactory.XLStoArray
'do work with excArr
End Sub

    Public Class Screw
        Public Property RowNum As Integer
        Public Property Member As String
        Public Property Type As String
        Public Property Thread As String
        Public Property srtSize As String
        Public Sub New()
        End Sub
    End Class

[Anonymous]

Hello Josh!

So I have encountered the same problem when creating addins. I don't know about the copy in the temp folder, but Excel still runs in the background. I tried to close the workbook, then quit the app, then empty the objects, nothing worked.

I tried everything and then in deepest end of google I've come across an article, which, of course, I can't seem to find now.

The gist of it was this: When you are using the Excel application outside of excel, relative references does not seem to work, because excel still "thinks" it will be needed in the future so it keeps the application open. 

Relative reference for example:

range("A2") etc. 

Now if you are in another application you should always put the necessary parent object before it like this: 

worksheet.range("A2") but what worksheet, you have to have a workbook open, but what workbook, you need to run a Excel application for this.

• Excel application
  • Workbook
    • Worksheet
      • Ranges
      • Cells
      • Etc

In my case I did the following:

'Created the reference to a new Excel application
Dim oExcel As New Excel.Application

'Created a new workbook
Dim oWB As Excel.Workbook
oWB = oExcel.Workbooks.Add

'Set it to visible so I can see what's happening
oExcel.Visible = True

'Created the reference to a worksheet
Dim oWS As Excel.Worksheet

Then

oWS = oWB.ActiveSheet
or 
oWS = oWB.Sheets(1)
or whichever sheet you want to work with in your case the ScrewFactory.Worksheet

And after this I use everything with this reference, for example:

oWS.range("A2").value 2 = whatever you want to. 

When you are outside of excel (in visual studio and you are just using the excel application just like you are using Inventor application you have to specifically reference your objects, where did they come from)

Now, in your case:

I can see that iParts have this property ExcelWorksheet, but you still have to reference that.

So I would create an Excel application (see above)

Then reference to the workbook (since you could have multiple workbooks running, you have to specify which one do you wanna work with) then you can use that worksheet object.

In the API it says: iPartFactory.ExcelWorkSheet() As Object

Now object is the least specific object there is. 

So in your code before this 

Dim getSheet As Excel.Worksheet = ScrewFactory.ExcelWorkSheet

 I would create an excel application object, then a workbook application object then you can follow up with your 

getSheet object which I can see is a Worksheet type.

So it would go like this:

'Create the reference to a new Excel application
Dim oExcel As New Excel.Application

'Create a new workbook
Dim oWB As Excel.Workbook
oWB = oExcel.Workbooks.Add

'Set it to visible so I can see what's happening
oExcel.Visible = True

'Created the reference to a worksheet
Dim getSheet as Excel.Worksheet = ScrewFactory.ExcelWorkSheet

Now when you type: getSheet.Range("A2").value2 the program will know which excel application, which workbook and what sheet you are referring to.

Oh and don't use the below version:

Dim oXLApp As Object
oXLApp = CreateObject("Excel.Application") 

 I hope this helps, it worked for me after dealing with this problem for over 4 days and it kinda makes sense.

Accept it as a solution if it helped, or give it a thumbs up 🙂 

[josh.nieman]

I appreciate your help!

It still appears there is a "gap" in logic between creating a new Excel.Application, an Excel.Workbook object, and the subsequent call to the iPartFactory.ExcelWorksheet.  The Worksheet isn't tied to the Workbook in any direct way, and I don't seem to be able to do that, unless I'm mistaken.  I don't know if that's any problem, or if it's just to get a "controlled" or "accessible" process of Excel open, before I start calling up a worksheet.  

Regardless...the problem seems to persist, however.  I notice that on my machine, it now creates two Excel.exe processes.  It's still creating the background process when I call the iPartFactory.ExcelWorksheet, even though there's already an active Excel instance prior to that.  I don't know why that would be.  That is not the normal behavior when opening Excel files for me.  It's like it doesn't care that I already started Excel. 🙂

I'm a bit lost on this.  😕  Kind of a pain.

All I want to do is get that iPartFactory worksheet silently, invisibly, copy it to an array, all without the user knowing anything is happening, and with at least a decent level of cleanup.

If it fills the TEMP folder with files, I don't much care.

If it leaves background processes going, I'd prefer it not... but if that's unavoidable, it's acceptable.

If it causes Excel to pop up and show the iPartFactory worksheet copy, that's not cool.

If this is of any complication, the iPartFactory -is- Vaulted, and is always checked out by an dummy account used to control access to library/standard documents.

Current code for context, including desperate acts that may not be doing anything helpful:

    <Extension()>
    Public Function XLStoArray(ipartfacto As iPartFactory) As Array
        Dim appExcel As New Excel.Application
        Dim xlsWB As Excel.Workbook = appExcel.Workbooks.Add
        appExcel.Visible = False 'flip when testing is done
        appExcel.DisplayAlerts = False
        Dim getSheet As Excel.Worksheet = ipartfacto.ExcelWorkSheet
        'Dim getsheet As Excel.Worksheet = xlsWB.Worksheets.Add(ipartfacto.ExcelWorkSheet)
        Dim getRange As Excel.Range = getSheet.Range("A2:K2659")
        excArr = getRange.Value2
        getSheet = Nothing
        getRange = Nothing
        ScrewDoc.Close()
        xlsWB.Close(False)
        appExcel.Quit()
        Return excArr
    End Function

[josh.nieman · Accepted solution]

I managed to get this to work.  This will still leave junk in the TEMP folder.  This will occasionally still leave an excel process running, consuming about 5mb memory and almost no processor load.  User reports no popup of excel, so I'm calling this good enough.  TEMP folder trash is sometimes just the fact of MS Windows life.

I was getting the Vault dialog popping up about modifying a library factory, even though I'm only reading it... so I just turned off dialogs in Inventor while reading it.  /shrug

Instead of creating a new excel application, I'm instead getting the existing one by backing up the object tree from the iPartFactory.ExcelWorksheet to the .application and .workbook objects, and closing/quitting both at the end.  Didn't get me everything I wanted but might have had a hand in getting me 'close enough'.
I wish I could have solved the problem of the residual Excel.exe background process Inventor creates, but as they don't say in France, "say the vee".

<Extension()>
    Public Function XLStoArray(ipartfacto As iPartFactory) As Array
        Dim getSheet As Excel.Worksheet = ipartfacto.ExcelWorkSheet
        Dim factWB As Excel.Workbook = getSheet.Parent
        Dim factExc As Excel.Application = getSheet.Application
        factExc.Visible = False
        factExc.DisplayAlerts = False
        Dim getRange As Excel.Range = getSheet.Range("A2:K2659")
        excArr = getRange.Value2

        ScrewDoc.Close()
        factWB.Close(False)
        factExc.Quit()
        Return excArr
    End Function

[tobias.orlow]

Hi @josh.nieman , 

some more input from my side if you still wanna give it a shot:

I did some testing just opening iPartFactory Worksheets and closing them and monitored %temp% as well as running processes. It seems to properly be closed and cleaned up (tested in 2019&2020), so the chance is high that there's still some of your interop objects referencing something when trying to close.

Please make sure you set all your Excel objects (workbook, sheet, range etc.) to 'Nothing' in your routine after you are done using them, so GC can dispose them properly.

Also, below an example on how I made it work previously:

I declared the Excel objects used in my app in the beginning of the class like

    Dim _Excel As Microsoft.Office.Interop.Excel.Application = Nothing
    Dim _excelStarted As Boolean = False
    Dim oBook As Workbook = Nothing
    Dim oSheet As Worksheet = Nothing
    Dim ValueCell As Range = Nothing

_excelstarted just being a check in case a user pressed some button already and Excel is loaded already. Once Excel is started and the _Excel.Ready Property returns 'true', I set it to 'true'.

Then the Sub to close looks like this and can be called when you're done with Excel:

#Region "Close Excel"
    Private Sub CloseExcel()
        Try
            If Not _Excel Is Nothing Then
                If Not oBook Is Nothing Then
                    oBook.Save() 'not sure if you want to save yours
                    oBook.Close()
                    Dim workbooks As Workbooks = _Excel.Workbooks
                    workbooks.Close()
                End If
                _Excel.Quit()
                _Excel = Nothing
                _excelStarted = False
            End If
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
            WriteError(ex.ToString, "Close Excel Error:")
        Finally
            'You would also need to call GC.Collect and GC.WaitForPendingFinalizers twice when working with Office COM objects since the first time we call the methods,
            'we only release objects that we are not referencing with our own variables.
            'The second time the two methods are called Is because the RCW for each COM object needs to run a finalizer that actually fully removes the COM Object from memory.
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
        End Try
    End Sub
#End Region

The 'WriteError' function in the Catch-statement simply being a helper function which writes errors to a logfile (in the 'filepath' location, in my case Temp Folder) so I can analyze errors on other machines easier. 'format' is a String storing a custom DateTime format. This is fully optional but could be interesting for you as well as you mentioned some users experiencing different behaviors.

   Public Function WriteError(ByVal ex As String, ByVal code As String)
        If System.IO.File.Exists(filepath) Then
            Using stwriter As New IO.StreamWriter(filepath, True)
                stwriter.WriteLine("-------------------START-------------" + DateTime.Now.ToString(format))
                stwriter.WriteLine(code)
                stwriter.WriteLine(ex)
                stwriter.WriteLine("--------------------END--------------" + DateTime.Now.ToString(format))
            End Using
        Else
            Dim stwriter As IO.StreamWriter = IO.File.CreateText(filepath)
            stwriter.WriteLine("-------------------START-------------" + DateTime.Now.ToString(format))
            stwriter.WriteLine(code)
            stwriter.WriteLine(ex)
            stwriter.WriteLine("--------------------END--------------" + DateTime.Now.ToString(format))
            stwriter.Close()
        End If
    End Function

Hopefully, this helps you improve your tool further.

Best regards,

Tobias

[josh.nieman]

@tobias.orlow thanks so much for following up!

I appreciate the explanation and advice - I'll implement these ideas into my code, and ensure I'm cleaning up properly and see how it goes.  I'll report back with my findings.

I'm using 2020.1 so hopefully my results mirror yours!

[CattabianiI]

Hi @josh.nieman, hi @tobias.orlow. 
I've the same situation in Inventor 2023, and these snippets seems not to work?
Have you updated to a newer Inv version and tried them?

To be honest I don't think it's a real issue, but a customer complained about that excel process and I found out this thread.