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!! = ))
Microsoft Excel

Avatar of undefined
Last Comment
developingprogrammer

8/22/2022 - Mon
stergium

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
Michael Fowler

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
ASKER
developingprogrammer

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
developingprogrammer

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!! = ))
Michael Fowler

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 Fowler

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
developingprogrammer

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.
ASKER
developingprogrammer

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? = )
ASKER
developingprogrammer

hrmm seems like parallel references instead of sequential references. really weird Michael!!
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
developingprogrammer

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
Michael Fowler

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
developingprogrammer

superb answer Michael!! = ))

i think that makes sense too ha = )

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