Dynamic Web Queries

Instead of hard coding the parameter values in the code of your VBA procedures, you can create a dynamic query that will prompt the user for the parameter setting when the query is run.

The Portfolio2 procedure shown below uses the GET method for sending dynamic parameters. This procedure displays a dialog box prompting the user to enter stock symbols separated by spaces. Notice that the message is placed within square brackets and surrounded by double quotes, like this:

symbols=[""Enter symbols separated by spaces""]

When the web query is activated, a dialog box appears, as shown in Figure 16-19. Here the user may specify any stock symbols in which he or she is interested.

Sub Portfolio2()

Dim sht As Worksheet Dim qryTbl As QueryTable

' insert a new worksheet in the current workbook Set sht = ThisWorkbook.Worksheets.Add ' create a new Web query in a worksheet Set qryTbl = sht.QueryTables.Add(Connection:= _

"URL;http://uk.finance.yahoo.com/quote?symbols=[""Enter " &_ "symbols separated by spaces""]", _ Destination:=sht.Range("a1")) ' retrieve data from Web page and specify formatting ' paste data in a worksheet With qryTbl

.BackgroundQuery = True .WebSelectionType = xlSpecifiedTables .WebTables = "17"

.WebFormatting = xlWebFormattingAll .Refresh BackgroundQuery:=False .SaveData = True End With

End Sub

End Sub

Figure 16-19:

A dynamic web query requests parameter values from the user

Figure 16-19:

A dynamic web query requests parameter values from the user

The next example demonstrates a web query that uses the POST method with dynamic parameters:

1. In the Code window, enter the Currency_Exchange_POST procedure, as shown below.

Sub Currency_Exchange_POST() With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.xe.net/cgi-bin/ucc/convert", _ Destination:=Range("D1")) .PostText = "From=[""Enter the currency symbol from which " & _

"you want to convert""]&Amount=[ Enter amount you wish " & _

"to convert""]&To=[""Enter the currency symbol you want to obtain""]" .BackgroundQuery = True .WebSelectionType = xlSpecifiedTables .WebTables = "8"

.WebFormatting = xlWebFormattingAll .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub

2. Run the above procedure. Notice that the Currency_Exchange_POST procedure uses the PostText property of the QueryTable object for sending dynamic parameters to the web server. A web query can use a combination of static and dynamic parameters, as shown in the following code fragment:

"From=[""Enter the currency symbol from which " & _

"you want to convert ""]&Amount="1"& To=[""Enter the currency symbol " & _ " you want to obtain""]"

Because Amount is a static parameter, the user will only be prompted for the From and To currency symbols.

¡H MyWebCJ

ueries.xls

.|D|X|

IT

Î

ye.COtn Universal Cuirency Convener™ Results

2

Live mid-market tales as of2302.38. I3 23:28.28 GMT.

3

100.00 USD _ 419.300 PLN

A

United States Dollas — Poland Zlotyoh

5

i USD = 4.19300 PLN 1 FIN = &.238«3 USD

B

Another Conversion? | Mote Cuneitcies I Euro Info

7

Now available: XE.com Wireless Currency Seivices!

3

XE oom Fro* Currency Tools ] Fr«* Fl*t«s by E-Mail | Put This on Your Silt For Fro+!

3

"Unwe<s«l Currency Convener", "XE". "XE.COM". and the spinning currency logo are trad* marks of ><£ Corporation.

1fl

H *

» HKS

5heet4 / 5heetl / Sheet2 \sheet3/ | i | |

iin\

Figure 16-20: This worksheet has been generated by the Currency_Exchange_POST procedure.

Note: Web pages undergo frequent modifications. It's not uncommon to find out that a web query you prepared a while ago suddenly stops working because the web page address or parameters required to process the data have changed. If you plan on using web queries in your applications, you must watch for any changes introduced on the web sites that supply you with vital information. Particularly watch for table references. "Table 13" from a while ago could now be a totally different number, causing your query to retrieve data that you don't care about (or no data at all).

0 0

Post a comment