Parameters Collection Object [Excel 2003 VBA Language Reference]

  • Article
QueryTable
Parameters
Parameter
Range

A collection of Parameter objects for the specified query table. Each Parameter object represents a single query parameter. Every query table contains a Parameters collection, but the collection is empty unless the query table is using a parameter query.

Using the Parameters Collection

Use the Parameters property to return the Parameters collection. The following example displays the number of parameters in query table one.

MsgBox Workbooks(1).ActiveSheet.QueryTables(1).Parameters.Count

Use the Add method to create a new parameter for a query table. The following example changes the SQL statement for query table one. The clause "(city=?)" indicates that the query is a parameter query, and the value of city is set 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

You cannot use the Add method on a URL connection query table. For URL connection query tables, Microsoft Excel creates the parameters based on the Connection and PostText properties.

Properties | Application Property | Count Property | Creator Property | Parent Property

Methods | Add Method | Delete Method | Item Method

Parent Objects | QueryTable

Child Objects | Range