The information in this article applies to:
- Microsoft Excel 97 for Windows
SUMMARY
A Web query is a new feature in Microsoft Excel 97 that allows you to
retrieve data stored on your intranet, the Internet, or the World Wide Web.
A Web query can use static parameters, dynamic parameters, or a combination
of both. Queries with static parameters send a query without any input;
queries with dynamic parameters prompt you for input. Regardless of the
type of parameters in the query, the requested information is pulled from
an Internet or an intranet site, and the results are placed in a worksheet.
When you use dynamic parameters, you can use the following three methods
for obtaining the parameter values:
- Use a dialog box that prompts you to type the values.
- Specify the values to use.
- Get the values from a worksheet cell.
This article provides an sample dynamic Web query and the steps for setting
the parameter values for the query using the three methods.
MORE INFORMATION
In each of the examples, the same Web query file is used to generate the
initial results that are returned to a worksheet. After you perform a query
to return data to a worksheet, the Web query file is no longer used to
update the data in the worksheet; the query is stored in the worksheet.
In some of the examples below, you use the Parameters dialog box to make
changes to the parameters in a query. Note that these changes do not affect
the Web query file you initially use to run the query; any changes you make
to the query parameters are stored in the worksheet.
NOTE: The examples in this article use a third-party Web site. Microsoft
does not guarantee the examples will work indefinitely; the Web site is not
owned or maintained by Microsoft.
Running a Sample Query Included with Microsoft Office (Prompt Appears)
- Save and close any open workbooks, and then create a new workbook.
- On the Data menu, point to Get External Data, and then click Run Web
Query.
- Click "Detailed Stock Quote by PC Quote, Inc.iqy" in the Run Query
dialog box, and then click Get Data.
NOTE: The "Detailed Stock Quote by PC Quote, Inc.iqy" file is in the
Program Files\Microsoft Office\Queries folder.
- Click OK in the "Returning External Data to Microsoft Excel" dialog box.
- In the Enter Parameter Value dialog box type "msft" (without the
quotation marks) in the RefEdit box, and then click OK.
A detailed stock quote for Microsoft Corp. (MSFT) is returned to the
worksheet.
Running Sample Web Query (Worksheet Cell Value)
- Switch to Sheet2 in the workbook, and then type "msft" (without the
quotation marks) in cell A1.
- With A2 selected, point to Get External Data on the Data menu, and then
click Run Web Query.
- Click "Detailed Stock Quote by PC Quote, Inc.iqy" in the Run Query
dialog box, and then click Get Data.
- In the "Returning External Data to Microsoft Excel" dialog box, click
Parameters.
The Parameters dialog box appears. Notice the option for this Web query
uses a prompt for the parameter value.
- In the Parameters dialog box, click "Get the value from the following
cell," click cell A1 to insert the reference "=Sheet2!$a$1" in the
RefEdit box, and then click OK.
- Click OK in the "Returning External Data to Microsoft Excel" dialog box.
A detailed stock quote for Microsoft Corp. (MSFT) is returned to the
worksheet.
- Change the contents of cell A1 in Sheet2 to a different (valid) stock
symbol.
- On the External Data toolbar, click Refresh Data.
Since the contents of cell A1 changed, a detailed stock quote for the new
stock symbol is returned to the worksheet.
Running Sample Web Query (Set Parameter Value)
- Activate Sheet2 in the workbook, and then type "msft" (without the
quotation marks) in cell A1.
- With A2 selected, point to Get External Data on the Data menu, and then
click Run Web Query.
- Click "Detailed Stock Quote by PC Quote, Inc.iqy" in the Run Query
dialog box, and then click Get Data.
- In the "Returning External Data to Microsoft Excel" dialog box, click
Parameters.
- In the Parameters dialog box, click "Use the following value," type
"msft" (without the quotation marks) in the box, and then click OK.
- Click OK in the "Returning External Data to Microsoft Excel" dialog box.
A detailed stock quote for Microsoft Corp. (MSFT) is returned to the
worksheet.
- On the External Data toolbar, click Refresh Data.
You are not prompted for any value, and the same results are returned to
your worksheet. However, if you click Refresh Data on another day, an
updated stock quote for Microsoft Corp. is returned to the worksheet.
Changing the Parameter Value for a Worksheet
If you want to use a specific value for a parameter, as in the previous
example, you can change this value in the Parameters dialog box.
- Click any cell in the results that are returned to Sheet3.
- On the External Data toolbar, click Query Parameters.
- In the Parameters dialog box, change the contents of the box below
"Use the following value" to a different stock symbol, and then click
OK.
- On the External Data toolbar, click Refresh Data.
The stock quote in Sheet3 is changed to reflect the new stock symbol you
typed in step 3.
For additional information, please see the following article in the
Microsoft Knowledge Base:
ARTICLE-ID: Q157482
TITLE : XL97: How to Create Web Query (.iqy) Files
REFERENCES
For more information about Web queries, click the Index tab in Microsoft
Excel Help, type the following text
web, queries
and then double-click the selected text to go to the "Create a Web query"
topic.
|