Cannot free Excel automation object

[Rick]

VS 2005
Office 2000
using late bound Excel automation object.

Seems like this is a common problem, which is normally related to the
automation object creating something that is hidden from me so I don't
release it and therefore the automation object will not free itself.

Is there some way to discover what is left over after I have tried to free
this object?

From the task manager I can see that Excel is still running at the end of
the procedure. If I close the program, Excel is then freed. A "quirk" when
I run with Office 2000 installed is that if I try to open the file the
automation object created, Excel opens and never shows the document. If I
close this excel window, the Excel in memory is also closed and then I can
properly open the file. When I run in Office 2007, the file opens properly
the first time.

Any suggestions?

Rick

I'm doing something like this:

Dim xlApp As Object 'Excel app

Dim xlWbs As Object 'workbooks

Dim xlWb As Object 'workbook

Dim xlWss As Object 'worksheets

Dim xlWs As Object 'worksheet

Dim xlRange As Object 'Range object

Dim fc As Object 'formatcondition object



try

'create all the items and write to a new spreadsheet

finally

GC.Collect()

GC.WaitForPendingFinalizers()

'go through each of the objects defined above and call each one to free them

If Not obj Is Nothing Then

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)

obj = Nothing

End If

GC.Collect()

GC.WaitForPendingFinalizers()

end

 

[Rick]

I have narrowed the problem with freeing to the "Range" object.

if I have a range object like this:

Dim rangeObj as Object = worksheet.Range("A1:C1")
rangeObj.Value = "test"

then in the finally I free the object using
Marshall.FinalReleaseCOMObject... everything frees correctly, but if I add

rangeObj.Font.Bold = true
or
rangeObj.Font.Size = 12
or
rangeObj.Formula = ...

then Excel object is not freed at the end.

Can someone suggest if there is an unknown object being created here that I
need to free separately?

Rick

 

[Norman Yuan]

If you declare rangeObj outside the try...catch...finally block, like this:

Private Sub MySub()

Dim rangeObj As Object=...
....
Try
.....
Catch
....
Finally
'No matter what you do, Excel object will not be freed
'Because rangeObj is still in scope and holding reference to something
in Excel object
End Try

....

End Sub

So, you need to declare any Excel-related variable inside the Try...Catch...
block, or explicitly set any Excel-related object variables to Nothing
before Finally clause being executed.

 

[Rick]

Thanks Norman, but that did not yield any results.

I was actually defining the excel vars outside the Try Finally block, but
not initializing any of them. The initilization all happened inside the Try
Finally block. If I move the declaration outside the Try Finally block,
they are out of scope inside the Finally block, so I can't do anything with
them.

Anyway, I tried to define them all inside the Try block and then:
1. Free them all using Marshal.FinalReleaseCOMObject + var = nothing
2. Set them all to nothing.

Both of these methods left Excel open at the end of the routine.

Rick

 

[Andrei Smolin [Add-in Express]]

High Rick,

Font is a COM object. Save Font in a variable, use the variable, and,
afterwards, apply ReleaseComObject to the variable (if it is not Nothing).
Use the same approach for any property that returns a COM object. Though a
bore, this helps.

Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
www.add-in-express.com

 

[Rick]

Andrei,

As I stated I am unsing late binding so I had no help from intellisense, but
I created a small interop Excel so I could see exactly which objects were
creating a Excel.Objects. With your lead, I found several other objects
that needed to be freed as well - Font, Interior, ActiveWindow etc.

Thanks for the tip.

Rick

"Andrei Smolin [Add-in Express]" <andrei dot smolin @ add-in-express
dot_com> wrote in message news:[email protected]...