Share via

Parameter.SetParam Method

  • Article
Excel Developer Reference

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 XlParameterType 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.”

Visual Basic for Applications 
  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

See Also