Web Queries with Parameters

Web Query Options

Formatting gone]

C Rich text formatting only c Full HTML formatting Import settings for preforrratted <PRE> blocks P Import <PRE> blocks Into columns

I"* Treat cor&ecubve delmiterr- .3> one

Use tb° same import setting For the etfire section Other Import settings--

disable date recognition Disable Web query redirections

Figure 16-15: The Web Query Options dialog box

Often, in order to retrieve data from a web page, you need to specify parameters. To send parameters to the web server in your web query, use the POST or GET method after checking which of these methods the particular web server uses. You can find out this information in the following way:

1. Activate your browser and enter the address of a web page from which you want to retrieve information. For example, enter:

http://www.xe.net/ucc/

http://www.xe.net/ucc/

Figure 16-16: This web page allows you to convert one type of currency into another type.

Choose Source from your browser's View menu. The underlying code for this web page appears in Notepad (Figure 16-17).

In Notepad, choose Find from the Edit menu and type POST as the text to search for. If the parameters are being sent to the web server using the POST method, the word POST should appear highlighted, as shown below. Next to the POST method, there is a URL of the web server that supplies the data.

Ij0ui;C(11

Notepad

|D|x|

File Edit

Formal Help

<BR><FONT F.Ar:E-Verdana,.¿rial,Helvetica" S IZ&-2> For information on advertising on this page, <AHR

<BR><FONT F.Ar:E-Verdana,.¿rial,Helvetica" S IZ&-2> For information on advertising on this page, <AHR

w xe .comAjcc/convert .ogi">

<P><T£BLEWIDTH=100% CELLPADDING=1 CELLSPACINGS BORDER=D BGCOLOR=#000000><TR><T <TABLEWIDTH=100% CELLPADDING=3 CELLSPACINGS BORDER=D>

<TR VjALIGN=TOP yy_IGN=LEFT> <TD BGCOLOR=#FFF090> <TAB LE WI DTH= 1 DO % CELLPADDING=0 CELLSPACINGS BORDER=D><TR> <TD > < F 0 NT Faoe="Aial. Helvetica">Snbsp ; < !XER C- U C C1 - P1.0- R1 > < B > I want to convert... </B > </F 0 NT <TD ALI G N= F: IG HT> < F 0 NT Face=AArdana,Aial. Helvetica" SIZ&-1>using <B>live</B> currency rates< <n"R></TABLE> <HD> <fTR>

Figure 16-17:

You can view the source of the underlying data for a particular web page by selecting View | Source from your browser's menu. The underlying code will appear in Notepad.

Now that you know which method is used to send the parameters, you need to find out how the parameters are called.

In Notepad, choose Find from the Edit menu and type the word name as the search string. Next to the word "name" you should see text in quotes. This text is the name of the first parameter. After the word "value=", you should see the current value of a parameter. For example:

<INPUT TYPE="text" NAME="Amount" VALUE="1" SIZE=10><BR>

In the above HTML statement, the word "Amount" is the name of a parameter and "1" is the current value of this parameter. A value of the parameter can also be one of the options located in the HTML <option> tag. For example:

<SELECT NAME="From OPTION VALUE= OPTION VALUE= OPTION VALUE= OPTION VALUE= OPTION VALUE=

SIZE=5 onChange="CheckMore()"> EUR" SELECTED>EUR Euro</OPTION> USD">USD United States Dollars</OPTION> CAD">CAD Canada Dollars</OPTION> GBP">GBP United Kingdom Pounds</OPTION> DEM">DEM Germany Deutsche Marks</OPTION>

In the above HTML code segment, the word "From" is the name of a parameter. This parameter can have one of the following values: USD, CAD, GBP, DEM.

If a web server receives parameters using the GET method, you can see the parameter names and their values in your browser's address bar:

http://www.nycenet.edu/dist_sch/sch/searchresult/asp?boro=Manhattan _ &flag= schoolInfo2

Notice that the first parameter is preceded by a question mark. Parameters are separated from one another using the & sign.

Static and Dynamic Parameters

Web query parameters can be static or dynamic. When you use static parameters, you do not need to enter any values when running a web query (see the code of the Portfolio procedure below). A static web query always returns the same data.

If you use dynamic parameters, you can specify one or more values as criteria for your data retrieval while executing your web query (see the Portfolio2 procedure in the next section). A dynamic web query returns data based on the supplied parameters.

In the following example you will see how you can create a static web query programmatically. This web query will send parameters to the web server using the GET method. These parameters are static because you are not prompted to supply their values when the web query is run. The values of the parameters, which the server expects to receive, are coded inside the VBA procedure.

1. In the Visual Basic Editor window, insert a new module into the current VBA project—VBAProject (MyWebQueries.xls).

2. In the Code window, enter the code of the Portfolio procedure:

Sub Portfolio()

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=met&symbols=aol", _ 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

3. Run the Portfolio procedure. The result of this procedure is shown in Figure 16-18.

Ü HyV/ebBueues xl:

IQJXJ

1

B

C D E

F | G H | 1

■-

Si m Sol

Warme

Exchange Last Trade

Change Volume Related Information

"H

2

MET

METLIFE INC

NYSE 3-Aug «27.03

0.26 Ö.S7X 1,500,300 Chart. Nevs. T«h. Analysis, mo

-

3 "

AOL

AOL TIME VARNER

NYSE 9-Aug ♦11.05

-0.W -125% 21,376,400 Chart, News, Tech. Analysis, mo

S

Compare over 300 Credit Cards Online

h ^

1 H \Sheet4 / 5hest 1 / 5teet2 / Sheet3 /

IÎ ■ I

>m

Figure 16-18: A web query can retrieve data from a specific table on a web page.

Figure 16-18: A web query can retrieve data from a specific table on a web page.

Notice that the Portfolio procedure imports data contained in the 17th table of the specified web site (see the WebTables property setting). To retrieve data from two or more tables, separate their indices or names with commas. The table names or indices must appear in quotes. To view the explanations of other properties used in this procedure, see the Manhattan_Schools procedure earlier in this chapter.

When sending several values using the same parameter name, instead of repeating the parameter name (as shown in the Portfolio procedure), you can separate parameter values with the plus (+) sign, like this:

http://uk.finance.yahoo.com/quote?symbols=met+aol+ibm

Tip 16-2: Which Table Should I Import?

Web pages may contain many tables. Tables allow you to organize the content of the page. When viewing the HTML source code in Notepad, you can easily recognize tables by the following tags: <TABLE> (beginning of table) and </TABLE> (end of table). The <TD> tag indicates table data. This data will be placed in a worksheet cell when retrieved by Excel. Every new table row begins with the <TR> tag and ends with the </TR> tag. Many times, one table is placed inside another table (referred to as table nesting). Because tables are usually numbered in the HTML code, finding the correct table number containing the data you want to place in the worksheet usually requires experimentation. The Excel 2002 New Web Query dialog box provides a visual clue to which tables a particular page contains (see Figure 16-12 earlier in this chapter). By clicking on the arrow pointing to the table, you can mark a particular table for selection. You can then click the Save Query button to save the query to a file. When you open the prepared .iqy file in Notepad, you will see the number assigned to the selected table (as shown below). You can now use this number in your VBA procedure.

WEB 1

http://uk.finance.yahoo.com/quote? _ symbols=met+aol+ibm

Selection=17 Formatting=None

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

DisableDateRecognition=False

DisableRedirections=False

0 0

Post a comment