Results 1 to 4 of 4

Thread: web query (xp)

  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    web query (xp)

    I have a a few web queries as part of a loop. sometime I get a run-time error. '1004'
    Application-defined or object-defined error
    this ends my loop with a message box allowing me to break the code or debug (the loop is basically a timer which makes the web queries run at a set interval)

    I think I have tracked the error down that the queries sometimes do not update themselves properly.
    I have tried manually updating All Quieries and sometimes I get this error box.

    "This web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and click edit query on the external data toolbar."

    here is a copy of 1 of the web queries which occur ( there are about 8 in total which happen 1 right after another)

    Range("A4").Select ''''''''''''''''''''''''' Select User Data Area query

    With Selection.QueryTable ''''''''''''''''' Update User Data Area query
    .Name = "User Data Area"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    End With
    Selection.QueryTable.Refresh BackgroundQuery:=False

    is there some way of running this query in a macro and if it returns the error code 1004 then to go back and run the query again to get the missing data.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: web query (xp)

    Maybe setting the BackgroundQuery property to False helps? (Webqueries are asynchronous when this is set to true and your code may continue without the querying being finished).

    Of course you can use error trapping to cater for any problems:

    <pre> Dim lCount as long
    On Error Goto LocErr

    'Code
    'More Code
    Exit Sub (or function)
    LocErr:
    If Err.Number=1004 Then
    If lCount<10 then
    lCount=lCount+1
    Resume
    Else
    Resume Next
    End If
    Else
    Msgbox "Unexpected Error: " & Err.number & ", " & Err.Description
    Resume Next
    End If
    End Sub (or function)</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: web query (xp)

    Could I not do just do something like this right in line with the query


    '''code
    '''more code

    on error goto User_Data_Area

    User_Data_Area:
    Range("A4").Select ''''''''''''''''''''''''' Select User Data Area query

    With Selection.QueryTable ''''''''''''''''' Update User Data Area query
    .Name = "User Data Area"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    End With
    Selection.QueryTable.Refresh BackgroundQuery:=False

    '''next query
    '''more code

    If I do this will it bounce back to the start if it encounters an error?

    do I have to use

    If Err.Number=1004 Then
    If lCount<10 then
    lCount=lCount+1
    Resume
    Else
    Resume Next
    End If

    and if I do have to does this go back up to the failed web query and start it again at that point in the code

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: web query (xp)

    You cannot jump to a label when you're within an error handling process, VBA expects either a Resume or Resume Next statement.

    My code retries the error causing statement maximum 10 times, then it skips the statement that caused the error.
    I forgot to reset the counter to zero, change:

    Else
    Resume Next
    End If

    to:

    Else
    lCount=0
    Resume Next
    End If
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •