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).Di spose()

End If

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

If Marshal.IsComOb ject(obj) Then

Marshal.Release ComObject(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.Applicati on

Dim xlWb As Excel.Workbook

Dim xlWs As Excel.Worksheet

....

xlApp = New Excel.Applicati on

xlApp.Visible = True

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

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

....

xlWb.Close(Save Changes:=False)

xlApp.Quit()

SetNothing(xlWs )

SetNothing(xlWb )

SetNothing(xlAp p)

End Function



Thanks in advance for any help,



Doug

[Stephany Young]

Re: Setting Excel application to nothing

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

Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns zero:

Dim _refcount As Integer = 1

While _refcount 0
_refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" , ReadOnly:=True)

...

Dim _refcount As Integer = 1

While _refcount 0
_refcount = Marshal.Release ComObject(xlWb)
End While

_refcount = 1

While _refcount 0
_refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
news:%23vLdNJoR HHA.496@TK2MSFT NGP06.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).Di spose()
>
End If
>
' Decrease the reference counter, if it's a COM object
>
If Marshal.IsComOb ject(obj) Then
>
Marshal.Release ComObject(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.Applicati on
>
Dim xlWb As Excel.Workbook
>
Dim xlWs As Excel.Worksheet
>
...
>
xlApp = New Excel.Applicati on
>
xlApp.Visible = True
>
xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
"timesheet.xls" , ReadOnly:=True)
>
xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>
...
>
xlWb.Close(Save Changes:=False)
>
xlApp.Quit()
>
SetNothing(xlWs )
>
SetNothing(xlWb )
>
SetNothing(xlAp p)
>
End Function
>
>
>
Thanks in advance for any help,
>
>
>
Doug
>
>

[RobinS]

Re: Setting Excel application to nothing

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_work er 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@localhos twrote in message
news:efk2mcoRHH A.996@TK2MSFTNG P02.phx.gbl...

You know that the Excel objects ar COM objects so just code:
>
Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns zero:
>
Dim _refcount As Integer = 1
>
While _refcount 0
_refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
ReadOnly:=True)
>
...
>
Dim _refcount As Integer = 1
>
While _refcount 0
_refcount = Marshal.Release ComObject(xlWb)
End While
>
_refcount = 1
>
While _refcount 0
_refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
news:%23vLdNJoR HHA.496@TK2MSFT NGP06.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).Di spose()
>>
> End If
>>
> ' Decrease the reference counter, if it's a COM object
>>
> If Marshal.IsComOb ject(obj) Then
>>
> Marshal.Release ComObject(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.Applicati on
>>
> Dim xlWb As Excel.Workbook
>>
> Dim xlWs As Excel.Worksheet
>>
>...
>>
> xlApp = New Excel.Applicati on
>>
> xlApp.Visible = True
>>
> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>"timesheet.xls ", ReadOnly:=True)
>>
> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>
>...
>>
> xlWb.Close(Save Changes:=False)
>>
> xlApp.Quit()
>>
> SetNothing(xlWs )
>>
> SetNothing(xlWb )
>>
> SetNothing(xlAp p)
>>
>End Function
>>
>>
>>
>Thanks in advance for any help,
>>
>>
>>
>Doug
>>
>>

>

[Stephany Young]

Re: Setting Excel application to nothing

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.Release ComObject 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" <RobinS@NoSpam. yah.nonewrote in message
news:g9qdnaGxmP B1R1_YnZ2dnUVZ_ tKjnZ2d@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_work er 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@localhos twrote in message
news:efk2mcoRHH A.996@TK2MSFTNG P02.phx.gbl...

