[Resolved] BackgroundWorker - Destroy Excel Instance

[Bruce Fox]

I have the following (snippet) to Create and inastance of Excel from within a BackgroundWorker.

            Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)

After I do the Excel thing, I do:

            If IsNothing(oExcel) = False Then
                oExcel.Quit()
                oExcel = Nothing
            End If

Except Excel is still pressent in Task Manager until I close the App????
(The above code executes)

Confussed!

[jmcilhinney]

First up, this:

If IsNothing(oExcel) = False Then

should be more correctly written:

If oExcel IsNot Nothing Then

As to your question, if you call GC.Collect after setting the Excel Application reference to Nothing the process will be destroyed. I'm not sure whether there's a tidier way than that but it should work. You might want to read this, but RobDog might have an preferable alternative.

[Bruce Fox]

Thanks for the pointers JM .

I will give the clean up a shot when I get back to work tomomorow.

For what it's worth, Excel (a different instance) gets destroyed from my other code block not in the BW (each instance is independant of each other). So in short, it seems the one created in the BW cant be removed; well at least till I try your suggestion.

I will post results.

Cheers.

[Bruce Fox]

Ok, still no joy at the moment. The instance of Excel remains until the App is closed.
I added some of the referenced CG functions (from the above link); I did however omited the Marshaling functions cause I'm not too sure how to impliment them.

    Private Sub BWLoadWorksheets_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BWLoadWorksheets.DoWork
        Dim worker As System.ComponentModel.BackgroundWorker = DirectCast(sender, System.ComponentModel.BackgroundWorker)

        Try
            Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            oExcel.Visible = False
            Dim oWB As Excel.Workbook = oExcel.Workbooks.Open(CType(e.Argument, String))
            Dim WSNames(oWB.Worksheets.Count - 1) As String

            For intIdx As Integer = 0 To oWB.Worksheets.Count - 1
                Dim oWS As Excel.Worksheet = DirectCast(oWB.Worksheets(intIdx + 1), Excel.Worksheet)
                ' Load up the Array
                WSNames(intIdx) = oWS.Name
                ' Calculate the % complete and pass out to update the Progressbar
                worker.ReportProgress((intIdx + 1) * CInt(100 / (oWB.Worksheets.Count)))
            Next

            If oExcel IsNot Nothing Then
                oWB.Close(SaveChanges:=False)
                oExcel.Quit()
                oWB = Nothing
                oExcel = Nothing
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End If

            ' Pass out the Array of Worksheet Names to the completed BackgroundWorker 
            e.Result = WSNames

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try

    End Sub

[RobDog888]

Sup Bruce!

It not a recommended way to "clean" an Excel instance by calling the GC. Having the instance of Excel remain running after the app closes is usually from implied instanciation of Excel objects or not properly closing and destroying your Excel objects.

It looks like you are closing/destroying Excel ok but try creating an object var for each level of access you drill down the EOM.

Dim oWB As Excel.Workbook = oExcel.Workbooks.Open(CType(e.Argument, String))

'Could be re-written as ...
Dim oWBs As Excel.Workbooks = oExcel.Workbooks
Dim oWB As Excel.Workbook = oWBs.Open(CType(e.Argument, String))

Then close/destroy as you are logically are doing but loose the GC.

[RobDog888]

Ok found the issue. I had missed it. In addition to changing the declaration of the vars, there was a scope issue with the Excel object varin your loop. Since its not destroyed in the loop it will also cause the retension of the instance of Excel.

I wrote a variation of your code for testing and Excel now terminates properly without the GC calls.

