AnsweredAssumed Answered

vrf Excel ActiveX question

Question asked by VRFuser on Jan 13, 2005
Well, it seems I stuck my foot in my mouth yet again folks. Apparently the
caffeine never kicked in yesterday - or today for that matter.

The position of DisplayAlerts = False does not make any difference, as I
thought I remembered. The link update query, for some reason, is not
considered an "alert".

The first offered solution (turn link updating off) doesn't work. Why this
is the case only MS knows.

The second offered solution (use SendKeys) doesn't work either because of a
very basic flaw - IDispatch calls will block until complete. An application
modal dialog that pops up in the course of an IDispatch call will block the
calling thread until the modal dialog is dismissed. The net effect is that
until the workbook is open VEE will not execute SendKeys.

Parallel VEE threads won't work either because all VEE threads are run on
one Win32 thread - which is blocked waiting for an IDispatch call to
complete. So that's out.

What does work is to set the Excel.Application property AskToUpdateLinks to
False, in which case the query is skipped and links are updated
automatically.

If you don't want to update links then there is only one avenue left (I
guess, though my brain is pretty clogged right now): start a Win32 thread
that watches for the query dialog and post an "n" character to it. Needless
to say, VEE won't be able to do it on it's own (because it's waiting for the
answer).

In a strange twist of fate, I retried all this again before I posted this
and low and behold, the update query is now permanently turned off. I have
no idea how this happened because I explicitly tried to save this property
along with the workbook and it didn't work. Deleting both workbooks and
recreating them did not turn this query back on. Exiting Excel did not turn
them back on. Rebooting the machine did not turn them back on.

So there you have it. "It's always something. Either your brain is clogged
or Excel doesn't work right." I can only guess that the checkbox isn't tied
to the Application property, and the Application property is the one saved.
I guess I can check for this right now...

Yes friends, that is indeed the case. After executing xl.AskToUpdateLinks =
True the update query is now back on. So now I have to find out why. Oh
god...

I don't know where it is. It's probably persisted in the registry but
there's no setting that is of an obvious name. I quit. Oh god... almost.
-SHAWN-



---
You are currently subscribed to vrf as: rsb@soco.agilent.com
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list,  email "vrf@agilent.com". 
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".

Outcomes