Link to home
Create AccountLog in
Avatar of developingprogrammer
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 = )


    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)

Open in new window


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

Open in new window


this does not work
xlsApp.DisplayAlerts = False
        xlsApp.Quit
    xlsApp.DisplayAlerts = True
    Set xlsApp = Nothing

Open in new window


why must i
Set rngLimitsTestCell = Nothing
and
Set xlsWst = Nothing
first?

what i think is that in the hierarchy tree, xlsApp-->xlsWbk-->xlsWst-->rngLimitsTestCell
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!! = ))
Avatar of stergium
stergium
Flag of Greece image

Hello.
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
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
Avatar of developingprogrammer
developingprogrammer

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
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!! = ))
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
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.
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.
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? = )
hrmm seems like parallel references instead of sequential references. really weird Michael!!
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
Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
superb answer Michael!! = ))

i think that makes sense too ha = )

thanks for all your help all the way Michael!! = ))