Using Parameters With Web Queries

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 choosing Data, Get External data, New Web Query.

In the next dialog, you enter the URL of the site you want to extract data from. For example:

http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2003-44,GGLD:en&q=%22Jan+Karel+Pieterse%22

To make part of the url work as a parameter, replace 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):

http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2003-44,GGLD:en&q=["SearchFor"]

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. The string you entered will be used as both the name of the parameter and the prompt:

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

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.

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("A1"))
        .BackgroundQuery = False
        MsgBox .Parameters.Count
    End With
End Sub

Result:

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

Sub Demo()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    With oSh.QueryTables.Add("URL;http://www.jkp-ads.com/Articles/[""PageName""].htm", oSh.Range("A1"))
        .BackgroundQuery = False
        MsgBox .Parameters.Count
        With .Parameters(1)
            .SetParam xlRange, oSh.Range("H11")
            .RefreshOnChange = True
        End With
    End With
End Sub

Now the messagebox shows:

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 66 in total (Show All Comments):

Comment by: Dennis ODonovan (8/15/2008 1:26:14 PM)

Can this method be used to parameterize a database query (querying a database on
our corporate network), or does it only work with web queries? Is there a link to
instructions on how to parameterize a database query? Sorry if this is posted in
the wrong area. Thanks!

Comment by: Jan Karel Pieterse (8/17/2008 10:03:14 PM)

Hi Dennis,

Certainly:

<a target="_blank" href="http://www.dicks-clicks.com/excel/ExternalData6.htm">Check
out this page.</a>

Comment by: Dennis ODonovan (8/18/2008 11:57:20 AM)

Thanks, Jan Karel!

I am ashamed to say I am a lazy programmer and was looking for a simpler way to do
this. I found such a way here (you may recognize the site!):
http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-
data-queries/ but was having trouble getting it to work with aggregated fields
(e.g. count, sum, etc.), even found a comment asking about the same error I was
getting: "Not a valid column name" on the criteria value. Anyway, I played around
with it and got a workaround: build a simple query without the aggregated fields,
but be sure to include the fields you want to parameterize, and set up the
parameters. Then if you modify the query to include the aggregated fields the
parameters are retained since they are already established. But if the aggregated
fields are specified in the initial query the MS Query tool would not allow
parameters.

Thanks again!

Comment by: Jim (9/5/2008 8:50:45 AM)

Is there a way to set up a web query so that the user is prompted for the url of
the table to be imported? I'n trying to set up queries for multiple web beased
tbales, and some of the tables haven't been made yet.

Comment by: Jan Karel Pieterse (9/8/2008 12:58:33 AM)

Hi Jim,

It depends on the exact way the url is set up. If part of the url is an argument to
show specific data (like I show in the example above), then you can do that.

Comment by: Thomas Hoofensmire (10/24/2008 7:02:08 AM)

I tried to set up a web query to get information from my online bank account. How
can I set up the web query to use my logon information so that I can get to the
summary page of my online bank account? Some people from my work suggested that I
might have to use "send keys". No idea what that is, but I am willing to try it if
it works.

Comment by: Jan Karel Pieterse (10/24/2008 7:43:49 AM)

Hi Thomas,

It might work, but is tricky to do. The routine might look like this:

Sub GetQuery()
SendKeys "%ddwwww.jkp-ads.com~{TAB}username{TAB}password~%i~"
End Sub

Comment by: S.Suresh Mumbai (12/22/2008 10:53:51 AM)

Any best tutorial for webquery- advanced level. Thanks