Option Explicit On
Option Strict On

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            oExcel.Visible = False
            Dim oWBs As Excel.Workbooks = oExcel.Workbooks
            Dim oWB As Excel.Workbook = oWBs.Open(CType("C:\Book1.xls", String))
            Dim WSNames(oWB.Worksheets.Count - 1) As String

            For intIdx As Integer = 0 To oWB.Worksheets.Count - 1
                Dim oWS As Excel.Worksheet = DirectCast(oWB.Worksheets.Item(intIdx + 1), Excel.Worksheet)
                ' Load up the Array
                WSNames(intIdx) = oWS.Name
                ' Calculate the % complete and pass out to update the Progressbar
                'worker.ReportProgress((intIdx + 1) * CInt(100 / (oWBs.Count)))
                MessageBox.Show(WSNames(intIdx).ToString)
                oWS = Nothing
            Next

            If oExcel IsNot Nothing Then
                oWBs = Nothing
                oWB.Close(SaveChanges:=False)
                oWB = Nothing
                oExcel.Quit()
                oExcel = Nothing
            End If
            ' Pass out the Array of Worksheet Names to the completed BackgroundWorker 
            'e.Result = WSNames

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

End Class

[Bruce Fox]

Hey RD

Thanks for the reply. I will give that a shot tomorrow my time.

Makes sense - now . I had stepped thru my release block and all seemed fine.

Thanks again to you both, I will post results.

Cheers.

[RobDog888]

Cool, the only thing that was different is that I didnt setup a bw thread. So I hope it works for your situation.

[Bruce Fox]

Not a problem, I see exactly what you did - I feel such a goose.
I got the Application, the Workbook but missed the Worksheet.... doh!

[RobDog888]

Well i also added one for the WorkBooks collection too - oWBs. I find that sometimes setting vars for each level works better then drilling down multi-levels at once like ...

Dim oSht As Excel.WorkSheet = DirectCast(oApp.Workbooks.(1).Sheets(1), Excel.Worksheet)

When doing Late Binding and Reflection you are required to perform acccess one level at a time with each object var. Perhaps that is where the underlying logic/design is derrived from but early binding tries to make a shortcut of it and fails indeterminantly.

Othe then that, its those darn pesky implied instanciations 99% of the time which is why calling the GC or ReleaseCOMObject is not needed and can actually cause complications/problems.

Say, its still business hours over there. Heading out early I see?

[Bruce Fox]

I'll take that advice onboard and impliment first thing

I'm lucky, I start at 7am and finish at 3:30pm (and 1pm on Fridays) - 38 hour week - happy, happy.

[vijy]

Hiii all..

I faced some problem in killing the excel instance.

Let me explain what problem i faced.
case 1:

Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)

  'Code .....

Excel.Kill()

This Excel.Kill() will kills all the instance in the task manager.so i dropped this case.

case 2:
Then i tried to get the process id of the particular excel application and tried to kill a single application.Here i got confused of which process id i want to kill if user opened morethan five excel.
i dropped this case too..

Case 3:

i tried this one.. working cool..
for the current application instance.MainWindowTitle.ToString() will return empty.

Dim  myProcesses as System.Diagnostics.Process[]
                myProcesses = System.Diagnostics.Process.GetProcessesByName("Excel")
                ForEach instance as System.Diagnostics.Process  in myProcesses)
                
                    Dim id as string = instance.MainWindowTitle.ToString()                    
                    if id.Trim() = string.Empty then
                    
                        instance.CloseMainWindow()
                        instance.Kill()
                        break
                    End if
                Next

I hope this will be a somewat useful hint...

[Bruce Fox]

I don't think that is the best way to achieve that aim...

[vijy]

can you please explain why?

[RobDog888]

Its not a good method because if you write clean Excel code there will never ever be a need to do any workaround like that. Its similar to using End in a program. Why place a bandaid on a cut when if you can avoid getting cut in the first place would be the better option.

[Bruce Fox]

Wierd.

I unsuccsefully tested the updated code block in my App and Excel persisted until App closure.

Stepped tru etc and looks fine except Excel remains.

Next, opend a new project, added a COM ref to Excell 11, and dropped in the code block (below) and Excel remains until the App is closed - Bugger!

You had success Rob - whats different????? (I just omitted the 'worker')
It's no doubt a scope issue, I just can't see it.