>You know that the Excel objects ar COM objects so just code:
>>
> Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns zero:
>>
> Dim _refcount As Integer = 1
>>
> While _refcount 0
> _refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
>ReadOnly:=True )
>>
> ...
>>
> Dim _refcount As Integer = 1
>>
> While _refcount 0
> _refcount = Marshal.Release ComObject(xlWb)
> End While
>>
> _refcount = 1
>>
> While _refcount 0
> _refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
>news:%23vLdNJo RHHA.496@TK2MSF TNGP06.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).Di spose()
>>>
>> End If
>>>
>> ' Decrease the reference counter, if it's a COM object
>>>
>> If Marshal.IsComOb ject(obj) Then
>>>
>> Marshal.Release ComObject(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.Applicati on
>>>
>> Dim xlWb As Excel.Workbook
>>>
>> Dim xlWs As Excel.Worksheet
>>>
>>...
>>>
>> xlApp = New Excel.Applicati on
>>>
>> xlApp.Visible = True
>>>
>> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>>"timesheet.xl s", ReadOnly:=True)
>>>
>> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>>
>>...
>>>
>> xlWb.Close(Save Changes:=False)
>>>
>> xlApp.Quit()
>>>
>> SetNothing(xlWs )
>>>
>> SetNothing(xlWb )
>>>
>> SetNothing(xlAp p)
>>>
>>End Function
>>>
>>>
>>>
>>Thanks in advance for any help,
>>>
>>>
>>>
>>Doug
>>>
>>>

>>

>
>

[RobinS]

Re: Setting Excel application to nothing

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.Releas eComObject 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@localhos twrote in message
news:u2yFjDqRHH A.1200@TK2MSFTN GP02.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.Release ComObject 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" <RobinS@NoSpam. yah.nonewrote in message
news:g9qdnaGxmP B1R1_YnZ2dnUVZ_ tKjnZ2d@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_work er 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@localhos twrote in message
>news:efk2mcoRH HA.996@TK2MSFTN GP02.phx.gbl...

>>You know that the Excel objects ar COM objects so just code:
>>>
>> Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns
>>zero:
>>>
>> Dim _refcount As Integer = 1
>>>
>> While _refcount 0
>> _refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
>>ReadOnly:=Tru e)
>>>
>> ...
>>>
>> Dim _refcount As Integer = 1
>>>
>> While _refcount 0
>> _refcount = Marshal.Release ComObject(xlWb)
>> End While
>>>
>> _refcount = 1
>>>
>> While _refcount 0
>> _refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
>>news:%23vLdNJ oRHHA.496@TK2MS FTNGP06.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).Di spose()
>>>>
>>> End If
>>>>
>>> ' Decrease the reference counter, if it's a COM object
>>>>
>>> If Marshal.IsComOb ject(obj) Then
>>>>
>>> Marshal.Release ComObject(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.Applicati on
>>>>
>>> Dim xlWb As Excel.Workbook
>>>>
>>> Dim xlWs As Excel.Worksheet
>>>>
>>>...
>>>>
>>> xlApp = New Excel.Applicati on
>>>>
>>> xlApp.Visible = True
>>>>
>>> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>>>"timesheet.x ls", ReadOnly:=True)
>>>>
>>> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>>>
>>>...
>>>>
>>> xlWb.Close(Save Changes:=False)
>>>>
>>> xlApp.Quit()
>>>>
>>> SetNothing(xlWs )
>>>>
>>> SetNothing(xlWb )
>>>>
>>> SetNothing(xlAp p)
>>>>
>>>End Function
>>>>
>>>>
>>>>
>>>Thanks in advance for any help,
>>>>
>>>>
>>>>
>>>Doug
>>>>
>>>>
>>>

>>
>>

>

[Doug Glancy]

Re: Setting Excel application to nothing

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@localhos twrote in message
news:efk2mcoRHH A.996@TK2MSFTNG P02.phx.gbl...

