Excel - Error Trapping Web Queries

[basil3legs]

Hi,

Through an Excel 2000 Macro, I am opening a list of webpages through a Query. However, some of the pages do not always exist and hence the Macro crashes with a "Runtime error '1004' Unable to open....". I am trying to capture this error and jump to the next page on the list but it is still crashing when it hits the first page that doesn't exist.

The code I have is :

    With Selection.QueryTable
        .Connection = Worksheets("Page1").Cells(URLLine, 1).Value
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
    End With
    On Error GoTo JUMP
    Selection.QueryTable.Refresh BackgroundQuery:=False


    Code for when page exists


    JUMP:
    On Error GoTo 0

Any help will be greatly appreciated. Thank.

 

[steveouthwaite]

OK, first a disclaimer, I have no experience with querytables, so I'm assuming the rest of the code works , but I can help you wout with the errorhandling...

first thing, you need to include a label to return to, then once an error has been trapped, I would check to see if it is the one you're expecting and deal with accordingly
you have "on error goto 0" in the error trapper, but this tells the sub to go there when Annother error occurs

try this...

return_here_to_have_another_go:



With Selection.QueryTable
        .Connection = Worksheets("Page1").Cells(URLLine, 1).Value
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
    End With
    
    On Error GoTo errorhandler
    Selection.QueryTable.Refresh BackgroundQuery:=False


    [Code for when page exists]

    exit sub

    errorhandler:

    if error.number = 1004 then goto return_here_to_have_another_go

    ' you can add more code to deal with other errors
    ' I tend to add the next line at the end of errorhandlers, although many people would disagree... basically if an unknown error occurs, go back to the main code on the line after the one that caused the error and hope nothing terminal has happened :)
    resume next
    end sub

always remember to have "exit sub" before the errorhandler, coz you probably don't want that code executing at the end of your main routine.
good luck


SteveO

 

[basil3legs]

Thanks for the help SteveO.

I was under the impression 'On Error GoTo 0' just cancelled the error handling as a safety precaution in case another error occured elsewhere - please correct me if I am wrong.

I don't need the 'exit sub' either as all that is after the JUMP: is 'On Error GoTo 0' (as mentioned above so shouldn't cause a problem [and is fine when the pages exist]) and then the end of a 'Do... Loop Until...' statement which sends the next page to the query.

What is actually happening is that the loop goes through the list loading the pages fine until it hits one that does not exist. At that point, I get the Runtime error and it highlights the QueryTable.Refresh line as the guilty party (which makes sense) whereas when this happens, I want it to jump to the JUMP: label and then try and load the next page.

 

[basil3legs]

I have finally got this working!!

I have ended up using:

    On Error Resume Next
    Selection.QueryTable.Refresh BackgroundQuery:=False
    If Err.Number = 1004 Then GoTo errorhandler


    [Code for when page exists]


errorhandler:
    err.clear

Not ideal but at least it works!

 

[steveouthwaite]

Ah right, I see what you mean. You wanted to exclude a section, I thought you wanted to loop back and try annother url. Thought you were all lost and guessing that "on error goto 0" would send the execution back to the start of the sub. appologies for that.

That sees to be a solution to your problem, but as Selection.QueryTable.Refresh returns a boolean to indicate success (according to MSDN, but I havent checked)it would be 'cleaner' to use:

if Selection.QueryTable.Refresh BackgroundQuery:=False then
' [Code for when page exists]

end if

then if you implement errorhandling in the future, you won't get unexpected problems (if you have an earlier "on error goto marker" then your "if err.number = ...." line won't get executed, as the code will jump to "Marker:"

anyway, your solution works and that's all that matters



SteveO