EXCEL How to change the REST URL based on the cell

Question

I created a web query as below in Excel. New Queries --> From Other Sources --> From Web

How do I pass in the cell value as a parameter to the Url?

I have search bunch of articles, including in the stackoverflow. None of them work.

There is 1 article says I can do this in VBA.

ThisWorkbook.Sheets("[SheetName]").QueryTables("[TableName]").Connection =

But it is not there.

Any one know how to do this?

More information:

It is connecting to a REST server, and geting back the JSON result.

I found an article discussing how to create a query link mine, but it has not options to add the parameters. Please see the link. Step 3 have the URL typed in.

https://success.planview.com/Planview_LeanKit/Reporting/Advanced_Reporting/030Configure_A_Connection_To_The_Reporting_API_With_Excel

Answer 1

Give the cell a name, e.g. pMyParameter.

Open the query editor and create a new blank query (New Query --> Other Sources --> Blank Query). Give that query a name similar (or same) to the cell name (let's call it pMyParameterQuery). Open the Advanced Editor and replace everything with the following.

let
    Source = Excel.CurrentWorkbook(){[Name="pMyParameter"]}[Content]{0}[Column1]
in
    Source

You'll see in the query list that this query does not return a table but text (or a number / date / ... depending on the contents of your named cell).

Now switch to your web query and open its Advanced Editor. Find the line that has the URL, probably something like this:

Source = Web.Page(Web.Contents("http://www.example.com?someParameter=someValue")),

Here you can use the result of the parameter-query in the URL like this:

Source = Web.Page(Web.Contents("http://www.example.com?someParameter=" & pMyParameterQuery)),