Macro stops when web query has connection error

[Straha]

I have a macro that runs several web queries on ticker symbol in a list. It returns the query data for a symbols then loops to the next symbol. While not extremely fast or efficient it gets the job done...until it has a web connection failure (usually a timeout issue) and at that point the macro just stops and reports the error. At this point I have to run the macro again on the entire set of ticker symbols.

Is there some additional code I can use that will allow the macro to "try again" instead of just stopping. I have thought about "On Error Resume Next" but I do not know enough about VBA to make it work.

TIA

Here is a sample of the code:

' Retrieve data for each symbol

For Each c In rng

If IsEmpty(c.Value) Then Exit For

' This query will retrieve the annual returns

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.smartmoney.com/fundsnapshots/index.cfm?story=return&symbol=" & c.Value _
, Destination:=Worksheets("Data").Range("B" & Application.WorksheetFunction.Match(c.Value, dtrng, 0)))
.Name = "Annual Returns for " & c.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "17"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

 

[BrianB]

Perhaps something like this. I have not tested whether the *Internet* failure generates a normal trappable error.

Sub skeleton_code_error_trap()
    On Error GoTo WaitAbit
    '- put normal code here
    '
    Exit Sub
'---------------------------------------------------------
WaitAbit:
    '- wait 10 seconds
    Application.Wait Now + TimeValue("00:00:10")
    Resume  ' has another go at the line
End Sub
'----------------------------------------------------------

 

[mrclox]

Just wanted to bring this post back up because it has solved a major problem for me and maybe others too... i know its a while back but thanks a million

 

[plw101]

web query

I have just discovered the joys of web query from excel, but sometimes have a situation where the server fails to respond at all and excel just freezes for ages.
Does anyone know how to put a timeout on the query, so that if this happens, it will give up after say 2 minutes ?

Peter

 

[matteboy2001]

I too am having this problem. One 2 second glitch in my internet can stall the workbook indefinitely. I am trying to fix it using the code given above, and that works when I tab through the code (using F8 repeatedly). But if I just let the code go (F5, or remove the breakpoint at the beginning of the sub procedure), I get the error. This section of the code looks like :

Private Sub RunPersonList()
On Error Resume Next
Range("A209:F350").Clear
ActiveWorkbook.RefreshAll
If btn_sendalert = False Then
Exit Sub
End If
r = 2
bool = False
While bool = False
If Sheet4.Cells(r, 1) <> "" Then

CheckCalls r, Sheet4.Cells(r, 2), Sheet4.Cells(r, 3), Sheet4.Cells(r, 5)
r = r + 1

Else: bool = True
End If
Wend
Wait

End Sub

This is the first time I have done anything with "On Error..." so I might be doing it wrong. Thanks for your help! This is a real-information flow I am trying to have here, so if it doesn't update for hours because it's waiting for me to click a button it's pretty much worthless!