developingprogrammer
asked on
closing Excel.exe
hey guys,
i had a problem with closing excel.exe and i found the solution myself through testing, but i don't understand why it's happening.
here's what i did - perhaps yall could explain to me the why = )
now i want to close the Excel.exe process that was created
so here is what works and what doesn't
this works
this does not work
why must i
Set rngLimitsTestCell = Nothing
and
Set xlsWst = Nothing
first?
what i think is that in the hierarchy tree, xlsApp-->xlsWbk-->xlsWst-- >rngLimits TestCell
so if i
xlsApp.DisplayAlerts = False
xlsApp.Quit
xlsApp.DisplayAlerts = True
Set xlsApp = Nothing
that closes the Excel application without saving and then sets xlsApp to nothing. this should cascade down and thus the Excel.exe should also close right?
note i don't need to Set xlsWbk = Nothing.
this leads me to think that i need to set all grandchildren objects to nothing but for children objects the nothing state is automatically cascaded down.
perhaps guys yall can correct my way of thinking? thanks guys!! = ))
i had a problem with closing excel.exe and i found the solution myself through testing, but i don't understand why it's happening.
here's what i did - perhaps yall could explain to me the why = )
Set xlsApp = New Excel.Application
With xlsApp
.Visible = False
Set xlsWbk = .Workbooks.Open(strPathOfSourceFile, , True)
End With
Set xlsWst = xlsWbk.Worksheets(strWorksheetName)
Set rngLimitsTestCell = xlsWst.Cells(1, 1)
now i want to close the Excel.exe process that was created
so here is what works and what doesn't
this works
Set rngLimitsTestCell = Nothing
Set xlsWst = Nothing
xlsApp.DisplayAlerts = False
xlsApp.Quit
xlsApp.DisplayAlerts = True
Set xlsApp = Nothing
this does not work
xlsApp.DisplayAlerts = False
xlsApp.Quit
xlsApp.DisplayAlerts = True
Set xlsApp = Nothing
why must i
Set rngLimitsTestCell = Nothing
and
Set xlsWst = Nothing
first?
what i think is that in the hierarchy tree, xlsApp-->xlsWbk-->xlsWst--
so if i
xlsApp.DisplayAlerts = False
xlsApp.Quit
xlsApp.DisplayAlerts = True
Set xlsApp = Nothing
that closes the Excel application without saving and then sets xlsApp to nothing. this should cascade down and thus the Excel.exe should also close right?
note i don't need to Set xlsWbk = Nothing.
this leads me to think that i need to set all grandchildren objects to nothing but for children objects the nothing state is automatically cascaded down.
perhaps guys yall can correct my way of thinking? thanks guys!! = ))
It is a commonly known issue that Unless your code explicitly declares and clears automation-based object variables used as the object of a with-block (With...End With syntax), those objects can get stuck in memory as "orhpaned" com objects. This will then prevent the release of their automation server classes (since a consitiuent object still has a non-zero COM reference count). This will present as the symptom you are observing: Excel won't close despite the call to .Quit.
http://social.msdn.microsoft.com/Forums/office/en-US/908ba72a-3293-4eb9-b80e-fd2e6e78e185/vba-close-excel-problem
Michael
http://social.msdn.microsoft.com/Forums/office/en-US/908ba72a-3293-4eb9-b80e-fd2e6e78e185/vba-close-excel-problem
Michael
ASKER
hi stergium, thanks for you help!! = )
i've tested this code many many times (2 hrs haha) and the Set rngLimitsTestCell = Nothing is definitely required.
the thing i'm not sure about is - why must it be closed first before the application?
why must the grandchildren be closed before the application? that is the thing that is really perplexing me.
Here are 2 very article on this - but they do not expound on the sequence of closing the objects.
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/
http://support.microsoft.com/default.aspx?scid=kb;en-us;319832
i've tested this code many many times (2 hrs haha) and the Set rngLimitsTestCell = Nothing is definitely required.
the thing i'm not sure about is - why must it be closed first before the application?
why must the grandchildren be closed before the application? that is the thing that is really perplexing me.
Here are 2 very article on this - but they do not expound on the sequence of closing the objects.
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/
http://support.microsoft.com/default.aspx?scid=kb;en-us;319832
ASKER
Hi Michael74!
thanks for looking at this!
hrmm correct me if i'm wrong, but it doesn't always cause an orphaned object (as per the 2nd link i pasted above). it uses a globalmultiuse activex class and thus if the code is run a second time it references the old object. there are different issues that could be caused based on the code.
anyway apologies i think i didn't ask my question is a clear way - what i wanted to ask was:
Question
1) Why do i have to set to nothing in sequence the grand grand child first (range object) then the grand child (worksheet) then the parent (excel application?)
edit: guys i just tested and i found out that i just need to set to nothing the range and worksheet variable in any sequence BEFORE setting to nothing the application variable why can't i just set to nothing the parent only? the nothing should be cascaded down right?
edit AGAIN: guys i realised that i just need to set to nothing the range, the worksheet and the application variable in ANY SEQUENCE
=======don't need to answer question 1 anymore, i answered myself haha ============
2) why do i not have to set to nothing the workbook (the child object)? i do understand that using the xlsApp.Quit method closes all workbooks.
this "sets them to nothing" and thus the workbook variable is "set to nothing" is that right?
so the METHOD "cascades" or rather specifically causes the workbook to be set to nothing as it is closed whereas the range and worksheet variable is not directly influenced by any method which directly causes it to become nothing. is that the explanation?
thanks guys!! = ))
thanks for looking at this!
hrmm correct me if i'm wrong, but it doesn't always cause an orphaned object (as per the 2nd link i pasted above). it uses a globalmultiuse activex class and thus if the code is run a second time it references the old object. there are different issues that could be caused based on the code.
anyway apologies i think i didn't ask my question is a clear way - what i wanted to ask was:
Question
1) Why do i have to set to nothing in sequence the grand grand child first (range object) then the grand child (worksheet) then the parent (excel application?)
edit: guys i just tested and i found out that i just need to set to nothing the range and worksheet variable in any sequence BEFORE setting to nothing the application variable why can't i just set to nothing the parent only? the nothing should be cascaded down right?
edit AGAIN: guys i realised that i just need to set to nothing the range, the worksheet and the application variable in ANY SEQUENCE
=======don't need to answer question 1 anymore, i answered myself haha ============
2) why do i not have to set to nothing the workbook (the child object)? i do understand that using the xlsApp.Quit method closes all workbooks.
this "sets them to nothing" and thus the workbook variable is "set to nothing" is that right?
so the METHOD "cascades" or rather specifically causes the workbook to be set to nothing as it is closed whereas the range and worksheet variable is not directly influenced by any method which directly causes it to become nothing. is that the explanation?
thanks guys!! = ))
Have you tried removing the With...End With block. As the parent object is used in this block it could be the cause of your problem.
Michael
Michael
The references in the objects are probably causing the COM to leave the objects in memory due to the issue I noted above. try removing with..end with blocks that reference these objects.
ASKER
hi Michael!
i did some testing and i confirmed that it's not because of the with block.
with or "without" haha, i still need to explicitly set all both my range, worksheet variable to nothing before i the excel.application instance will disappear.
i did some testing and i confirmed that it's not because of the with block.
with or "without" haha, i still need to explicitly set all both my range, worksheet variable to nothing before i the excel.application instance will disappear.
ASKER
hrmm Michael, it makes me think that the worksheet and range variable has TWO references -
xlsWsk's References
1) referencing xlsWbk
2) referencing xlsApp
rngLimitsTestCell
1) referencing xlsWsk
1) referencing xlsApp
i think it may sound absurd but that's the only theory i can come up to explain this phenomenon. what do you think Michael? = )
xlsWsk's References
1) referencing xlsWbk
2) referencing xlsApp
rngLimitsTestCell
1) referencing xlsWsk
1) referencing xlsApp
i think it may sound absurd but that's the only theory i can come up to explain this phenomenon. what do you think Michael? = )
ASKER
hrmm seems like parallel references instead of sequential references. really weird Michael!!
ASKER
hrmm guys, any idea why we can't just set the xlsApp to nothing after quitting the excel application? why does Excel.exe in the processes window in task manager still hang around until xlsWsk and rngLimitsTestCell are also set to nothing?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
superb answer Michael!! = ))
i think that makes sense too ha = )
thanks for all your help all the way Michael!! = ))
i think that makes sense too ha = )
thanks for all your help all the way Michael!! = ))
As far as i read the code xlsWst is the worksheet which in turn must close first before the application.
about this... Set rngLimitsTestCell = Nothing i believe that can be ommited.
Hope that helps