Todayâ€™s author is Jan Karel Pieterse, an Excel MVP. You can find more useful tips from Jan Karel on his website: http://www.jkp-ads.com/ This post shows you how to dynamically work with data from a website in Excel.
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.
Youâ€™ll get this screen:
Enter http://moneycentral.msn.com/companyreport?symbol=MSFT and click the Go Button.
Donâ€™t do anything yet, first click the â€œOptionsâ€ button and set the webquery to return full html formatting results (if so desired):
After changing these settings, select the part you want to be imported into your sheet by clicking the appropriate black-on-yellow arrow:
Now weâ€™re ready to click â€œImportâ€. If the page takes time to load, youâ€™ll see a progress screen:
Next Excel asks where to put the results; put them in cell A3 so we have room above the table:
If you want an interactive result in your sheet, enabling you to enter new search criteria in a cell, you have to go through a little VBA. Donâ€™t worry, this is easy.
These steps are needed:
- Click any cell in the new table.
- Open the Visual Basic Editor by hitting the Alt+F11 key.
- Hit control+g to open the immediate window.
- Now type the following line:
- Click Enter to confirm.
- Close the Visual Basic Editor.
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 (we used a bit of VBA because with the MSN site you canâ€™t do this through the user interface). The string you entered will be used as both the name of the parameter and the prompt.
Click the refresh all button on the data tab of the ribbon, in the connections group. Excel will interpret the part between the square brackets as a parameter and prompt you for a value:
Excel detects we have a parameter and now asks what value you want.
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.
Click Visual Basic on the Developer tab, click Module on the Insert menu in VBE, and then paste the following code into the new module:
<span class="kwrd">Sub</span> Demo()
<span class="kwrd">Dim</span> oSh <span class="kwrd">As</span> Worksheet
<span class="kwrd">Set</span> oSh = ActiveSheet
<span class="kwrd">With</span> oSh.QueryTables.Add(<span class="str">"URL;http://www.jkp-ads.com"</span>, oSh.Range(<span class="str">"A1"</span>))
.BackgroundQuery = <span class="kwrd">False</span>
<span class="kwrd">End</span> <span class="kwrd">With</span>
<span class="kwrd">End</span> Sub
Click Run Sub/UserForm on the Run menu, or press F5 to run the macro.
The message box shows, that there are no parameters:
If the code shown above is changed to add a parameter in the connection string, things change:
Dim oSh As Worksheet
Set oSh = ActiveSheet
With oSh.QueryTables.Add( _
<span class="str">"URL;http://www.jkp-ads.com/Articles/["</span><span class="str">"PageName"</span><span class="str">"].htm"</span>, oSh.Range(<span class="str">"A1"</span>))
.BackgroundQuery = False
.SetParam xlRange, oSh.Range(<span class="str">"H11"</span>)
.RefreshOnChange = True
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.
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.