Setting Excel application to nothing

[Doug Glancy]

I got the following code from Francesco Balena's site, for disposing of Com
objects:

Sub SetNothing(Of T)(ByRef obj As T)

' Dispose of the object if possible

If obj IsNot Nothing AndAlso TypeOf obj Is IDisposable Then

DirectCast(obj, IDisposable).Dispose()

End If

' Decrease the reference counter, if it's a COM object

If Marshal.IsComObject(obj) Then

Marshal.ReleaseComObject(obj)

End If

obj = Nothing

End Sub



It works fine when I run it at home - the Excel instance is disposed. When
I run it at work, there is still an instance of Excel running. I can't
debug at work, so am looking for help. The only difference I'm aware of is
that the Excel file at work is on the network, at home it's on the C drive.
Here's the code from the function that calls the module above:



Function get_tasks_from_timesheet() As String()

Dim xlApp As Excel.Application

Dim xlWb As Excel.Workbook

Dim xlWs As Excel.Worksheet

....

xlApp = New Excel.Application

xlApp.Visible = True

xlWb = xlApp.Workbooks.Open(timesheet_path & "\" &
"timesheet.xls", ReadOnly:=True)

xlWs = xlWb.Worksheets(Now.ToString("MMMM yyyy"))

....

xlWb.Close(SaveChanges:=False)

xlApp.Quit()

SetNothing(xlWs)

SetNothing(xlWb)

SetNothing(xlApp)

End Function



Thanks in advance for any help,



Doug

 

[Stephany Young]

You know that the Excel objects ar COM objects so just code:

Marshal.ReleaseComObject(xlWs)

etc.

SetNothing might be all well and good but if you don't understand EXACTLY
what it is doing then don't use it.

Now ... There's a few gotchas with disposing of Excel objects.

The Marshal.ReleaseComObject returns an Integer representing the new value
of the reference counter for the object you supply as the parameter.
Although this is usually zero, I have found that with Excel objects that the
value returned can be greater than zero. If this is the case then you can
call Marshal.ReleaseComObject until it returns zero:

Dim _refcount As Integer = 1

While _refcount > 0
_refcount = Marshal.ReleaseComObject(_obj)
End While

Being COM objects, you will find that, even though it might appear that it
is, xlApp.Workbooks.Open is NOT a Shared method. What you end up with is an
instance of an Excel Workbooks object that you don't have a variable to
reference it with. I have found that you need to explicit create reference
variables for everything and then dispose of them coirrectly as well:

...

Dim xlWbs As Excel.Workbooks = xlApp.Workbooks

