stop web query

[Guest]

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

 

[Guest]

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

 

[Guest]

Hello,
I am still stuck. This is in fact the first time I try to modify a query. My
query is the following:

With ActiveSheet.QueryTables.Add(Connection:= _
ITEMIDWEB _
, Destination:=Range("A1"))
.Name = "MyWebQueryTable"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.Refresh BackgroundQuery:=False

End With

where should I put the code?