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.Application, wb As Excel.Workboo

xl = CreateObject("Excel.application"
wb = xl.Workbooks.Ad
wb.Close(False
ReleaseCOMObject(wb
xl.Quit(
ReleaseCOMObject(xl
xl = Nothin

end su

sub foo_doesntwork(
Dim xl As Excel.Application, wb As Excel.Workboo

xl = CreateObject("Excel.application"
wb = xl.Workbooks.Ad
wb.Close(False
ReleaseCOMObject(wb
xl.Quit(
ReleaseCOMObject(xl
xl = Nothin

end su

Public Sub ReleaseCOMObject(ByVal pobj As Object
Dim x As Intege
Tr
x = System.Runtime.InteropServices.Marshal.ReleaseComO bject(pobj
Catc
Finall
pobj = Nothin
End Tr
End Sub

[Ken Tucker [MVP]]

Hi,

Add GC.Collect after xl=nothing.

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

"IanW" <an*******@discussions.microsoft.com> wrote in message
news:D4**********************************@microsof t.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.Application, wb As Excel.Workbook

xl = CreateObject("Excel.application")
wb = xl.Workbooks.Add
wb.Close(False)
ReleaseCOMObject(wb)
xl.Quit()
ReleaseCOMObject(xl)
xl = Nothing

end sub

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

xl = CreateObject("Excel.application")
wb = xl.Workbooks.Add
wb.Close(False)
ReleaseCOMObject(wb)
xl.Quit()
ReleaseCOMObject(xl)
xl = Nothing

end sub

Public Sub ReleaseCOMObject(ByVal pobj As Object)
Dim x As Integer
Try
x = System.Runtime.InteropServices.Marshal.ReleaseComO bject(pobj)
Catch
Finally
pobj = Nothing
End Try
End Sub

[Ken Tucker [MVP]]

Hi,

Add GC.Collect after xl=nothing.

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

"IanW" <an*******@discussions.microsoft.com> wrote in message
news:D4**********************************@microsof t.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.Application, wb As Excel.Workbook

xl = CreateObject("Excel.application")
wb = xl.Workbooks.Add
wb.Close(False)
ReleaseCOMObject(wb)
xl.Quit()
ReleaseCOMObject(xl)
xl = Nothing

end sub

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

xl = CreateObject("Excel.application")
wb = xl.Workbooks.Add
wb.Close(False)
ReleaseCOMObject(wb)
xl.Quit()
ReleaseCOMObject(xl)
xl = Nothing

end sub

Public Sub ReleaseCOMObject(ByVal pobj As Object)
Dim x As Integer
Try
x = System.Runtime.InteropServices.Marshal.ReleaseComO bject(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.Applicatio
Dim xlWB As Excel.Workboo

xlWB = xlApp.Workbooks.Ad

' Workbook manipulation code goes her

xlWB.SaveAs(sPath
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.ReleaseComO bject(o
Do While i >
i = System.Runtime.InteropServices.Marshal.ReleaseComO bject(o
Loo
Catc
Finall
o = Nothin
End Tr

End Functio

End Clas

This is called from a windows form app usin

Private Sub CreateXLWorkbook(ByVal sPath As String
Dim T As New TestClass.TestClas

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.Applicatio
Dim xlWB As Excel.Workboo

xlWB = xlApp.Workbooks.Ad

' Workbook manipulation code goes her

xlWB.SaveAs(sPath
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.ReleaseComO bject(o
Do While i >
i = System.Runtime.InteropServices.Marshal.ReleaseComO bject(o
Loo
Catc
Finall
o = Nothin
End Tr

End Functio

End Clas

This is called from a windows form app usin

Private Sub CreateXLWorkbook(ByVal sPath As String
Dim T As New TestClass.TestClas

T.doXLBit(sPath

T = Nothin

End Su

/IanW

[Paul Clement]

On Mon, 26 Apr 2004 03:31:03 -0700, "IanW" <bl****@newsgroups.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.Application
¤ 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******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Mon, 26 Apr 2004 03:31:03 -0700, "IanW" <bl****@newsgroups.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.Application
¤ 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******@ameritech.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****@newsgroups.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******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Tue, 27 Apr 2004 01:21:10 -0700, "IanW" <bl****@newsgroups.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******@ameritech.net
Microsoft MVP (Visual Basic)

[IanW]

Yes it happens both in the IDE and with the executable.

[IanW]

Yes it happens both in the IDE and with the executable.

[Paul Clement]

On Wed, 28 Apr 2004 02:01:05 -0700, "IanW" <bl****@newsgroups.nospam> wrote:

¤ Yes it happens both in the IDE and with the executable.

I don't see anything in the code you provided that would cause Excel to remain in memory. You may
want to use the Process Explorer utility to verify whether your application is holding a reference
to Excel. (http://www.sysinternals.com/ntw2k/fr.../procexp.shtml)

You can also terminate the process using an API function call, although it wouldn't be the preferred
method.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal
lpWindowName As String) As Int32
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Int32, ByVal wMsg
As Int32, ByVal wParam As Int32, ByVal lParam As Int32) As Int32

Public Function TerminateExcel()

Dim ClassName As String
Dim WindowHandle As Int32
Dim ReturnVal As Int32
Const WM_QUIT = &H12

Do

ClassName = "XLMain"
WindowHandle = FindWindow(ClassName, Nothing)

If WindowHandle Then
ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
End If

Loop Until WindowHandle = 0

End Function
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Wed, 28 Apr 2004 02:01:05 -0700, "IanW" <bl****@newsgroups.nospam> wrote:

¤ Yes it happens both in the IDE and with the executable.

I don't see anything in the code you provided that would cause Excel to remain in memory. You may
want to use the Process Explorer utility to verify whether your application is holding a reference
to Excel. (http://www.sysinternals.com/ntw2k/fr.../procexp.shtml)

You can also terminate the process using an API function call, although it wouldn't be the preferred
method.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal
lpWindowName As String) As Int32
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Int32, ByVal wMsg
As Int32, ByVal wParam As Int32, ByVal lParam As Int32) As Int32

Public Function TerminateExcel()

Dim ClassName As String
Dim WindowHandle As Int32
Dim ReturnVal As Int32
Const WM_QUIT = &H12

Do

ClassName = "XLMain"
WindowHandle = FindWindow(ClassName, Nothing)

If WindowHandle Then
ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
End If

Loop Until WindowHandle = 0

End Function
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)