Results 1 to 5 of 5

Thread: create a Web query Parameter

  1. #1

    create a Web query Parameter



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to create for the following url but am having great difficulty.

    HTML Code:
    https://docs.google.com/spreadsheets/d/1IJI83Lu-KDzIJigb6DDHrRiuo0jngpsBG9yyMHefSJ4/gviz/tq?tqx=out:html&gid=343315703&tq=select%20A%2CB%2CC%2CD%2CE%20WHERE%20C%3D%22Week%2036%22
    or this one which is decoded i believe

    HTML Code:
    https://docs.google.com/spreadsheets/d/1IJI83Lu-KDzIJigb6DDHrRiuo0jngpsBG9yyMHefSJ4/gviz/tq?tqx=out:html&gid=343315703&tq=select A,B,C,D,E WHERE C="Week 36"
    I trying to change the 36 so if i have a cellA1 if i change that value to 35 the url will change to 35 or could even do the whole "Week 35"

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Are you are wanting to just update the URL or fetch the information?

    if the former, in A1 add week number (eg 35), B1 = "https://docs.google.com/spreadsheets/d/1IJI83Lu-KDzIJigb6DDHrRiuo0jngpsBG9yyMHefSJ4/gviz/tq?tqx=out:html&gid=343315703&tq=select%20A%2CB%2CC%2CD%2CE%20WHERE%20C%3D%22Week%20" & A1 & "%22"

    If the latter, open macro recorder and record what you want to do, something like below and edit it to fix where required. There are easier way to do it but the forum here is Formula not VBA!

    Sub Macro2()
    Workbooks.Open Filename:= _
    "https://docs.google.com/spreadsheets/d/1IJI83Lu-KDzIJigb6DDHrRiuo0jngpsBG9yyMHefSJ4/gviz/tq?tqx=out:html&gid=343315703&tq=select%20A,B,C,D,E%20WHERE%20C=%22Week%2036%22"
    Range("A1:E5").Select
    Selection.Copy
    Windows("Book2").Activate
    ActiveSheet.Paste
    End Sub

  3. #3
    Code:
    Public Sub GetWeekData()Const WEEK_NUM As Long = 35
    Const URL As String = _
        "https://docs.google.com/spreadsheets/d/1IJI83Lu-KDzIJigb6DDHrRiuo0jngpsBG9yyMHefSJ4/gviz/tq?tqx=out:html&gid=343315703&tq=select%20A%2CB%2CC%2CD%2CE%20WHERE%20C%3D%22Week%20<weeknum>%22"
    Dim conn As Object
    
    
        Set conn = ActiveSheet.QueryTables.Add( _
            Connection:="URL;" & Replace(URL, "<weeknum>", WEEK_NUM), _
            Destination:=Range("$A$1"))
        With conn
            .Name = "Query for week " & WEEK_NUM
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    End Sub

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Nice Bob, I would have probably gone down the API route of DownloadURL.
    I was commenting more on the fact that the post was in Formula not VBA so an intermediate level person could manage with switching on the macro recorder but query tables and API calls probably way too advanced.

  5. #5
    I picked up on the thread title, web query, I took it literally and I used that approach. ALthough I put the required week number in a constant, it could of course easily be modified to pick that up from a cell.

    With APIs, with the possibility of 32bit or 64bit Excel, API calls have to cater for this.
    Last edited by Bob Phillips; 2014-09-10 at 08:45 AM.

Posting Permissions

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