Printable View
I have the following (snippet) to Create and inastance of Excel from within a BackgroundWorker.
After I do the Excel thing, I do:Code:Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
Except Excel is still pressent in Task Manager until I close the App????Code:If IsNothing(oExcel) = False Then
oExcel.Quit()
oExcel = Nothing
End If
(The above code executes)
Confussed!
First up, this:should be more correctly written:vb.net Code:
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.
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.
Code: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
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.
Code: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.
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.
Code: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
Hey RD :wave:
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.
Cool, the only thing that was different is that I didnt setup a bw thread. So I hope it works for your situation. :)
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! ;)
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? :D
I'll take that advice onboard and impliment first thing :thumb:
I'm lucky, I start at 7am and finish at 3:30pm (and 1pm on Fridays) - 38 hour week - happy, happy.
Hiii all..
I faced some problem in killing the excel instance.
Let me explain what problem i faced.
case 1:
This Excel.Kill() will kills all the instance in the task manager.so i dropped this case.Code:Dim oExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
'Code .....
Excel.Kill()
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.
I hope this will be a somewat useful hint...Code: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 don't think that is the best way to achieve that aim...
can you please explain why?
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. ;)
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.
Code: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
Well,
Still wrong, I have narowed it down to here maintaining the instance of Excel.
Still tweeking.....Code: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
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.Quote:
I unsuccsefully tested the updated code block in my App and Excel persisted until App closure.
Runtime Callable Rapper :D
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.
Code: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
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. :thumb:
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 :D
Hey, you have nothing to be sorry for :afrog:
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.
Well we had a bit of communications mixup so that part I can be sorry for :D
Anyways glad its all resolved now :) and Thanks again too. ;)
For what it's worth, here is the winning combination, once the COM object is released, calling the GC cleans it up:
Thanks Guys.Code: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
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)
Code: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
Hmmm, Still persists (note also the release of the oWS in the For/Next too):
Code: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
Even tried the .FinalRelease.
I guess there is some merit in the reference link JM posted: here.
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.
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
I'm sure I tried that after I posted the above snippet.Quote:
Originally Posted by RobDog888
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.
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.
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. ;)
G'Day Rob,Quote:
Originally Posted by RobDog888
I had hard coded that count earlier to confim suspisions.
I have it down to one spot now - will try suggestions etc and advise :)
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 :)Quote:
Originally Posted by RobDog888
I have tried to unsucsefully make a variables to hold the WorkSheet Count.
This is where its at at the moment:
Code: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