Re: Background Web Queries flaws!
From: Robin Hammond (rjNOrhSPAM_at_PLEASEnetvigator.com)
Date: 06/16/04
- Next message: paul: "Certified Macro's?"
- Previous message: Poh: "Return to MS Excel"
- In reply to: Robin Hammond: "Background Web Queries flaws!"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Jun 2004 09:11:50 +0800
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" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in message
news:c9h4tt$1p52@imsp212.netvigator.com...
> 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
>
>
- Next message: paul: "Certified Macro's?"
- Previous message: Poh: "Return to MS Excel"
- In reply to: Robin Hammond: "Background Web Queries flaws!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
