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

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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
Eri
------------------------------------------------------------------------------------------
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).Address)
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
 
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
 
Back
Top