XL98: How to Specify Dynamic Web Query Parameters (190803)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q190803

SUMMARY

A Web query is a new feature in Microsoft Excel 98 Macintosh Edition 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 a 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 Web site. This contact information is subject to change without notice. Microsoft in no way guarantees the accuracy of this contact information. Microsoft does not guarantee the examples will work indefinitely.

Running a Sample Query Included with Microsoft Office (Prompt Appears)

  1. Save and close any open workbooks, and then create a new workbook.
  2. On the Data menu, point to Get External Data, and then click Run Web Query.
  3. In the Run Query dialog box, click "Microsoft Investor Stock Quote" (in the Microsoft Office 98:Office:Queries folder), and then click Get Data.
  4. In the "Returning External Data to Microsoft Excel" dialog box, click OK.
  5. In the Enter Parameter Value dialog box type msft in the 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)

  1. Switch to Sheet2 in the workbook, and then type msft in cell A1.
  2. With A2 selected, point to Get External Data on the Data menu, and then click Run Web Query.
  3. Click "Microsoft Investor Stock Quote" in the Run Query dialog box, and then click Get Data.
  4. 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.
  5. 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 box, and then click OK.
  6. 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.
  7. Change the contents of cell A1 in Sheet2 to a different (valid) stock symbol.
  8. On the External Data toolbar, click the Refresh Data button.
Because 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)

  1. Activate Sheet3 in the workbook.
  2. Point to Get External Data on the Data menu, and then click Run Web Query.
  3. Click "Microsoft Investor Stock Quote" in the Run Query dialog box, and then click Get Data.
  4. In the "Returning External Data to Microsoft Excel" dialog box, click Parameters.
  5. In the Parameters dialog box, click "Use the following value," type msft in the box, and then click OK.
  6. In the "Returning External Data to Microsoft Excel" dialog box, click OK.

    A detailed stock quote for Microsoft Corp. (MSFT) is returned to the worksheet.
  7. On the External Data toolbar, click the Refresh Data button.
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.
  1. Click any cell in the results that are returned to Sheet3.
  2. On the External Data toolbar, click the Query Parameters button.
  3. 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.
  4. On the External Data toolbar, click the Refresh Data button.
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:

178870 XL98: How to Create Web Query Files

REFERENCES

For more information about Web queries, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

web, queries

and then click Show Topics. Select the "Get data from a Web source" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbhowto kbweb KB190803
©2002 Microsoft Corporation. All rights reserved.