Customize a parameter query

You can customize a Microsoft Query or Web query parameter query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) by changing the prompt, by using data from a cell as the prompt, or by using a constant.

For more information about creating parameter queries, see Microsoft Query Help or Get external data from a Web page.



Change the custom prompt for a parameter query

  1. On an Excel worksheet, click a cell anywhere in an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) that was created with a parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.).
  2. On the Data tab, in the Connections group, click Properties.

Excel Ribbon image

  1. In the Properties dialog box, click Connection Properties Button image.
  2. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
  3. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
  4. In the Prompt for value using the following string box, type the text that you want to use for the prompt, and then click OK. The custom prompt can contain up to 100 characters.
  5. To use the new custom prompt and refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.
  6. In the Enter Parameter Value dialog box, the new prompt is displayed.

 Note   Optionally, to prevent being prompted again during each refresh operation, you can select the Use this value/reference for future refreshes check box. If you use data from a cell as the parameter value, select the Refresh automatically when cell value changes check box.

 Note   The preceding procedure changes the custom prompt only for the current session of the workbook. If you want to permanently save your changes to the custom prompt, save the workbook or change the prompt for the query. For more information about changing a query, see Microsoft Query Help or Get external data from a Web page.

Top of Page Top of Page

Use data from a cell as a parameter value

  1. On your worksheet, type the values that you want to use as criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) in the query.
  2. Click a cell anywhere in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) that was created with a parameter query.
  3. On the Data tab, in the Connections group, click Properties.

Excel Ribbon image

  1. In the Properties dialog box, click Connection Properties Button image.
  2. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
  3. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
  4. Click Get the value from the following cell.
  5. On the worksheet, click the cell that contains the value that you want to use.

If you want to refresh the data whenever you change the value in the cell, select the Refresh automatically when cell value changes check box.

  1. Click OK.
  2. To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.

Top of Page Top of Page

Use a constant parameter value for a query

  1. In Excel, click a cell in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) that was created with a parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.).
  2. On the Data tab, in the Connections group, click Properties.

Excel Ribbon image

  1. In the Properties dialog box, click Connection Properties Button image.
  2. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
  3. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
  4. Click Use the following value.
  5. Type the value that you want to use for the parameter, and then click OK.
  6. To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.

Top of Page Top of Page

 
 
Applies to:
Excel 2007