You know that the Excel objects ar COM objects so just code:
>
Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns zero:
>
Dim _refcount As Integer = 1
>
While _refcount 0
_refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" , ReadOnly:=True)
>
...
>
Dim _refcount As Integer = 1
>
While _refcount 0
_refcount = Marshal.Release ComObject(xlWb)
End While
>
_refcount = 1
>
While _refcount 0
_refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
news:%23vLdNJoR HHA.496@TK2MSFT NGP06.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).Di spose()
>>
> End If
>>
> ' Decrease the reference counter, if it's a COM object
>>
> If Marshal.IsComOb ject(obj) Then
>>
> Marshal.Release ComObject(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.Applicati on
>>
> Dim xlWb As Excel.Workbook
>>
> Dim xlWs As Excel.Worksheet
>>
>...
>>
> xlApp = New Excel.Applicati on
>>
> xlApp.Visible = True
>>
> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>"timesheet.xls ", ReadOnly:=True)
>>
> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>
>...
>>
> xlWb.Close(Save Changes:=False)
>>
> xlApp.Quit()
>>
> SetNothing(xlWs )
>>
> SetNothing(xlWb )
>>
> SetNothing(xlAp p)
>>
>End Function
>>
>>
>>
>Thanks in advance for any help,
>>
>>
>>
>Doug
>>
>>

>

[Stephany Young]

Re: Setting Excel application to nothing

Yes.


"Doug Glancy" <nobobyhere@rep lytogroup.comwr ote in message
news:uBWVootRHH A.388@TK2MSFTNG P04.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@localhos twrote in message
news:efk2mcoRHH A.996@TK2MSFTNG P02.phx.gbl...

>You know that the Excel objects ar COM objects so just code:
>>
> Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns zero:
>>
> Dim _refcount As Integer = 1
>>
> While _refcount 0
> _refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
>ReadOnly:=True )
>>
> ...
>>
> Dim _refcount As Integer = 1
>>
> While _refcount 0
> _refcount = Marshal.Release ComObject(xlWb)
> End While
>>
> _refcount = 1
>>
> While _refcount 0
> _refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
>news:%23vLdNJo RHHA.496@TK2MSF TNGP06.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).Di spose()
>>>
>> End If
>>>
>> ' Decrease the reference counter, if it's a COM object
>>>
>> If Marshal.IsComOb ject(obj) Then
>>>
>> Marshal.Release ComObject(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.Applicati on
>>>
>> Dim xlWb As Excel.Workbook
>>>
>> Dim xlWs As Excel.Worksheet
>>>
>>...
>>>
>> xlApp = New Excel.Applicati on
>>>
>> xlApp.Visible = True
>>>
>> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>>"timesheet.xl s", ReadOnly:=True)
>>>
>> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>>
>>...
>>>
>> xlWb.Close(Save Changes:=False)
>>>
>> xlApp.Quit()
>>>
>> SetNothing(xlWs )
>>>
>> SetNothing(xlWb )
>>>
>> SetNothing(xlAp p)
>>>
>>End Function
>>>
>>>
>>>
>>Thanks in advance for any help,
>>>
>>>
>>>
>>Doug
>>>
>>>

>>

>
>

[Doug Glancy]

Re: Setting Excel application to nothing

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.Applicati on
Dim xlWbs As Excel.Workbooks = xlApp.Workbooks
Dim xlWb As Excel.Workbook = xlWbs.Open(time sheet_path & "\" &
"timesheet.xls" , ReadOnly:=True)
Dim xlWss As Excel.Worksheet s = xlWb.Worksheets <------------ This one
Dim xlWs As Excel.Worksheet = xlWss.Worksheet s(Now.ToString( "MMMM yyyy"))
Dim rngTasks As Excel.Range =
xlWs.Range("E6: V6").SpecialCel ls(Excel.XlCell Type.xlCellType Visible)

I get the following compile error:
Unable to cast COM object of type 'System.__ComOb ject' to interface type
'Excel.Workshee ts'.

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@localhos twrote in message
news:eE6vC3vRHH A.3520@TK2MSFTN GP05.phx.gbl...

Yes.
>
>
"Doug Glancy" <nobobyhere@rep lytogroup.comwr ote in message
news:uBWVootRHH A.388@TK2MSFTNG P04.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@localhos twrote in message
>news:efk2mcoRH HA.996@TK2MSFTN GP02.phx.gbl...

