How to re-fresh the import links if it fails?
 

I would like to retry 5 times to retrieve data from import links if it fails
to be retrieved. Does anyone have any suggestions on how to refresh the
import links if it fails?
Thanks in advance for any suggestions
Eric
------------------------------------------------------------------------------------------
Dim qtEntry As QueryTable
Dim qryConnect As String
Dim anyRange As Range
Dim anySheet As Worksheet

Set anyRange = Sheets("1").Range("A1:" & _
Sheets("1").Range("A1").SpecialCells(xlLastCell).A ddress)
On Error Resume Next ' errors if no querytable entry
anyRange.QueryTable.Delete
On Error GoTo 0
anyRange.ClearContents

qryConnect = "URL;http://www.cnn.com"

Set anySheet = Sheets("1")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=anySheet.Range("$A$1"))

.Name = "Summary"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

How to re-fresh the import links if it fails?
 

If I follow include something like this

With ....
' code
On Error Resume Next
' .Connection = "URL;http://www.cnn123456.com"

For i = 1 To 5
' If i = 5 Then
' .Connection = "URL;http://www.cnn.com"
' End If

.Refresh BackgroundQuery:=False
If Err.Number Then
Err.Clear
Else
Exit For
End If
Next
If i = 6 Then
Debug.Print "failed"
Else
Debug.Print i & " attempt(s) success"
End If

On Error GoTo 0
End With

uncomment the commented lines for testing

Regards,
Peter T

"Eric" wrote in message
...
I would like to retry 5 times to retrieve data from import links if it
fails
to be retrieved. Does anyone have any suggestions on how to refresh the
import links if it fails?
Thanks in advance for any suggestions
Eric
------------------------------------------------------------------------------------------
Dim qtEntry As QueryTable
Dim qryConnect As String
Dim anyRange As Range
Dim anySheet As Worksheet

Set anyRange = Sheets("1").Range("A1:" & _
Sheets("1").Range("A1").SpecialCells(xlLastCell).A ddress)
On Error Resume Next ' errors if no querytable entry
anyRange.QueryTable.Delete
On Error GoTo 0
anyRange.ClearContents

qryConnect = "URL;http://www.cnn.com"

Set anySheet = Sheets("1")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=anySheet.Range("$A$1"))

.Name = "Summary"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With