Hmmm...I like the looks of your recommendation. I notice the "Err.Clear"
code, which is probably the main problem I was having. I'll try this out when
I get the chance and let you know how it turns out, Robin.

The 4-digit numbers are the individual ID numbers assigned by ESPN.com to
all major league baseball players. My worksheet has certain players listed
along with their ID numbers. I use the macro to pull statistics from each
player profile page on ESPN.com (as well as pulling daily stats for the
purpose of scoring fantasy baseball games).

Yes, it's really that important. :)

Thanks,
Hfly

"Robin Hammond" wrote:

I've never had any interest in baseball. What do all those numbers mean?

Anyway, this seems to do it reasonably reliably, with results of retry
management code at the end.

Sub TestQ()
Dim lRetries As Long
Const MaxRetries = 5
Dim lID As Long
Dim lTargetRow As Long
Dim qtInput As QueryTable

lID = 7000
lTargetRow = 1

For lID = 7000 To 7100

lRetries = 0
With Sheets(1)

.Range("A1:R7").ClearContents

Set qtInput = .QueryTables.Add(Connection:= _
"URL;http://sports.espn.go.com/mlb/players/profile?statsId=" &
lID, _
Destination:=.Range("A1"))

With qtInput

.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
RetryQuery:
On Error GoTo RetryTest
.Refresh BackgroundQuery:=False
On Error GoTo 0

End With

.Range("A1:R7").Copy
Sheets(2).Cells(lTargetRow, 1).Value = lID
Sheets(2).Cells(lTargetRow + 1, 1).PasteSpecial xlPasteValues
lTargetRow = lTargetRow + 8

Debug.Print "Success on " & lID

End With

NextID:

On Error Resume Next
qtInput.Delete
On Error GoTo 0

Next lID

Exit Sub

RetryTest:
Err.Clear
lRetries = lRetries + 1
If lRetries = MaxRetries Then
Debug.Print "FAILURE ON " & lID
Resume NextID
Else
Debug.Print "RETRY ON " & lID
Resume RetryQuery
End If
End Sub

Success as follows:

Success on 7007
Success on 7008
Success on 7009
Success on 7010
Success on 7011
Success on 7012
Success on 7013
RETRY ON 7014
RETRY ON 7014
Success on 7014
RETRY ON 7015
Success on 7015
RETRY ON 7016
RETRY ON 7016
Success on 7016
RETRY ON 7017
Success on 7017
RETRY ON 7018
RETRY ON 7018
RETRY ON 7018
RETRY ON 7018
RETRY ON 7018
Success on 7018
RETRY ON 7019
Success on 7019
RETRY ON 7020
Success on 7020
RETRY ON 7021
RETRY ON 7021
RETRY ON 7021
RETRY ON 7021
Success on 7021

It looks like a fairly unfriendly server to me.

Maybe you should get into a real sport like Rugby. At least it has a real
world championship and the right team wins!

Robin Hammond
www.enhanceddatasystems.com


"Hfly" wrote in message
...
Here's the code snippet in question. It is a modified version of a
recorded
macro. Keep in mind that the variable "id" is a 4-digit number, and this
code
is inside a for loop where the "id" variable is changed each time through.

Thanks for discussing with me,
Hfly

With Selection.QueryTable
.Connection = _
"URL;http://sports.espn.go.com/mlb/players/profile?statsId=" &
id
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With