Excel instance staying open

[Atchoum]

I have a DLL that opens an Excel workbook and add-in.
If I close the app that calls the DLL before closing Excel, when I close
Excel everything is fine.
But if I close Excel while the app is still open, an instance of Excel
remains in the background. Then even if I close the app, it remains there.
What am I suppose to do, in my DLL, to completely release Excel. If I use
oExcel.Quit() then I shut down Excel...

TIA

[Herfried K. Wagner [MVP]]

* "Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> scripsit:

But if I close Excel while the app is still open, an instance of Excel
remains in the background. Then even if I close the app, it remains there.
What am I suppose to do, in my DLL, to completely release Excel. If I use
oExcel.Quit() then I shut down Excel...

PRB: Office Application Does Not Quit After Automation from Visual Studio .NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

[Atchoum]

Herfried,

Thanks. I can't seem to get it right thought. My program's objective is
simply to open a workboook and launch an add-in. I don,t want to close
anything once I am done, but I want obviously the program to release Excel.
Here is excerpt of my code. What am I doing wrong?

Private sub Main()
Dim oExcel As Object
Dim oWorkbookAddin As Object
Dim oWorkbook As Object
Dim oBooks As Object
oExcel = CreateObject("Excel.Application")
oBooks = oExcel.Workbooks
oWorkbookAddin = oExcel.Workbooks
oWorkbook = oExcel.Workbooks
oWorkbookAddin = oBooks.Open(cAppPath &
"\Addin_Excel_Templates.xla", AddToMRU:=False)
oWorkbook = oBooks.Open(Template, Editable:=OpenForEdition)
oWorkbook.Application.Run("Addin_Excel_Templates.x la!AddIn_Start")
NAR(oWorkbook)
NAR(oWorkbookAddin)
NAR(oBooks)
NAR(oExcel)
End Sub
Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

TIA,

Atchoum

[Norton]

hi

For me i will call

oWorkBook.Close
oExcel.Quit
NAR(oExcel)

It works fine

Regards,
norton

"Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> wrote in message
news:Zf*********************@wagner.videotron.net. ..

Herfried,

Thanks. I can't seem to get it right thought. My program's objective is
simply to open a workboook and launch an add-in. I don,t want to close
anything once I am done, but I want obviously the program to release Excel. Here is excerpt of my code. What am I doing wrong?

Private sub Main()
Dim oExcel As Object
Dim oWorkbookAddin As Object
Dim oWorkbook As Object
Dim oBooks As Object
oExcel = CreateObject("Excel.Application")
oBooks = oExcel.Workbooks
oWorkbookAddin = oExcel.Workbooks
oWorkbook = oExcel.Workbooks
oWorkbookAddin = oBooks.Open(cAppPath &
"\Addin_Excel_Templates.xla", AddToMRU:=False)
oWorkbook = oBooks.Open(Template, Editable:=OpenForEdition)
oWorkbook.Application.Run("Addin_Excel_Templates.x la!AddIn_Start")
NAR(oWorkbook)
NAR(oWorkbookAddin)
NAR(oBooks)
NAR(oExcel)
End Sub
Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

TIA,

Atchoum

[Paul Clement]

On Fri, 13 Aug 2004 23:29:20 -0400, "Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> wrote:

¤ I have a DLL that opens an Excel workbook and add-in.
¤ If I close the app that calls the DLL before closing Excel, when I close
¤ Excel everything is fine.
¤ But if I close Excel while the app is still open, an instance of Excel
¤ remains in the background. Then even if I close the app, it remains there.
¤ What am I suppose to do, in my DLL, to completely release Excel. If I use
¤ oExcel.Quit() then I shut down Excel...

Try setting the UserControl property of the Application object to True.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Atchoum]

But then Excel will close which I don't want!

Atchoum

[Atchoum]

Paul,

Thanks but it does not seem to make a difference...

Atchoum

[Paul Clement]

On Mon, 16 Aug 2004 15:22:55 -0400, "Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> wrote:

¤ Paul,
¤
¤ Thanks but it does not seem to make a difference...
¤
¤ Atchoum
¤

OK, so if you set the Application UserControl property to True (Visible property must also be True)
and destroy the other objects (that would include the Excel object) it still has no affect?
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Michael D. Ober]

To close excel, you need to add the line

Excel.Application.Quit

