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

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>

 

[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.xla!AddIn_Start")
NAR(oWorkbook)
NAR(oWorkbookAddin)
NAR(oBooks)
NAR(oExcel)
End Sub
Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(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

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
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]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Michael D. Ober]

To close excel, you need to add the line

Excel.Application.Quit

Mike Ober.

 

[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.ReleaseComObject(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.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub

 

[Paul Clement]

¤ 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.ReleaseComObject(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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Codo]

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

 

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

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

 

[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

 

[Codo]

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.ReleaseComObject(o)
        Finally
            o = Nothing
        End Try
    End Sub


Let me know if it worked!