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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...

>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

[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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...

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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...

>>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]

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" <Ro****@NoSpam.yah.nonewrote in message
news:g9******************************@comcast.com. ..

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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...

>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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...

>>>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

[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.
-----------------------------------------------
"Stephany Young" <noone@localhostwrote in message
news:u2**************@TK2MSFTNGP02.phx.gbl...

>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" <Ro****@NoSpam.yah.nonewrote in message
news:g9******************************@comcast.com. ..

>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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...

>>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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...
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

[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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...

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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...

>>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]

Yes.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:uB*************@TK2MSFTNGP04.phx.gbl...

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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...

>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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...

>>>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

[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" <noone@localhostwrote in message
news:eE**************@TK2MSFTNGP05.phx.gbl...

Yes.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:uB*************@TK2MSFTNGP04.phx.gbl...

>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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...

>>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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl...
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]

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" <no********@replytogroup.comwrote in message
news:Ob**************@TK2MSFTNGP04.phx.gbl...

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" <noone@localhostwrote in message
news:eE**************@TK2MSFTNGP05.phx.gbl...

>Yes.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:uB*************@TK2MSFTNGP04.phx.gbl...

>>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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...
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.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl.. .
>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
>
>

[Doug Glancy]

Stephany,

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

Doug

"Stephany Young" <noone@localhostwrote in message
news:eU**************@TK2MSFTNGP06.phx.gbl...

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" <no********@replytogroup.comwrote in message
news:Ob**************@TK2MSFTNGP04.phx.gbl...

>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" <noone@localhostwrote in message
news:eE**************@TK2MSFTNGP05.phx.gbl...

>>Yes.
"Doug Glancy" <no********@replytogroup.comwrote in message
news:uB*************@TK2MSFTNGP04.phx.gbl...
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" <noone@localhostwrote in message
news:ef*************@TK2MSFTNGP02.phx.gbl...
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.
>
>
"Doug Glancy" <no********@replytogroup.comwrote in message
news:%2***************@TK2MSFTNGP06.phx.gbl. ..
>>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
>>
>>
>