xlWb = xlWbs.Open(timesheet_path & "\" & "timesheet.xls", ReadOnly:=True)

...

Dim _refcount As Integer = 1

While _refcount > 0
_refcount = Marshal.ReleaseComObject(xlWb)
End While

_refcount = 1

While _refcount > 0
_refcount = Marshal.ReleaseComObject(xlWbs)
End While


Same thing again for your 'hidden' reference to xlWb.Worksheets.

If you apply the above I think you will find that the problem is solved.

 

[RobinS]

What if you want to leave the Excel spreadsheet open for the user to
access, but let go of it in your code?

I'm instantiating all of my objects, and then setting them all to Nothing
after I create a report, and then leave Excel open for the user to muck
around with.

If I run it in the same thread as the UI (and let the UI freeze up while
it's running), if the user closes Excel, the instance goes away even if the
app is still open.

If I run it in a background_worker thread, when the user closes Excel, the
instance doesn't go away until they close the app. I did implement
IDispose, but it didn't impact it.

Just mucking around, I added a GC.Collect() and now Excel goes away, but I
don't want to do this because I think it's a bad practice.

So can I do the Marshal thing even though I'm leaving Excel open and I'm
running the creation of the Excel report in a background thread, or will it
not work because I'm leaving Excel open?

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
--------------------------------------------------

 

[Stephany Young]

I know it's human nature to want to figure things out!

Yes, you're correct in saying that the GC.Collect helps things along in
'disposing' of the Excel instance. I thought I could remember off the top of
my head instead of referring to the code in question that I agonized for
days over until I got it to work.

All of the stuff I have done with Excel has involved doing stuff with the
Excel shell invisible. (It's all stuff that is all over in a small number of
seconds - if that.)

In the one case where the user does want to see the sheet at the end, after
I have 'disposed' of the Excel instance I then issue a Process.Start on the
..xls file. This means that what the user sees and interacts with has no
connection with the application in question and therefore the issues you
raise do not apply in my implementation.

One thing I have never managed to work out is why the behaviour is different
when dealing with a .xls on a local drive as compared to dealing with a .xls
on a network drive. As Doug noted, he has seen that difference in behaviour
also. I can only surmise that COM throws some extra stuff into the mix when
crossing a machine boundary. If that is the case than that would explain why
calling Marshal.ReleaseComObject returns a non-zero count on an Excel
object.

I suppose if you want to know more about what is actually happening you
would need to start delving into the PIA wrappers.

 

[RobinS]

I knew you'd figure that out. ;-)

When I open Excel, I try doing a GetObject on it, so if it's already open,
I don't create a new instance of Excel. I'm doing this because the user
might run several reports in a row, and I don't want to open up Excel
repeatedly. Because of this, closing it and then doing a Process.Start
won't really work, although it's a great solution otherwise.

Just for grins, I'll try to Marshall.ReleaseComObject and see if it closes
my Excel instance, or lets go of it, or has no impact whatsoever. What's
annoying is that it works if I run it w/o the threading.

PIA wrappers. Ugh.

I'll report back tomorrow. Too late to muck with it tonight.

Thanks,
Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------

 

[Doug Glancy]

Stephany,

Thanks for this (and to you and Robin for the interesting discussion that
followed). I won't be able to test this until Monday, but I'll post back on
this thread when I do. Am I understanding you correctly that I'll actually
be dimensioning and disposing the following 5 Excel objects:

App
Workbooks
Workbook
Worksheets
Worksheet

Doug

 

[Stephany Young]

Yes.

Stephany,

Thanks for this (and to you and Robin for the interesting discussion that
followed). I won't be able to test this until Monday, but I'll post back
on this thread when I do. Am I understanding you correctly that I'll
actually be dimensioning and disposing the following 5 Excel objects:

App
Workbooks
Workbook
Worksheets
Worksheet

Doug

 

[Doug Glancy]

I'm having trouble declaring the "hidden" worksheets collection that
Stephany referred to earlier. I've tried a few variations on the line noted
below

Dim xlApp As New Excel.Application
Dim xlWbs As Excel.Workbooks = xlApp.Workbooks
Dim xlWb As Excel.Workbook = xlWbs.Open(timesheet_path & "\" &
"timesheet.xls", ReadOnly:=True)
Dim xlWss As Excel.Worksheets = xlWb.Worksheets <------------ This one
Dim xlWs As Excel.Worksheet = xlWss.Worksheets(Now.ToString("MMMM yyyy"))
Dim rngTasks As Excel.Range =
xlWs.Range("E6:V6").SpecialCells(Excel.XlCellType.xlCellTypeVisible)

I get the following compile error:
Unable to cast COM object of type 'System.__ComObject' to interface type
'Excel.Worksheets'.

It's interesting because it seems that Excel.Workbooks is also an Interface
Type and that part compiles fine. If I remove the offending line and just
set:
Dim xlWs = xlWb.Worksheets(Now.ToString("MMMM yyyy"))
it compiles and works fine - at home that is - I still haven't tested at
work. Any thoughts on how to make the declaration work?

Doug

 

[Stephany Young]

This is the actual code I use. It uses Excel 2003 in VB.NET 2003 (Framework
1.1). Note the need to explicitly cast Workheets to Sheets and Sheet to
Worksheet. Note also that I like to ensure that the the order of the
parameters in the call to GCCom is in the reverse order to their assignment.
Whether this matters or not I haven't really tested.:

Dim _excel As Excel.Application = Nothing
Dim _books As Excel.Workbooks = Nothing
Dim _book As Excel.Workbook = Nothing
Dim _excel As Excel.Application = Nothing
Dim _sheets As Excel.Sheets = Nothing
Dim _sheet As Excel.Worksheet = Nothing
Dim _range1 As Excel.Range = Nothing
Dim _range2 As Excel.Range = Nothing
Dim _array As Array = Nothing

Try
_excel = New Excel.Application
_books = _excel.Workbooks
_book = _books.Open(_filename)
_sheets = CType(_excel.Worksheets, Excel.Sheets)
_sheet = CType(_sheets(1), Excel.Worksheet)
If _sheet.Name <> Path.GetFileNameWithoutExtension(_filename) Then
txtLog.AppendText("Invalid sheet name: " & _sheet.Name &
Environment.NewLine)
txtLog.AppendText("Should be: " &
Path.GetFileNameWithoutExtension(_filename) & Environment.NewLine)
txtLog.AppendText("Validation failed" & Environment.NewLine)
Application.DoEvents()
_book.Close()
_excel.Quit()
Cursor = Cursors.Default
Return
End If
_range1 = _sheet.Range("A1")
_range2 = _range1.CurrentRegion
_array = CType(_range2.Value, Array)
Catch _ex As Exception
txtLog.AppendText("Exception: " & _ex.ToString & Environment.NewLine)
Application.DoEvents()
Cursor = Cursors.Default
Return
Finally
_book.Close()
_excel.Quit()
GCCom(_range1, _range2, _sheet, _sheets, _book, _books, _excel)
End Try


Public Sub GCCom(ByVal ParamArray objects As Object())

Dim _references As Integer = 0

For Each _object As Object In objects
If Not _object Is Nothing Then
_references = Marshal.ReleaseComObject(_object)
While _references > 0
_references = Marshal.ReleaseComObject(_object)
End While
_object = Nothing
End If
Next

GC.Collect()

GC.WaitForPendingFinalizers()

End Sub

 

[Doug Glancy]

Stephany,

Thanks! That worked. It was the GC.Collect and WaitForPendingFinalizers
that seems to have made the difference on the network.

Doug