Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects

Using Parameters With Web Queries

This article has been posted on the Microsoft Excel team blog on MSDN too.

Introduction

Excel provides a very useful option to gather data from websites, called web queries. These have been introduced with Excel 97 and have been further enhanced in the versions released after Excel 97. This article shows how you can setup a web query so that you can make the worksheet dynamically update based on values in so called parameter cells.

Setting up the web query

Setting up a web query is as simple as clicking the Data tab and then clicking the "From Web" button on the Get External Data tab:

Inserting a web query in Exccel 2007

For Excel 2003 and earlier you need Data, Get External data, New Web Query.

You’ll get this screen:

Inserting a web query in Excel

Enter www.bing.com and click the Go Button.

In the search box that follows, enter the word Question and hit enter. The screen refreshes and a new url appears in the address box:

http://www.bing.com/search?q=Question&form=QBLH&filt=all

Don’t do anything yet, first click the "Options" button and set the Web Query to return full html results (if so desired):Setting a WebQueries options

If you want these results in your sheet, just hit the import button now. However, if you want an interactive result in your sheet, enabling you to enter new search criteria in a cell, modify the url so it looks like this:

http://www.bing.com/search?q=["Question"]&form=QBLH&filt=all

You have now made part of the url work as a parameter, by replacing that part of the url with some text between quotes and square brackets (this will only work when using the URL would open a normal page in a web browser). The string you entered will be used as both the name of the parameter and the prompt. Excel will interpret the part between the square brackets as a parameter and prompt you for a value once you click the Import button or when you refresh the query table. Now we’re ready to click "Import". If the page takes time to load, you’ll see a progress screen: 

Waiting for results

Next Excel asks where to put the results; put them in cell A3 so we have room above the table:

Tell Excel where to put the results

Excel detects we have a parameter and now asks what value you want. As you can see you can select a cell for the parameter, lets use cell A1.

The fun thing is, that you can either fill in a value or you can select a cell as an input for the parameter:

Excel prompts for a parameter value

By checking the two checkboxes shown above, you ensure that Excel will automatically update the query table when you change the content of the cell(s) you selected.

Sometimes, inserting the parameter part in the url fails. In such cases, try modifying the routine called Demo2 as shown below so it has the URL you need and the parameters at the proper positions. Make sure you have the proper cell addresses in place as well. When done, run the routine. You should now be able to use the dynamic web query.

Working With Web Query Parameters In VBA

Excel VBA offers the programmer access to web query parameters through the Parameters collection of Parameter objects.

Unlike "normal" database queries, it is not possible to add a parameter to the Parameters collection using the Add method (though even for that type of query, doing so is an awkward process).

Instead one has to add all parameters one needs to the Connection string. Excel will determine the number of parameters from that string once it has been applied to the Connection property and then the Parameters collection becomes available to VBA. In the example below, a web query is added to a worksheet. The message box shows, that there are no parameters:

 Sub Demo()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    With oSh.QueryTables.Add("URL;http://www.jkp-ads.com", oSh.Range("A3"))
        .BackgroundQuery = False
        MsgBox .Parameters.Count
    End With
End Sub

Result:
Showing the number of parameters

If the code shown above is changed to add a parameter in the connection string, things change:

 Sub Demo2()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Make sure we already have a valid value for the parameter
    Range("A1").Value="WebQuery"
    With oSh.QueryTables.Add("URL;http://www.jkp-ads.com/Articles/[""PageName""].asp", oSh.Range("A3"))
        .BackgroundQuery = False
        MsgBox .Parameters.Count
        With .Parameters(1)
            .SetParam xlRange, oSh.Range("A1")
            .RefreshOnChange = True
        End With
    End With
End Sub

Now the messagebox shows:

Showing umber of parameters

Note that in the code sample the parameter has been tied to a cell (SetParam method) and that the query is set to update when that cell changes (RefreshOnChange property set to True). The SetParam method is the only way to change the setting of the parameter object from e.g. prompt to Range and to change the range the query parameter uses as its source.

Conclusion

As this article shows, adding a parameter to a web query is relatively easy. The tricky part is the fact that you need to know that parameters can only be added through the connect string (the URL) by using a very specific syntax and that parameters can only be added by changing the connection string.


Comments

Showing last 8 comments of 239 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (1/4/2016 10:39:26 AM)

Hi Kingman,

I'm afraid this cannot be done using a parameter query. However, with a little bit of VBA this is easy enough:

Sub UpdateWebQuery()
    ActiveCell.QueryTable.Connection = "URL;https://www.hkex.com.hk/chi/stat/smstat/dayquot/d" & Format(Date,"yymmdd") & "c.htm
    ActiveCell.QuerTable.Refresh
End Sub

 


Comment by: Craig Spencer (4/6/2016 10:50:41 PM)

I have a macro that utilized an iqy with one parameter, the macro creates a new tab for many possible lines on the main source data, it brings in the data perfectly and will then concatenate some of the fields back to the main tab... however I have a tab created with the name from the main sheet and it puts that value in cell A1. So I get prompt to enter the parameter each time it creates the new tab to pull the data from the web query. I just click on cell A1 and it all works fine, but it would be awesome if I could set the named parameter to cell A1 in each tab as it cycles through the create/paste web contents into each spreadsheet.

I tried to add the .SetParam xlRange, ActSht (which I had set as ActiveSheet previously).Range("A1"), I tried it with and without the "with" statement, just including it in the query definitions but with no luck.

Do I need to assign a parameter name with the .SetParam command?

 


Comment by: chinesecheung (8/21/2016 3:49:22 PM)

Jan,

I cut and pasted the followings to Excel VBA and run it and I received error message 400. Do I miss anything?

Tks
Chinese Cheung
=======================================
Comment by: Jan Karel Pieterse (1/4/2016 10:39:26 AM)
Hi Kingman,

I'm afraid this cannot be done using a parameter query. However, with a little bit of VBA this is easy enough:


Sub UpdateWebQuery()
    ActiveCell.QueryTable.Connection = "URL;https://www.hkex.com.hk/chi/stat/smstat/dayquot/d" & Format(Date,"yymmdd") & "c.htm
    ActiveCell.QuerTable.Refresh
End Sub

 


Comment by: Jan Karel Pieterse (8/21/2016 7:11:28 PM)

Hi chinesecheung,

Do you receive a VBA runtime error or an error by Excel, stating "unable to open https://....."?

Note that the cod eyou posted contains a typo (QuerTable instead of QueryTable)

 


Comment by: BIBEK (9/2/2016 11:38:03 AM)

i have all data to be used as paremeter in column a, for each data i have to run web query everytime specifying the paremaeter as a1,a2,a3... or there is some alternate convenient method.

 


Comment by: Jan Karel Pieterse (9/5/2016 8:03:27 AM)

Hi Bibek,

I would set this up as follows:

- Set up one sheet which has the parameterized webquery in place as demonstrated on this web page.
- Have a macro that copies the cells A1, A2, ... in turn to the parameter cell on the sheet with the webquery
- Wait for the webquery to refresh and then copy the results of the webquery to another worksheet.

 


Comment by: chinesecheung (9/8/2016 1:46:14 PM)

Hi Jan,

I corrected the typo and still I received Run-time error 11004'; Application-defined or object-defined error

 


Comment by: Jan Karel Pieterse (9/8/2016 3:18:06 PM)

Hi chinesecheung,

Make sure the selected cell is within the range of the querytable before running my code.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].