SetParam Method

Applies To

Parameter object.

Description

Defines a parameter for the specified query table.

Syntax

expression.SetParam(Type, Value)

expression Required. An expression that returns a Parameter object.

Type Required Long. The parameter type. Can be one of the following XlParameterType constants.

Constant

Description

xlConstant

Uses the value specified by the Value argument.

xlPrompt

Displays a dialog box that prompts the user for the value. The Value argument specifies the text shown in the dialog box.

xlRange

Uses the value of the cell in the upper-left corner of the range. The Value argument specifies a Range object.


Value Required Variant. The value of the specified parameter, as shown in the description of the Type argument.

Example

This example changes the SQL statement for query table one. The clause "(city=?)" indicates that the query is a parameter query, and the example sets the value of city to the constant "Oakland."

Set qt = Sheets("Sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors  WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", xlParamTypeVarChar)
param1.SetParam xlConstant, "Oakland"
qt.Refresh
This example sets the value of city to the value of cell A2 on worksheet two.

Set qt = Sheets("Sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors  WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", xlParamTypeVarChar)
param1.SetParam xlRange, Range("Sheet2!A1")
qt.Refresh