Run-time Error 1004 - retry x times or end

[Guest]

Hi - am using the following querytable code to retrive data from a internet
site into excel where qurl is my webaddress.

With DataSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range(qStart))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Sometimes' I get Run-time error 1004. Unable to open (i.e. the aurl I have
specified). I know the link I have specified is valid, just that it fails
sometimes as if i continue from the break, it will complete after a retry or
2.

Can I somehow trap Error Code 1004 so that I can retry x times (say 5
times). If this fails, then end the sub-routine and run msgbox("The link is
currebtly unavailalbe").

This sounds logical to me, but I got no idea how to program it.
Bruce

 

[Mike Fogleman]

Try On Error Resume Next before your query code to stop the error from
halting the code.

Mike F

 

[Guest]

Thnak Mike - 2 questions

1. Do i put this line before my code?

2. qurl is an erray element. Will resume make it skip this element or retry
the one that caused the code to halt (as I want it to retry x times and then
exit if it fails?

Bruce

 

[Mike Fogleman]

1. Typically it would go on the line before the code where an error would be
expected. In this case right before:
With DataSheet.QueryTables.Add(Connection:="URL;" & qurl,
It would clear the error and resume on the next line of code following
the line that caused the error, effectively skipping that qurl, and allowing
the macro to end. You would then run the macro again if no results were
produced.

2. You didn't mention that this code was looping through different qurls.
This is a whole different situation. If I read you right, you want to stop
the loop if an error occurs and start another loop which will re-try the
qurl that failed, 5 more times, if it succeeds before the 5th attempt, then
exit the mini-loop and continue with the array loop. If it does not succeed
by the 5th attempt, then pop up a message, click on it, and skip that qurl
to continue on with the next.

If that is what you want, I would need some time to work that out.
Posting your entire subroutine is preferable over just a piece of it, so we
can get an overall sense of what the macro is doing, to better help you
modify it to get what you want. If the code is not in a standard module,
then state where it is. Also, if you have any public variables declared that
the macro uses, list them too.

Mike F