Parameter.SetParam Method (Excel)
Defines a parameter for the specified query table.
Syntax
expression .SetParam(Type, Value)
expression A variable that represents a Parameter object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
Type |
Required |
One of the constants of XlParameterType which specifies the parameter type. |
|
Value |
Required |
Variant |
The value of the specified parameter, as shown in the description of the Type argument. |
Remarks
XlParameterType can be one of these XlParameterType constants. |
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 |
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