>>You know that the Excel objects ar COM objects so just code:
>>>
>> Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns zero:
>>>
>> Dim _refcount As Integer = 1
>>>
>> While _refcount 0
>> _refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
>>ReadOnly:=Tru e)
>>>
>> ...
>>>
>> Dim _refcount As Integer = 1
>>>
>> While _refcount 0
>> _refcount = Marshal.Release ComObject(xlWb)
>> End While
>>>
>> _refcount = 1
>>>
>> While _refcount 0
>> _refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
>>news:%23vLdNJ oRHHA.496@TK2MS FTNGP06.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).Di spose()
>>>>
>>> End If
>>>>
>>> ' Decrease the reference counter, if it's a COM object
>>>>
>>> If Marshal.IsComOb ject(obj) Then
>>>>
>>> Marshal.Release ComObject(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.Applicati on
>>>>
>>> Dim xlWb As Excel.Workbook
>>>>
>>> Dim xlWs As Excel.Worksheet
>>>>
>>>...
>>>>
>>> xlApp = New Excel.Applicati on
>>>>
>>> xlApp.Visible = True
>>>>
>>> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>>>"timesheet.x ls", ReadOnly:=True)
>>>>
>>> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>>>
>>>...
>>>>
>>> xlWb.Close(Save Changes:=False)
>>>>
>>> xlApp.Quit()
>>>>
>>> SetNothing(xlWs )
>>>>
>>> SetNothing(xlWb )
>>>>
>>> SetNothing(xlAp p)
>>>>
>>>End Function
>>>>
>>>>
>>>>
>>>Thanks in advance for any help,
>>>>
>>>>
>>>>
>>>Doug
>>>>
>>>>
>>>

>>
>>

>

[Stephany Young]

Re: Setting Excel application to nothing

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.Applicati on = Nothing
Dim _books As Excel.Workbooks = Nothing
Dim _book As Excel.Workbook = Nothing
Dim _excel As Excel.Applicati on = 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.Applicati on
_books = _excel.Workbook s
_book = _books.Open(_fi lename)
_sheets = CType(_excel.Wo rksheets, Excel.Sheets)
_sheet = CType(_sheets(1 ), Excel.Worksheet )
If _sheet.Name <Path.GetFileNa meWithoutExtens ion(_filename) Then
txtLog.AppendTe xt("Invalid sheet name: " & _sheet.Name &
Environment.New Line)
txtLog.AppendTe xt("Should be: " &
Path.GetFileNam eWithoutExtensi on(_filename) & Environment.New Line)
txtLog.AppendTe xt("Validation failed" & Environment.New Line)
Application.DoE vents()
_book.Close()
_excel.Quit()
Cursor = Cursors.Default
Return
End If
_range1 = _sheet.Range("A 1")
_range2 = _range1.Current Region
_array = CType(_range2.V alue, Array)
Catch _ex As Exception
txtLog.AppendTe xt("Exception: " & _ex.ToString & Environment.New Line)
Application.DoE vents()
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.Release ComObject(_obje ct)
While _references 0
_references = Marshal.Release ComObject(_obje ct)
End While
_object = Nothing
End If
Next

GC.Collect()

GC.WaitForPendi ngFinalizers()

End Sub



"Doug Glancy" <nobobyhere@rep lytogroup.comwr ote in message
news:ObzTGn8RHH A.1200@TK2MSFTN GP04.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.Applicati on
Dim xlWbs As Excel.Workbooks = xlApp.Workbooks
Dim xlWb As Excel.Workbook = xlWbs.Open(time sheet_path & "\" &
"timesheet.xls" , ReadOnly:=True)
Dim xlWss As Excel.Worksheet s = xlWb.Worksheets <------------ This one
Dim xlWs As Excel.Worksheet = xlWss.Worksheet s(Now.ToString( "MMMM yyyy"))
Dim rngTasks As Excel.Range =
xlWs.Range("E6: V6").SpecialCel ls(Excel.XlCell Type.xlCellType Visible)
>
I get the following compile error:
Unable to cast COM object of type 'System.__ComOb ject' to interface type
'Excel.Workshee ts'.
>
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@localhos twrote in message
news:eE6vC3vRHH A.3520@TK2MSFTN GP05.phx.gbl...

