Running a web query with parameter from a cell within Excel

Question

I am trying to do a GET request when I change a value in a cell.

The link here:

https://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=metars&requestType=retrieve&format=xml&stationString=(PARAMETER SHOULD BE HERE)&hoursBeforeNow=1

So what's in this cell, should change this query. Is this possible, and if so, does anyone have a good solution for this? Been trying to google all night but can't seem to find anyone that has done the same thing.

Answer 1

Adapting from this answer, this is a two-step process.

Firstly, create a named range for the cell that stores the value you will pass to the query. Details on how to do so are here. I used "StationName" as the name of the range but you can use any name you like.

Secondly, we need to incorporate the named range into the source for the query. To do so:

• select any cell in the data table from the current query
• click on the Query tab in the ribbon
• click on Edit
• look for the Query Settings panel at the right of the screen - if it's not there, go to View then click on Query Settings
• the first entry in the Query Settings panel should be Source - click on that entry to select it
• make sure that the formula bar is displayed by going to View then checking Formula Bar
• the entry in the formula bar is too long to display so click on the V symbol at the end of the formula bar to expand it
• edit the formula bar text to the text shown below - make sure to change the name of the Excel range shown at Name="StationName" to whichever name you have used

= Xml.Tables(Web.Contents("https://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=metars&requestType=retrieve&format=xml&stationString=" & Excel.CurrentWorkbook(){[Name="StationName"]}[Content]{0}[Column1] & "&hoursBeforeNow=1"))

This picture shows how things should now look:

Now go to Home and click on Close & Load.

You may get a warning about privacy settings - to fix this, I chose to give both the Excel file and the aviationweather.gov site the "public" level of privacy.

You can access the privacy settings by selecting a cell in the data table, going to Query and choosing Edit, going to Home and choosing Data Source Settings, selecting Global Permissions and then using Edit Permissions on the relevant entries.

To make the query automatically refresh when the cell value changes, we need to use some VBA. We will need to know which sheet the cell is on, the name of the named range which refers to that cell and the name of the query we want to refresh. The first two should be straightforward and the name of the query is just "Query - " followed by the name shown in the Queries & Connections panel.

Open up the VBA Editor with ALT-F11, double-click on the relevant sheet object in the Project Explorer to open up the related code module and paste in the following code (but change the name of the named range and the name of the query to match your names):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, ThisWorkbook.Names("StationName").RefersToRange) _
    Is Nothing And Target.Cells.Count = 1 Then
    Application.EnableEvents = False
    ThisWorkbook.Connections("Query - Get station from aviationweather dot gov").Refresh
    Application.EnableEvents = True
End If

End Sub

Now changing the cell value should cause the query to refresh automatically

---

Here's some simple code to list the name of every connection in the workbook. In the VBA editor, use Insert > Module to create a new module and then paste in the following code:

Option Explicit

Sub listConnections()

Dim c As WorkbookConnection

For Each c In ThisWorkbook.Connections
    MsgBox c.Name
Next c

End Sub

Each connection name will pop up in a message box like this:

and you can just copy the name you need when it pops up. You can copy the message box contents just by hitting CTRL-C but you'll need to edit out some extra stuff when you paste. Copying the message box shown above results in the following text being placed on the clipboard:

---------------------------
Microsoft Excel
---------------------------
Query - Get station from aviationweather dot gov
---------------------------
OK   
---------------------------

Answer 2

I get daily Exchange Rate data from cbr.ru in this fashion.

screenshot of the Connection

http://www.cbr.ru/eng/currency_base/daily/?UniDbQuery.Posted=True&UniDbQuery.To=["Ticker"]

Where i use the date as a variable from an excel cell, in such a way that it refreshes automatically once the cell value changes. (named the parameter as Ticker)

screenshot of the Parameters

Hope this uber simple method helps.