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!! = ))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stergiumCommented:
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
0
Michael FowlerSolutions ConsultantCommented:
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
0
developingprogrammerAuthor Commented:
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
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

developingprogrammerAuthor Commented:
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!! = ))
0
Michael FowlerSolutions ConsultantCommented:
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
0
Michael FowlerSolutions ConsultantCommented:
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.
0
developingprogrammerAuthor Commented:
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.
0
developingprogrammerAuthor Commented:
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? = )
0
developingprogrammerAuthor Commented:
hrmm seems like parallel references instead of sequential references. really weird Michael!!
0
developingprogrammerAuthor Commented:
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?
0
Michael FowlerSolutions ConsultantCommented:
Ok this is weird. My guess is that the references know about the parent and so keep the COM object alive but the parent is unaware of the child objects. This would make sense as the parent objects are created first and only a reference is passed to the child object at creation.

For example the range object knows it is a range in a particular worksheet but the worksheet is only aware of system objects that relate to it and not instances created that reference it.

Michael
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
superb answer Michael!! = ))

i think that makes sense too ha = )

thanks for all your help all the way Michael!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.