Mike Ober.

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:g1********************************@4ax.com...

On Mon, 16 Aug 2004 15:22:55 -0400, "Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> wrote:
¤ Paul,
¤
¤ Thanks but it does not seem to make a difference...
¤
¤ Atchoum
¤

OK, so if you set the Application UserControl property to True (Visible property must also be True) and destroy the other objects (that would include the Excel object) it still has no affect?

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

[Codo]

Atchoum:

The way Interop in .NET works is by creating a proxy between the excel
object and your application. This proxy holds a reference to your COM
object, and the COM object will exist as long as there is a reference to it
(the proxy).

When a variable goes out of scope (a proxy), you cannot see it anymore,
but it still exists until it is Garbage Collected. Try

--> GC.Collect() 'This will make Excel close.

This is not the right thing to do, though. You should manually release
your com objects

Dim oCell As Excel.Cell
Dim oFont As Excel.Font = oCell.Font
oFont.Bold = True
Marshal.ReleaseComObject(oFont) 'This is what you want
Marshal.ReleaseComObject(oCell)

* If I do oCell.Font.Bold = True, a proxy will be created for font, but you
won't be able to release it. You should declare a variable for EVERY COM
OBJECT YOU WANT TO USE, even if you are accesing a subproperty of
something.

Hope this helps, and shows you the light.

[Atchoum]

Paul,
By application, you mean the Excel application?
I tried but it does not make a difference.

After many test, it boils down to this code: The s=Range.FindNext(s) is the
line that keeps the instance open.
If you comment it out, everything will close properly.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Open(Application.StartupPath &
"\test.xls")
For i = 1 To oWorkbook.Sheets.Count
Range = oWorkbook.Sheets(i).UsedRange
s = Range.Find("[[C:", lookin:=-4163) 'xlValues
If Not s Is Nothing Then
If Not s Is Nothing Then
sFirstAddress = s.Address
Do
s = Range.FindNext(s)
Loop While Not s Is Nothing And s.Address <>
sFirstAddress
End If
End If

Next i
oExcel.Visible = True
NAR(s)
NAR(oWorkbook)
oExcel = Nothing
NAR(oExcel)
GC.Collect()
End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

[Atchoum]

Thanks for the explanations.
The one line that makes it not release Excel is
s = Range.FindNext(s). Even if I use the GarbageCollector.
What am I doing wrong?

in:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Open(Application.StartupPath &
"\test.xls", Editable:=True)
For i = 1 To oWorkbook.Sheets.Count
Range = oWorkbook.Sheets(i).UsedRange
s = Range.Find("[[C:", lookin:=-4163) 'xlValues
If Not s Is Nothing Then
If Not s Is Nothing Then
sFirstAddress = s.Address
Do
s = Range.FindNext(s)
Loop While Not s Is Nothing And s.Address <>
sFirstAddress
End If
End If

Next i
oExcel.Visible = True
NAR(s)
NAR(oWorkbook)
oExcel = Nothing
NAR(oExcel)
GC.Collect()
End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
Finally
o = Nothing
End Try
End Sub

[Codo]

Atchoum wrote:

Thanks for the explanations.
The one line that makes it not release Excel is
s = Range.FindNext(s). Even if I use the GarbageCollector.
What am I doing wrong?

Here, you're losing a reference to the proxy of the first 's', and you won't
be able to release NAR(s)

Try

newS = Range.FindNext(s)
NAR(s)
s = newS

[Paul Clement]

On Tue, 17 Aug 2004 15:09:33 -0400, "Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> wrote:

¤ Paul,
¤ By application, you mean the Excel application?
¤ I tried but it does not make a difference.
¤
¤ After many test, it boils down to this code: The s=Range.FindNext(s) is the
¤ line that keeps the instance open.
¤ If you comment it out, everything will close properly.
¤
¤
¤ Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles Button1.Click
¤ oExcel = CreateObject("Excel.Application")
¤ oWorkbook = oExcel.Workbooks.Open(Application.StartupPath &
¤ "\test.xls")
¤ For i = 1 To oWorkbook.Sheets.Count
¤ Range = oWorkbook.Sheets(i).UsedRange
¤ s = Range.Find("[[C:", lookin:=-4163) 'xlValues
¤ If Not s Is Nothing Then
¤ If Not s Is Nothing Then
¤ sFirstAddress = s.Address
¤ Do
¤ s = Range.FindNext(s)
¤ Loop While Not s Is Nothing And s.Address <>
¤ sFirstAddress
¤ End If
¤ End If
¤
¤ Next i
¤ oExcel.Visible = True
¤ NAR(s)
¤ NAR(oWorkbook)
¤ oExcel = Nothing
¤ NAR(oExcel)
¤ GC.Collect()
¤ End Sub
¤
¤ Private Sub NAR(ByVal o As Object)
¤ Try
¤ System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
¤ Catch
¤ Finally
¤ o = Nothing
¤ End Try
¤ End Sub
¤