Option Explicit On
Option Strict On

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try
            Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            oExcel.Visible = False
            Dim oWBs As Excel.Workbooks = oExcel.Workbooks
            Dim oWB As Excel.Workbook = oWBs.Open(CType("C:\Documents and Settings\bruce.fox\Desktop\Copy of FLC ITT MODULES.xls", String))
            Dim WSNames(oWB.Worksheets.Count - 1) As String

            For intIdx As Integer = 0 To oWB.Worksheets.Count - 1
                Dim oWS As Excel.Worksheet = DirectCast(oWB.Worksheets.Item(intIdx + 1), Excel.Worksheet)
                ' Load up the Array
                WSNames(intIdx) = oWS.Name
                MessageBox.Show(WSNames(intIdx).ToString)
                oWS = Nothing
            Next

            If oExcel IsNot Nothing Then
                oWBs = Nothing
                oWB.Close(SaveChanges:=False)
                oWB = Nothing
                oExcel.Quit()
                oExcel = Nothing
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub


End Class

[Bruce Fox]

Well,

Still wrong, I have narowed it down to here maintaining the instance of Excel.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)

        If oExcel IsNot Nothing Then
            oExcel.Quit()
            oExcel = Nothing
        End If

    End Sub

Still tweeking.....

[RobDog888]

I unsuccsefully tested the updated code block in my App and Excel persisted until App closure.

Ah, my bad. I thought it was the usual persistancy after the app is closed. Yea for termination during the app's runtime and you cant wait for the GC to be called upon apps termination then you need to use the ReleaseCOMObject because of the creation of a runtime COM wrapper when the COM object is created from managed code. This wrapper is what is holding the reference to the COM objects and keeping Excel persisting until the apps termination.

Runtime Callable Rapper
http://msdn.microsoft.com/en-us/library/8bwh56xe.aspx

So ReleaseCOMObject would be best for your situation as it properly calls the GC.Collect immediatly which is safer then manually calling it from code.

Run this code and watch the TaskManager between button clicks of 1 and 2. You will see it created with button1 and destroyed and removed from the taskmangaer from button2.

Imports Microsoft.Office.Interop

Public Class Form1

    Private oExcel As Excel.Application

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        oExcel = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        oExcel.Visible = False
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If oExcel IsNot Nothing Then
            oExcel.Quit()
            Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
        End If
    End Sub

End Class

[Bruce Fox]

Ah, thank you kindly Sir.

I thought I was losing the plot!

To be honest, I would be happy for the instance of Excel to remain til the App is closed, I just was just concerned that it remained when I thought it shouldn't. That said, I guess it is feasible to use the ReleaseComObect to neaten things up.

Runtime Callable Wrapper (RCW), thought the topic was Return to Castle Wolfenstien – was I mistaken. Anyhoo, it all makes sense now.

I will give it a go tomorrow - thanks again.

[RobDog888]

Yea, sorry about that. There are the two types of Excel/Office persisting.

1. After termination of the program the office app still remains running
2. During program execution the ofice app doesnt terminate instantly but only after the app has terminated.

Having the ReleaseCOMObject do your clean up is best for during runtime termination while just proper coding of the office app is all that should be done for terminating after a program has ended.

Perhaps I will write a new FAQ item on this as it seems to be a popular topic

[Bruce Fox]

Hey, you have nothing to be sorry for

It became obvious that it was a .Net'ism, and you nailed it with the RC Rapper (Yo Mr T).

I was lovin life with the BackgroundWorker doin its thang and the UI tickin over. Looks like it still can thanks to you - Happy days.

In any case, I'm sure someone else will experience this issue so it's no doubt timely.

[RobDog888]

Well we had a bit of communications mixup so that part I can be sorry for
Anyways glad its all resolved now and Thanks again too.

[Bruce Fox]

For what it's worth, here is the winning combination, once the COM object is released, calling the GC cleans it up:

            If oExcel IsNot Nothing Then
                oWBs = Nothing
                oWB.Close(SaveChanges:=False)
                oWB = Nothing
                oExcel.Quit()
                Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
                GC.Collect()
                oExcel = Nothing
            End If

Thanks Guys.

[RobDog888]

Thats weird because the ReleaseCOMObject internally calls the GC already. I see you arent calling the ReleaseCOMObject on the other objects. Perhaps that is why you need the secondary GC call.

