Results 1 to 2 of 2

Thread: querytable.refresh fails and error handling (alternatives?)

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    4

    querytable.refresh fails and error handling (alternatives?)

    Hello,

    Im running into a snag I can't figure out. I was hoping someone could give me a tip or point me in the right direction to resolve my conundrum.

    Im running a macro solution in excel to refresh tradingdata from the same site for different assets. These assets are approached in a loop and per loopcycle I am running an add.querytable connecting to a website and pulling data from 'Table 1'. This all works fine for the most part.

    Unfortunately, there are some assets for which the tradingdata on the site are in a different Table 'Table 0'. If my macro runs across these assets, the querytable fails on .Refresh BackgroundQuery:=False (error 1004). Logically, as the data is in another table.

    The code I am running as the refresh query is as follows;

    Code:
        ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Bron = Web.Page(Web.Contents(""" & Ticker_link & """))," & Chr(13) & "" & Chr(10) & "    Data1 = Bron{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"" = Table.TransformColumnTypes(Data1,{{""Date"", type date}, {""Price"", type text}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Vol."", type text}, {""Change %"", Pe" & _
            "rcentage.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"""
        xlstATD.Activate
    
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 1"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Table 1]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = Ticker_ID
            .Refresh BackgroundQuery:=False
        End With
    Now what I want to do have VBA check to see if the query was succesfull in pulling data from the site. If not, I want VBA to try a different query (for 'Table 0' and if that fails I want to switch a boolean, so that my script moves on to the next asset (loopcycle).

    I've been searching the internet and testing different sollutions, but I don't seem to find a manner in which I can have VBA check if the query failed. I found the .AfterRefresh operation, but I can not grasp how it works, assuming that operation checks to see if the add.querytable was a success.

    How can I have VBA check to see if the datadownload from the query was a succes?

    thanks in advance for tips and advice.

    regards.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: querytable.refresh fails and error handling (alternatives?)

    Code:
        ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Bron = Web.Page(Web.Contents(""" & Ticker_link & """))," & Chr(13) & "" & Chr(10) & "    Data1 = Bron{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"" = Table.TransformColumnTypes(Data1,{{""Date"", type date}, {""Price"", type text}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Vol."", type text}, {""Change %"", Pe" & _
            "rcentage.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"""
        xlstATD.Activate
    on error goto redo
    t = 2
    :redo
    t = t -1
    if t > - 1 then
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table" & t & "";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Table 1]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = Ticker_ID
            .Refresh BackgroundQuery:=False
        End With
       else
       mybool = true
    end if
    this is totally untested and was typed in the browser so may contain typos or code errors

    edit: fix an error in code
    Last edited by westconn1; Jan 11th, 2021 at 02:53 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width