M
Marshall
Hi all,
I recently posted a query re an invoked instance of Excel
not closing correctly - although the code all ran fine
the first time, when run a second time, object errors
were returned. Looking at the Task Manager showed that
after closing the Excel app, an 'excel.exe' process was
still running.
The answer I got confirmed my suspicion - that it was an
Excel bug and there's no sure way of dealing with it.
However I've played with it a bit more since and I've
fixed the problem in my case and I suspect it's the same
problem in other cases (and thus the reason for this huge
post - hopefully it'll help someone else out!)
My brother put me onto some freeware tools at
www.sysinternals.com PSKILL.exe allows you to kill a
process by name or ID. Can run this from VBA via Shell.
Having done it, and re-run my code, I'm getting different
errors (again, only on the second run). Eg:
appxl.Range(Selection, Selection.End(xlToRight)).Select
....returns 'remote server machine name does not exist'.
Help identifies this as being caused by an object being
improperly created. I wondered what this was about given
that I've already declared and set 'appxl' as the app
object. It occurred to me that maybe the association
wasn't extending through to the 'selection' object, so I
experimented and I find that this change:
appxl.Range(appxl.Selection, appxl.Selection.End
(xlToRight)).Select
....results in the code running okay. And not only that,
it prevents the parasite excel process being invoked in
the first place. So it seems that the bug is not that the
excel app isn't closed properly, but rather that excel
allows you (on the first run) to inadvertently raise an
undeclared second(?) app object (by way of omitting the
parent object from the expression), rather than
validating the expression as it does on the second run.
So basically, in my case, making all the references to
excel objects explicit by prefixing them with the
declared app object (in my case 'appxl') has fixed the
problem altogether, and I no longer need to kill a
parasite process (despite pskill.exe being a very handy
tool!).
Hope this helps.
Cheers,
Marshall.
I recently posted a query re an invoked instance of Excel
not closing correctly - although the code all ran fine
the first time, when run a second time, object errors
were returned. Looking at the Task Manager showed that
after closing the Excel app, an 'excel.exe' process was
still running.
The answer I got confirmed my suspicion - that it was an
Excel bug and there's no sure way of dealing with it.
However I've played with it a bit more since and I've
fixed the problem in my case and I suspect it's the same
problem in other cases (and thus the reason for this huge
post - hopefully it'll help someone else out!)
My brother put me onto some freeware tools at
www.sysinternals.com PSKILL.exe allows you to kill a
process by name or ID. Can run this from VBA via Shell.
Having done it, and re-run my code, I'm getting different
errors (again, only on the second run). Eg:
appxl.Range(Selection, Selection.End(xlToRight)).Select
....returns 'remote server machine name does not exist'.
Help identifies this as being caused by an object being
improperly created. I wondered what this was about given
that I've already declared and set 'appxl' as the app
object. It occurred to me that maybe the association
wasn't extending through to the 'selection' object, so I
experimented and I find that this change:
appxl.Range(appxl.Selection, appxl.Selection.End
(xlToRight)).Select
....results in the code running okay. And not only that,
it prevents the parasite excel process being invoked in
the first place. So it seems that the bug is not that the
excel app isn't closed properly, but rather that excel
allows you (on the first run) to inadvertently raise an
undeclared second(?) app object (by way of omitting the
parent object from the expression), rather than
validating the expression as it does on the second run.
So basically, in my case, making all the references to
excel objects explicit by prefixing them with the
declared app object (in my case 'appxl') has fixed the
problem altogether, and I no longer need to kill a
parasite process (despite pskill.exe being a very handy
tool!).
Hope this helps.
Cheers,
Marshall.