>Yes.
>>
>>
>"Doug Glancy" <nobobyhere@rep lytogroup.comwr ote in message
>news:uBWVootRH HA.388@TK2MSFTN GP04.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@localhos twrote in message
>>news:efk2mcoR HHA.996@TK2MSFT NGP02.phx.gbl.. .
>>>You know that the Excel objects ar COM objects so just code:
>>>>
>>> Marshal.Release ComObject(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.Release ComObject 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.Release ComObject until it returns
>>>zero:
>>>>
>>> Dim _refcount As Integer = 1
>>>>
>>> While _refcount 0
>>> _refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
>>>ReadOnly:=Tr ue)
>>>>
>>> ...
>>>>
>>> Dim _refcount As Integer = 1
>>>>
>>> While _refcount 0
>>> _refcount = Marshal.Release ComObject(xlWb)
>>> End While
>>>>
>>> _refcount = 1
>>>>
>>> While _refcount 0
>>> _refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
>>>news:%23vLdN JoRHHA.496@TK2M SFTNGP06.phx.gb l...
>>>>>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).Di spose()
>>>>>
>>>> End If
>>>>>
>>>> ' Decrease the reference counter, if it's a COM object
>>>>>
>>>> If Marshal.IsComOb ject(obj) Then
>>>>>
>>>> Marshal.Release ComObject(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.Applicati on
>>>>>
>>>> Dim xlWb As Excel.Workbook
>>>>>
>>>> Dim xlWs As Excel.Worksheet
>>>>>
>>>>...
>>>>>
>>>> xlApp = New Excel.Applicati on
>>>>>
>>>> xlApp.Visible = True
>>>>>
>>>> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>>>>"timesheet. xls", ReadOnly:=True)
>>>>>
>>>> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>>>>
>>>>...
>>>>>
>>>> xlWb.Close(Save Changes:=False)
>>>>>
>>>> xlApp.Quit()
>>>>>
>>>> SetNothing(xlWs )
>>>>>
>>>> SetNothing(xlWb )
>>>>>
>>>> SetNothing(xlAp p)
>>>>>
>>>>End Function
>>>>>
>>>>>
>>>>>
>>>>Thanks in advance for any help,
>>>>>
>>>>>
>>>>>
>>>>Doug
>>>>>
>>>>>
>>>>
>>>
>>>

>>

>
>

[Doug Glancy]

Re: Setting Excel application to nothing

Stephany,

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

Doug

"Stephany Young" <noone@localhos twrote in message
news:eUD5br9RHH A.4260@TK2MSFTN GP06.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.Applicati on = Nothing
Dim _books As Excel.Workbooks = Nothing
Dim _book As Excel.Workbook = Nothing
Dim _excel As Excel.Applicati on = 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.Applicati on
_books = _excel.Workbook s
_book = _books.Open(_fi lename)
_sheets = CType(_excel.Wo rksheets, Excel.Sheets)
_sheet = CType(_sheets(1 ), Excel.Worksheet )
If _sheet.Name <Path.GetFileNa meWithoutExtens ion(_filename) Then
txtLog.AppendTe xt("Invalid sheet name: " & _sheet.Name &
Environment.New Line)
txtLog.AppendTe xt("Should be: " &
Path.GetFileNam eWithoutExtensi on(_filename) & Environment.New Line)
txtLog.AppendTe xt("Validation failed" & Environment.New Line)
Application.DoE vents()
_book.Close()
_excel.Quit()
Cursor = Cursors.Default
Return
End If
_range1 = _sheet.Range("A 1")
_range2 = _range1.Current Region
_array = CType(_range2.V alue, Array)
Catch _ex As Exception
txtLog.AppendTe xt("Exception: " & _ex.ToString & Environment.New Line)
Application.DoE vents()
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.Release ComObject(_obje ct)
While _references 0
_references = Marshal.Release ComObject(_obje ct)
End While
_object = Nothing
End If
Next
>
GC.Collect()
>
GC.WaitForPendi ngFinalizers()
>
End Sub
>
>
>
"Doug Glancy" <nobobyhere@rep lytogroup.comwr ote in message
news:ObzTGn8RHH A.1200@TK2MSFTN GP04.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.Applicati on
>Dim xlWbs As Excel.Workbooks = xlApp.Workbooks
>Dim xlWb As Excel.Workbook = xlWbs.Open(time sheet_path & "\" &
>"timesheet.xls ", ReadOnly:=True)
>Dim xlWss As Excel.Worksheet s = xlWb.Worksheets <------------ This one
>Dim xlWs As Excel.Worksheet = xlWss.Worksheet s(Now.ToString( "MMMM yyyy"))
>Dim rngTasks As Excel.Range =
>xlWs.Range("E6 :V6").SpecialCe lls(Excel.XlCel lType.xlCellTyp eVisible)
>>
>I get the following compile error:
>Unable to cast COM object of type 'System.__ComOb ject' to interface type
>'Excel.Workshe ets'.
>>
>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@localhos twrote in message
>news:eE6vC3vRH HA.3520@TK2MSFT NGP05.phx.gbl.. .

