Excel Instances don't close

[IanW]

I don't know if this is in the right place but..
I am writing a VB.Net Class Library which uses Excel to produce workbooks

If I create an instance of Excel and then close it I can get .NET to destroy the instance of Excel, however as soon as I try to add a workbook and then end then close Excel the instance will not close
Code is included below
Has anyone else experienced this
What is going wrong

Sub foo_Works(
Dim xl As Excel.Applicati on, wb As Excel.Workboo

xl = CreateObject("E xcel.applicatio n"
wb = xl.Workbooks.Ad
wb.Close(False
ReleaseCOMObjec t(wb
xl.Quit(
ReleaseCOMObjec t(xl
xl = Nothin

end su

sub foo_doesntwork(
Dim xl As Excel.Applicati on, wb As Excel.Workboo

xl = CreateObject("E xcel.applicatio n"
wb = xl.Workbooks.Ad
wb.Close(False
ReleaseCOMObjec t(wb
xl.Quit(
ReleaseCOMObjec t(xl
xl = Nothin

end su

Public Sub ReleaseCOMObjec t(ByVal pobj As Object
Dim x As Intege
Tr
x = System.Runtime. InteropServices .Marshal.Releas eComObject(pobj
Catc
Finall
pobj = Nothin
End Tr
End Sub

[Ken Tucker [MVP]]

Hi,

Add GC.Collect after xl=nothing.

Ken
--------------------

"IanW" <an*******@disc ussions.microso ft.com> wrote in message
news:D4******** *************** ***********@mic rosoft.com...

I don't know if this is in the right place but...
I am writing a VB.Net Class Library which uses Excel to produce workbooks.

If I create an instance of Excel and then close it I can get .NET to
destroy the instance of Excel, however as soon as I try to add a workbook
and then end then close Excel the instance will not close.
Code is included below.
Has anyone else experienced this?
What is going wrong?

Sub foo_Works()
Dim xl As Excel.Applicati on, wb As Excel.Workbook

xl = CreateObject("E xcel.applicatio n")
wb = xl.Workbooks.Ad d
wb.Close(False)
ReleaseCOMObjec t(wb)
xl.Quit()
ReleaseCOMObjec t(xl)
xl = Nothing

end sub

sub foo_doesntwork( )
Dim xl As Excel.Applicati on, wb As Excel.Workbook

xl = CreateObject("E xcel.applicatio n")
wb = xl.Workbooks.Ad d
wb.Close(False)
ReleaseCOMObjec t(wb)
xl.Quit()
ReleaseCOMObjec t(xl)
xl = Nothing

end sub

Public Sub ReleaseCOMObjec t(ByVal pobj As Object)
Dim x As Integer
Try
x = System.Runtime. InteropServices .Marshal.Releas eComObject(pobj )
Catch
Finally
pobj = Nothing
End Try
End Sub

[Ken Tucker [MVP]]

Hi,

Add GC.Collect after xl=nothing.

Ken
--------------------

"IanW" <an*******@disc ussions.microso ft.com> wrote in message
news:D4******** *************** ***********@mic rosoft.com...

I don't know if this is in the right place but...
I am writing a VB.Net Class Library which uses Excel to produce workbooks.

If I create an instance of Excel and then close it I can get .NET to
destroy the instance of Excel, however as soon as I try to add a workbook
and then end then close Excel the instance will not close.
Code is included below.
Has anyone else experienced this?
What is going wrong?

Sub foo_Works()
Dim xl As Excel.Applicati on, wb As Excel.Workbook

xl = CreateObject("E xcel.applicatio n")
wb = xl.Workbooks.Ad d
wb.Close(False)
ReleaseCOMObjec t(wb)
xl.Quit()
ReleaseCOMObjec t(xl)
xl = Nothing

end sub

sub foo_doesntwork( )
Dim xl As Excel.Applicati on, wb As Excel.Workbook

xl = CreateObject("E xcel.applicatio n")
wb = xl.Workbooks.Ad d
wb.Close(False)
ReleaseCOMObjec t(wb)
xl.Quit()
ReleaseCOMObjec t(xl)
xl = Nothing

end sub

Public Sub ReleaseCOMObjec t(ByVal pobj As Object)
Dim x As Integer
Try
x = System.Runtime. InteropServices .Marshal.Releas eComObject(pobj )
Catch
Finally
pobj = Nothing
End Try
End Sub

[IanW]

Hi
This doesn't fix the problem
Does anyone have any more ideas
Is it failing because it is in a class library
I have simplyfied the code that doesn't work below

Public Class TestClas

Public Sub New(

End Su

Public Function doXLBit(ByVal sPath As String
Dim xlapp As New Excel.Applicati o
Dim xlWB As Excel.Workboo

xlWB = xlApp.Workbooks .Ad

' Workbook manipulation code goes her

xlWB.SaveAs(sPa th
xlWB.Close(
RCO(xlWB
xlWB = Nothin
xlApp.Quit(
RCO(xlApp
xlapp = Nothin
GC.Collect(

End Functio

Private Function RCO(ByVal o As Object
Dim i As Intege

Tr
i = System.Runtime. InteropServices .Marshal.Releas eComObject(o
Do While i >
i = System.Runtime. InteropServices .Marshal.Releas eComObject(o
Loo
Catc
Finall
o = Nothin
End Tr

End Functio

End Clas

This is called from a windows form app usin

Private Sub CreateXLWorkboo k(ByVal sPath As String
Dim T As New TestClass.TestC las

T.doXLBit(sPath

T = Nothin

End Su

/IanW

[IanW]

Hi
This doesn't fix the problem
Does anyone have any more ideas
Is it failing because it is in a class library
I have simplyfied the code that doesn't work below

Public Class TestClas

Public Sub New(

End Su

Public Function doXLBit(ByVal sPath As String
Dim xlapp As New Excel.Applicati o
Dim xlWB As Excel.Workboo

xlWB = xlApp.Workbooks .Ad

' Workbook manipulation code goes her

xlWB.SaveAs(sPa th
xlWB.Close(
RCO(xlWB
xlWB = Nothin
xlApp.Quit(
RCO(xlApp
xlapp = Nothin
GC.Collect(

End Functio

Private Function RCO(ByVal o As Object
Dim i As Intege

Tr
i = System.Runtime. InteropServices .Marshal.Releas eComObject(o
Do While i >
i = System.Runtime. InteropServices .Marshal.Releas eComObject(o
Loo
Catc
Finall
o = Nothin
End Tr

End Functio

End Clas

This is called from a windows form app usin

Private Sub CreateXLWorkboo k(ByVal sPath As String
Dim T As New TestClass.TestC las

T.doXLBit(sPath

T = Nothin

End Su

/IanW

[Paul Clement]

On Mon, 26 Apr 2004 03:31:03 -0700, "IanW" <bl****@newsgro ups.nospam> wrote:

¤ Hi,
¤ This doesn't fix the problem.
¤ Does anyone have any more ideas?
¤ Is it failing because it is in a class library?
¤ I have simplyfied the code that doesn't work below.
¤
¤ Public Class TestClass
¤
¤ Public Sub New()
¤
¤ End Sub
¤
¤ Public Function doXLBit(ByVal sPath As String)
¤ Dim xlapp As New Excel.Applicati on
¤ Dim xlWB As Excel.Workbook
¤
¤ xlWB = xlApp.Workbooks .Add
¤
¤ ' Workbook manipulation code goes here
¤

This is rather important code. Are you creating any other Excel objects without setting them to an
object reference? If you have any implicit object references this can cause Excel to remain in
memory.
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Mon, 26 Apr 2004 03:31:03 -0700, "IanW" <bl****@newsgro ups.nospam> wrote:

¤ Hi,
¤ This doesn't fix the problem.
¤ Does anyone have any more ideas?
¤ Is it failing because it is in a class library?
¤ I have simplyfied the code that doesn't work below.
¤
¤ Public Class TestClass
¤
¤ Public Sub New()
¤
¤ End Sub
¤
¤ Public Function doXLBit(ByVal sPath As String)
¤ Dim xlapp As New Excel.Applicati on
¤ Dim xlWB As Excel.Workbook
¤
¤ xlWB = xlApp.Workbooks .Add
¤
¤ ' Workbook manipulation code goes here
¤

This is rather important code. Are you creating any other Excel objects without setting them to an
object reference? If you have any implicit object references this can cause Excel to remain in
memory.
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[IanW]

Sorry I didn't make it clear, that comment is just a placeholder it is there for my memory
The instance remains open with no code there. If you create a project as it is it leaves the instance open
There will be code that generates worksheets but I can't get past the instance staying open
BTW I am using Excel 2000, would this make a difference

/IanW

[IanW]

Sorry I didn't make it clear, that comment is just a placeholder it is there for my memory
The instance remains open with no code there. If you create a project as it is it leaves the instance open
There will be code that generates worksheets but I can't get past the instance staying open
BTW I am using Excel 2000, would this make a difference

/IanW

[Paul Clement]

On Tue, 27 Apr 2004 01:21:10 -0700, "IanW" <bl****@newsgro ups.nospam> wrote:

¤ Sorry I didn't make it clear, that comment is just a placeholder it is there for my memory.
¤ The instance remains open with no code there. If you create a project as it is it leaves the instance open.
¤ There will be code that generates worksheets but I can't get past the instance staying open.
¤ BTW I am using Excel 2000, would this make a difference?

Version shouldn't make any difference. Does this happen when running from both the IDE and from the
resulting executable?
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)