Make certain to destroy any objects you create. This would include the Range object (probably both
"Range" and "s" in your example). In addition, try not to create any implicit objects. In other
words, always set methods that return objects to variables so that they can be disposed of.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Atchoum]

Paul,

I tried to dispose of Range and s but to no avail even if I do it in the
loop itself so that they are disposed of before they are re-assigned... This
is driving me nuts.

Atchoum

[Atchoum]

Codo,

Sounded like a good idea but it does not work. SIGH.

Atchoum

[Codo]

Atchoum wrote:

Codo,

Sounded like a good idea but it does not work. SIGH.

Atchoum

I don't have VB at home (I'm on Linux/Java), but I'm going to test the code
at the office. Let's see what's going on...

[Atchoum]

Thanks. I am at a loss here...

Atchoum

[Codo]

Atchoum wrote:

Thanks. I am at a loss here...

Atchoum

Here we are... I've tested this code. Notice that whenever an object might
be returned, I keep it in a variable so I can dispose of it...

e.g. Workbooks.Sheets(1).Range(1, 1) involves three objects. You should
declare a variable for each...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
* * * * Dim oExcel As Excel.Application = New Excel.Application
* * * * Dim oWorkbooks As Excel.Workbooks = oExcel.Workbooks
* * * * Dim oWorkbook As Excel.Workbook =
oWorkbooks.Open(Application.StartupPath & "\test.xls", Editable:=True)
* * * * Dim oSheets As Excel.Sheets = oWorkbook.Sheets

* * * * Dim oRange As Excel.Range
* * * * Dim oSheet As Excel.Worksheet
* * * * Dim s As Excel.Range
* * * * Dim nextS As Excel.Range

* * * * Dim sFirstAddress As String

* * * * Dim i As Integer
* * * * For i = 1 To oSheets.Count
* * * * * * oSheet = CType(oSheets(i), Excel.Worksheet)
* * * * * * oRange = oSheet.UsedRange

* * * * * * s = oRange.Find("[[C:", lookin:=-4163) 'xlValues

* * * * * * If Not s Is Nothing Then
* * * * * * * * sFirstAddress = s.Address
* * * * * * * * Do
* * * * * * * * * * nextS = oRange.FindNext(s)
* * * * * * * * * * NAR(s)
* * * * * * * * * * s = nextS
* * * * * * * * Loop While Not s Is Nothing And s.Address <> sFirstAddress
* * * * * * End If

* * * * * * NAR(oRange)
* * * * * * NAR(oSheet)

* * * * Next i

* * * * oExcel.Visible = True
* * * * NAR(oSheets)
* * * * NAR(oWorkbook)
* * * * NAR(oWorkbooks)
* * * * NAR(oExcel)
* * End Sub

* * Private Sub NAR(ByVal o As Object)
* * * * Try
* * * * * * System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
* * * * Finally
* * * * * * o = Nothing
* * * * End Try
* * End Sub
Let me know if it worked!

[Atchoum]

Codo,

No, I am still having the same problem. Have you been able to test it
yourself? Did it work on your system?
On mine, it still is the s = oRange.Find("[[C:", lookin:=-4163) which is the
culprit...

Atchoum

[Paul Clement]

On Wed, 18 Aug 2004 20:02:22 -0400, "Atchoum" <goglus@DONT_SPAM_ME_videotron.ca> wrote:

¤ Paul,
¤
¤ I tried to dispose of Range and s but to no avail even if I do it in the
¤ loop itself so that they are disposed of before they are re-assigned... This
¤ is driving me nuts.
¤
¤ Atchoum
¤

Well if all else fails you can always use API function calls to kill the process:

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)