stop web query
Have not personally tried this on a web query but suppose it should work same
as for other kinds of query...
The resulting querytable has some properties and methods that can be used:
QueryTable.BackgroundQuery - this should be set to True so that Excel (and
your code) can still do things while waiting for the query results
QueryTable.Refreshing - tells you if the query is still refreshing (true) or
done (false)
QueryTable.CancelRefresh - to cancel refreshing
So you should be able to start the query and then use a loop (timed) to
check for a hung query:
Dim QuitTime as Date
QuitTime = Now() + TimeValue("00:02:00") - to give up after 2 minutes; this
can be set to your preference
' Now wait 'til the time has passed - OR we get results
' MyWebQueryTable should be set to be the querytable from your web query
While MyWebQueryTable.Refreshing and (Now() < QuitTime)
DoEvents ' Let Windows keep doing what it needs to do...
Wend
If MyWebQueryTable.Refreshing Then ' If we exceeded the set quit time...
MyWebQueryTable.CancelRefresh
MsgBox "Web Query Cancelled: Time limit exceeded!"
End If
- K Dales
"Jeremy Bertomeu" wrote:
Hi
My problem is the following: I use vba web query to download a large number
of tables from the web. It works fine but for some pages it occasionally
freezes while querying the web page. Since I am not sure how to stop it, I
was thinking there should be a way to stop the query if it takes too long to
download and have the code continue.
How can I do that ?
Thanks for your help,
Jeremy
|