create a Web query Parameter

N

nikimitsy

New member
Joined
Mar 2, 2014
Messages
2
Reaction score
0
Points
0
I am trying to create for the following url but am having great difficulty.

HTML: 
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: 
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"
 
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
 
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
 
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.
 
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:
You must log in or register to reply here.
Back
Top