Prev: Alpha Numeric’s bunch together
Next: Button to take the user in a different place on the same sheet
From: Eric on 1 Apr 2010 02:15
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).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

From: Peter T on 1 Apr 2010 04:16
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" <Eric(a)discussions.microsoft.com> wrote in message
news:61DA48C4-DB2E-4C53-AD68-681F1D994EC1(a)microsoft.com...
>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).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
>


 | 
Pages: 1
Prev: Alpha Numeric’s bunch together
Next: Button to take the user in a different place on the same sheet