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

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
 
P

Peter T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top