>>Yes.
>>>
>>>
>>"Doug Glancy" <nobobyhere@rep lytogroup.comwr ote in message
>>news:uBWVootR HHA.388@TK2MSFT NGP04.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@localhos twrote in message
>>>news:efk2mco RHHA.996@TK2MSF TNGP02.phx.gbl. ..
>>>>You know that the Excel objects ar COM objects so just code:
>>>>>
>>>> Marshal.Release ComObject(xlWs)
>>>>>
>>>>etc.
>>>>>
>>>>SetNothin g 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.Release ComObject 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.Release ComObject until it returns
>>>>zero:
>>>>>
>>>> Dim _refcount As Integer = 1
>>>>>
>>>> While _refcount 0
>>>> _refcount = Marshal.Release ComObject(_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(time sheet_path & "\" & "timesheet.xls" ,
>>>>ReadOnly:=T rue)
>>>>>
>>>> ...
>>>>>
>>>> Dim _refcount As Integer = 1
>>>>>
>>>> While _refcount 0
>>>> _refcount = Marshal.Release ComObject(xlWb)
>>>> End While
>>>>>
>>>> _refcount = 1
>>>>>
>>>> While _refcount 0
>>>> _refcount = Marshal.Release ComObject(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" <nobobyhere@rep lytogroup.comwr ote in message
>>>>news:%23vLd NJoRHHA.496@TK2 MSFTNGP06.phx.g bl...
>>>>>>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).Di spose()
>>>>>>
>>>>> End If
>>>>>>
>>>>> ' Decrease the reference counter, if it's a COM object
>>>>>>
>>>>> If Marshal.IsComOb ject(obj) Then
>>>>>>
>>>>> Marshal.Release ComObject(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.Applicati on
>>>>>>
>>>>> Dim xlWb As Excel.Workbook
>>>>>>
>>>>> Dim xlWs As Excel.Worksheet
>>>>>>
>>>>>...
>>>>>>
>>>>> xlApp = New Excel.Applicati on
>>>>>>
>>>>> xlApp.Visible = True
>>>>>>
>>>>> xlWb = xlApp.Workbooks .Open(timesheet _path & "\" &
>>>>>"timesheet .xls", ReadOnly:=True)
>>>>>>
>>>>> xlWs = xlWb.Worksheets (Now.ToString(" MMMM yyyy"))
>>>>>>
>>>>>...
>>>>>>
>>>>> xlWb.Close(Save Changes:=False)
>>>>>>
>>>>> xlApp.Quit()
>>>>>>
>>>>> SetNothing(xlWs )
>>>>>>
>>>>> SetNothing(xlWb )
>>>>>>
>>>>> SetNothing(xlAp p)
>>>>>>
>>>>>End Function
>>>>>>
>>>>>>
>>>>>>
>>>>>Thanks in advance for any help,
>>>>>>
>>>>>>
>>>>>>
>>>>>Doug
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>

>>
>>

>