Remember that the RCW keeps an internal counter and increments it by one for each reference to the COM object. So when you create other objects you are incrementing the counter and then ReleaseCOMObject is only called once so it leaves some references still in the RCW.

Try like this... (just wrote in the reply box)

If oExcel IsNot Nothing Then
    oWB.Close(SaveChanges:=False)
    Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
    oWB = Nothing
    Runtime.InteropServices.Marshal.ReleaseComObject(oWBs)
    oWBs = Nothing
    oExcel.Quit()
    Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
    oExcel = Nothing
End If

[Bruce Fox]

Hmmm, Still persists (note also the release of the oWS in the For/Next too):

            For intIdx As Integer = 0 To oWB.Worksheets.Count - 1
                Dim oWS As Excel.Worksheet = DirectCast(oWB.Worksheets.Item(intIdx + 1), Excel.Worksheet)
                ' Load up the Array
                WSNames(intIdx) = oWS.Name.ToString
                ' Calculate the % complete and pass out to update the Progressbar
                worker.ReportProgress((intIdx + 1) * CInt(100 / (oWB.Worksheets.Count)))
                Runtime.InteropServices.Marshal.ReleaseComObject(oWS)
                oWS = Nothing
            Next

            If oExcel IsNot Nothing Then
                Runtime.InteropServices.Marshal.ReleaseComObject(oWBs)
                oWBs = Nothing
                oWB.Close(SaveChanges:=False)
                Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
                oWB = Nothing
                oExcel.Quit()
                Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
                oExcel = Nothing
            End If

[Bruce Fox]

Even tried the .FinalRelease.

I guess there is some merit in the reference link JM posted: here.

[Bruce Fox]

I suspect it may be my Range object holding a connection.

EDIT: Reworked code to have ALL objects released in the release block to no avail.
Seems a reference is being kept behind the sceenes.

[RobDog888]

Hmm, it worked fine for me but where is the Range object? Im not seeing it.

Oh and the oWB should be before the oWBs. Its parent - child in creation and child - parent in destruction

[Bruce Fox]

Oh and the oWB should be before the oWBs. Its parent - child in creation and child - parent in destruction

I'm sure I tried that after I posted the above snippet.

I am at home and I will try to isolate down to minimal code. As for the range object - I must be dreaming.... I use that in a different unrelated Sub - sorry.

[Bruce Fox]

I commented all bar the initial oExcel instanciation, and progressively rolled back the commets. All worked (oWBs & oWB) up to re-gaining: Dim WSNames(oWB.Worksheets.Count - 1) As String

I guess the RCW may somehow count this.

[RobDog888]

Try setting a var to the count and using that in the loop. Shouldnt it be an additional var oWBShts.Count perhaps?

I know you have enough to continue on but guess for understanding of where the implicit reference is coming from.

[Bruce Fox]

Try setting a var to the count and using that in the loop

G'Day Rob,

I had hard coded that count earlier to confim suspisions.
I have it down to one spot now - will try suggestions etc and advise

[RobDog888]

Just noticed that there is another count reference in the ReportProgress line.

[Bruce Fox]

Just noticed that there is another count reference in the ReportProgress line.

Yep. I have had that commented for awhile now. I figured once I work past the first I can get to that one

[Bruce Fox]

I have tried to unsucsefully make a variables to hold the WorkSheet Count.

This is where its at at the moment:

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            oExcel.Visible = False
            Dim oWBs As Excel.Workbooks = oExcel.Workbooks
            Dim oWB As Excel.Workbook = oWBs.Open(CType("C:\Documents and Settings\Bruce\Desktop\FLC ITT MODULES.xls", String))

            MsgBox(oWB.Worksheets.Count) '<<<<<< This appears to holds the extra count in RCW, commenting this out alows Excel to be imediatly released

            If oExcel IsNot Nothing Then
                oWB.Close(SaveChanges:=False)
                Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
                oWB = Nothing
                Runtime.InteropServices.Marshal.ReleaseComObject(oWBs)
                oWBs = Nothing
                oExcel.Quit()
                Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
                oExcel = Nothing
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub
End Class