Excel question: Can I add a timeout to a webquery?

Factual Questions
1

The following query works ok, but it sometimes gets stuck and hangs for a long time. What can I add so that if the query gets stuck it will timeout and move on to the next query? I have noticed that when it does get stuck that it is on the line that reads .Refresh BackgroundQuery:=False

Sub wbquery()
Dim n As Integer

For n = 1 To 100
With ActiveSheet.QueryTables.Add(Connection:=“URL;” & “Wikipedia, the free encyclopedia” & n & “”, _
Destination:=ActiveSheet.Range(“A65000”).End(xlUp).Offset(1, 0))
.Name = “myquery”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Delete
End With
Next n
End Sub

2

There may be a more direct approach to doing this, but I wonder if you might be able to watch the readystate ?

Disclaimer: I’m not that good at VBA. Some of this is mine; some is boilerplate stuff I’ve been able to scrounge up. And I never was able to figure out event listeners in VBA, which would surely be a better way.

This works for browser windows… if the same thing can be applied to the (spreadsheet?) doing the query, I wonder if you can modify this hack:



Public Sub browserTimeout(browser)
' next two lines are new, looking for a long wait
    Dim tries as Int
    tries=0
    Debug.Print "monitoring readyState"
    Application.Wait (Now() + TimeValue("0:00:05"))
    While browser.readyState <> 4 And tries < 100
' increment the number of tries and check the readyState every second
        tries=tries+1
        Application.Wait (Now() + TimeValue("0:00:01"))
    Wend
End Sub

I’ll cheerfully admit that’s a hack, but I wonder if it might tell you that’s a productive direction to go in.

3

Yes, this is the direction I want to take, but I am unclear how to apply the browsers code to the Excel code.