Help with a hanging web query (any timeout suggestions)?

[Guest]

Hi,
I'm not a programmer by trade but I'm trying to have a go with a financial
model that feeds a list of stock symbols through a web query procedure to
retrieve an assortment of trading data. It works most of the time but
occaisionally stalls/crashes when it tries to refresh a query table and gets
no response from the host website (finance.yahoo.com but I've tried others
and run into the same problem).

It would seem this issue could be overcome by a timeout feature but that the
refresh method on Excel's querytable object does not include that as a
property. I've been trying unsuccessfully to come up with a workaround and
so far have tried:

(1) Refreshing the query with the background property set to FALSE and using
either OnTime or a Windows-based timer to cancel it after a set period. With
VBA stalled on the .refresh line, the timer doesn't seem to be able to break
in.

I can get the timer to work if I have the query refresh in the background
but instead run into the following issues.

(2) Refreshing the query with the background property set to TRUE results in
the rest of the code executing before the query retrieves the data and the
model ends up with blank rows.

(3) Refreshing the query with the background property set to TRUE and then
pausing VBA for 15 seconds leads to the code resuming but the query table
doesn't populate fully (only the first cell).

(4) Refreshing the query with the background property set to TRUE followed
by code to check whether the query has completed before proceeding doesn't
seem to work either. I've tried several variations on something like:

Do Until ActiveSheet.QueryTables(1).Refreshing = False
DoEvents
Loop

and this works if I step through the code but if I run the code in real time
the Do Loop appears to run ad infinitum without recognizing that the query
has stopped refreshing. Is there anything obvious that should be included
with the code here to get VBA to recognize when the query is done refreshing?

I'd greatly appreciate any suggestions on these or any other approaches to
some form of workaround on this issue.

Thanks in advance,

Glen

 

[DM Unseen]

Glen,

DoEvents does not always work, it depends on your operating system.
Also it does not alwasy work inside Excel(Excel might actually no be
multitasking when doing a backgroun refresh).

try Application.Wait instead

DM Unseen

 

[Guest]

Thanks for the suggestion, really appreciate the help. I actually tried
using application.wait and got the result I described in #3. The query table
doesn't fully populate while VBA is in wait mode for some reason. I just get
the upper left hand cell (the name of the query table). This happens even if
I set the wait time to be considerably longer than the query normally takes
to refresh. Have you been able to use application.wait and have a web query
populate successfully before VBA continues onward?

Thanks,
Glen

 

[DM Unseen]

Glen,

I've never had to bother with Webqueries myself. Maybe use the
Application.OnTime functionality and schedule a Proc to cancel the
backgroundquery after some time?

DM Unseen