Background Web Queries flaws!
 

I escalated this one to MS to see if they could find a solution, so to put
it to rest...

After going around in circles for a while I have the official answer.
According to MS:

1. There is no way to trap errors on a background query.
2. There is no way to control the timeout setting.

The basic problem is that IE spits an error message before returning control
to Excel.

Simple solution for the next version of Office if there is still time - make
sure that the querytable afterrefresh event fires before the error message
from IE.

If anyone really wants to get around this, the solution I found after a lot
of time involves creating a couple of activex wrappers in VB, the first with
a timer in it for use in Excel, the second with a group to calls to the
Asyncdownload method. From there, I create one timer and multiple Async
controls on a form, use the timer to check the state of each Async download
control, and if it's completed, push the next file to that particular
control. It's a long winded way around something that should be trivial.

See my next post for details on the timer if you are interested.

Robin Hammond
www.enhanceddatasystems.com


"Robin Hammond" wrote in message
...
General rant follows - any suggestions for angles to try to get around
this
would be gratefully received. Running XL2002 (XP), IE6.

Problems with background web queries
1. They are not really background at all. There is seemingly no control of
error message display, so a server error on a legitimate query results in
an
error message which halts execution. Why expose the web query interface in
VBA if you subsequently cannot control execution of the query from VBA.
Surely there should have been a DisplayErrorMessages property, or the
query
could return a value into the destination that says query error.

2. There doesn't appear to be any control of the timeout value on a query.
If running lots of background queries (for a very significant speed boost
over non background types) it is more than possible that it is going to
take
longer than what appears to be a default time out of 5 minutes. Or, if
running some complex data manipulation on a database server with a massive
data return, it is feasible that a return could take longer. Where is the
timeout property? In KB article 181050 there is a mention of a registry
setting for IE4 that could alter this, but no such thing that I can find
for
IE6. However, this KB article suggests a 60 minute time out for IE6. It
doesn't appear that way on my system, and changing the registry entry as
suggested in the article makes no difference. It looks to me like somebody
hard coded a 5 minute limit into the web query system. Hard to believe.

3. Complex solutions that might have worked, don't. Creating a class
module
to handle an afterrefresh event for each and every query table that has
yet
to background refresh doesn't work either. The afterrefresh event only
fires
after the error message in point 1. What use is that as an error handler?
None whatsoever.

4. I have just had a look at XL2003 to see if there is any improvement in
the model, and cannot see any changes. A trawl of the archives shows that
I
am not the first person to run into these flaws.

5. It now looks like I am going back to resurrecting a winsock class
wrapper
to get a decent asynchronous web query working. So much for improved
connectivity.

Robin Hammond
www.enhanceddatasystems.com