Web Query Refresh Issues

[Guest]

I'm using Excel 2002 SP3...

I wrote a macro over a year ago that performs several web queries and
copies/pastes data between each new query. Last year, after writing the
macro, the program would nicely zip through all of the queries and everything
would be great. The last time I ran the macro successfully was in October
2004.

In April 2005, I ran the macro again. The URLs in question are still exactly
the same. The problem now is the run-time error '1004', "unable to open" the
URL. If I hit "Debug", then run it again to continue, it might successfully
refresh that web query and move on. It's hit or miss.

My theory is that the web query refresh method is not being patient enough
for data. I can immediately open up an IE window and load the same pages
easily. I'm wondering if the refresh method is not waiting long enough for
the data to load. Is there a way to set a parameter to tell it to wait longer
for the URL to open?

Alternately, I've attempted to write an error-handling mechanism, but the
best I can do is to get it to refresh one extra time. After that, it just
crashes. I know I'm flirting with an infinite loop, but it would be nice to
get the query to keep trying until it finds the data. I know it's out there!

Any feedback would be greatly appreciated.

Thanks,
Hfly

 

[Robin Hammond]

Hfly,

Unfortunately, there is no such thing as a webquery timeout parameter. It
should have been obvious, but they didn't include it.

You could try posting your code if the sites are public and somebody will
have a look at it.

Robin Hammond
www.enhanceddatasystems.com

 

[Don Guillett]

As usual, post your macro for comments

 

[Guest]

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

 

[Don Guillett]

After a problem or two I got this to work for 7307 & 7308. It could be
improved.

Sub doloop()
x = 3
For Each c In [mylist]
[a1].Select
With Selection.QueryTable
.Connection = _
"URL;http://sports.espn.go.com/mlb/players/profile?statsId=" & c
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.Refresh BackgroundQuery:=False
End With
Sheets("sheet1").Range("b3:r3").Copy Sheets("sheet2").Range("b" & x)
x = x + 1
Next c
End Sub

 

[Guest]

The issue is not the loop around the web query, it's the query itself. When I
run the macro, it loops through about 80 different values of "id", and all of
these "id" values, when combined with the URL in my code, form an existing
webpage.

When I run the macro, it goes on fine until the run-time error occurs. Then
I hit Debug, then continue running it, and keep doing that until it loads the
query okay. For the most part, it eventually finds the data, but some don't
load at all. Between April and October of last year, I ran this macro a few
times each week and never had a run-time error, so something is different
that's causing problems.

Robin responded to my theory with something that unfortunately makes sense.
So now the issue is error response. Instead of me having to manually hit
Debug and Continue with each run-time error, I'd like to have code that
recognizes the error and refreshes the QueryTable again. Any assistance with
that would be appreciated.

Thanks,
Hfly

After a problem or two I got this to work for 7307 & 7308. It could be
improved.

Sub doloop()
x = 3
For Each c In [mylist]
[a1].Select
With Selection.QueryTable
.Connection = _
"URL;http://sports.espn.go.com/mlb/players/profile?statsId=" & c
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.Refresh BackgroundQuery:=False
End With
Sheets("sheet1").Range("b3:r3").Copy Sheets("sheet2").Range("b" & x)
x = x + 1
Next c
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

As usual, post your macro for comments

 

[Robin Hammond]

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

 

[Guest]

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

 

[Guest]

Well, it looks like this does the trick, though, as you mentioned, I'm now
very disappointed in the server I'm getting data from.

Thanks for your help, Robin!

Hfly

 

[Guest]

Like you I used QueryTables extensively a few years ago. Since then the
web-sites have become more complicated, requiring a user to login, using
frames so that the actual URL needed is not displayed in the address line and
also using Java Virtually none of the modules I wrote then continue to work.
Even the microsoft example given in
http://support.microsoft.com/default.aspx?scid=kb;en-us;213730 fails. For
what it is worth I found the following sub which detects an error 1004 and
retries the Query.

Public Sub Get_Query_Data(a_strSheet As String, a_strConnection As String)
Dim intErrorCount As Integer, intResponse As Integer
On Error GoTo GetQueryData_Error
intErrorCount = 0
Worksheets(a_strSheet).Activate

GetQueryData_Retry:
With ActiveSheet.QueryTables.Add(Connection:=a_strConnection, _
Destination:=Range("A1"))
.RefreshStyle = xlOverwriteCells
.Refresh (False)
End With
Data_Obtained:
Exit Sub

GetQueryData_Error:
If Err.Number = 1004 Then 'This error occurs if the Web site cannot be
found
intErrorCount = intErrorCount + 1
If intErrorCount > 10 Then
intResponse = MsgBox("Error Count Exceeded", vbRetryCancel)
If intResponse = vbCancel Then
Exit Sub
End If
End If
Resume GetQueryData_Retry
End If
End Sub

Hope this helps.
--
Thanks in anticipation

The issue is not the loop around the web query, it's the query itself. When I
run the macro, it loops through about 80 different values of "id", and all of
these "id" values, when combined with the URL in my code, form an existing
webpage.

When I run the macro, it goes on fine until the run-time error occurs. Then
I hit Debug, then continue running it, and keep doing that until it loads the
query okay. For the most part, it eventually finds the data, but some don't
load at all. Between April and October of last year, I ran this macro a few
times each week and never had a run-time error, so something is different
that's causing problems.

Robin responded to my theory with something that unfortunately makes sense.
So now the issue is error response. Instead of me having to manually hit
Debug and Continue with each run-time error, I'd like to have code that
recognizes the error and refreshes the QueryTable again. Any assistance with
that would be appreciated.

Thanks,
Hfly

After a problem or two I got this to work for 7307 & 7308. It could be
improved.

Sub doloop()
x = 3
For Each c In [mylist]
[a1].Select
With Selection.QueryTable
.Connection = _
"URL;http://sports.espn.go.com/mlb/players/profile?statsId=" & c
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.Refresh BackgroundQuery:=False
End With
Sheets("sheet1").Range("b3:r3").Copy Sheets("sheet2").Range("b" & x)
x = x + 1
Next c
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

As usual, post your macro for comments

--
Don Guillett
SalesAid Software
(e-mail address removed)
I'm using Excel 2002 SP3...

I wrote a macro over a year ago that performs several web queries and
copies/pastes data between each new query. Last year, after writing the
macro, the program would nicely zip through all of the queries and
everything
would be great. The last time I ran the macro successfully was in October
2004.

In April 2005, I ran the macro again. The URLs in question are still
exactly
the same. The problem now is the run-time error '1004', "unable to open"
the
URL. If I hit "Debug", then run it again to continue, it might
successfully
refresh that web query and move on. It's hit or miss.

My theory is that the web query refresh method is not being patient enough
for data. I can immediately open up an IE window and load the same pages
easily. I'm wondering if the refresh method is not waiting long enough for
the data to load. Is there a way to set a parameter to tell it to wait
longer
for the URL to open?

Alternately, I've attempted to write an error-handling mechanism, but the
best I can do is to get it to refresh one extra time. After that, it just
crashes. I know I'm flirting with an infinite loop, but it would be nice
to
get the query to keep trying until it finds the data. I know it's out
there!

Any feedback would be greatly appreciated.

Thanks,
Hfly

 

[Guest]

Robin,

Thanks for this cool enhancement of the QueryTables function! Because of it,
I have been able to rewrite my code to use it instead of an awkward bunch of
SendKeys statements that were hard to debug since I could not step through
the code.

Great stuff! Far more elegant than what I was doing.

Gregg Roberts