Class Parameters (Excel VBA)

A collection of Parameter objects for the specified query table. To use a Parameters class variable it first needs to be instantiated, for example

Dim prms as Parameters
Set prms = ActiveCell.QueryTable.Parameters

For Each

Here is an example of processing the Parameters items in a collection.

Dim prm As Parameter
For Each prm In ActiveCell.QueryTable.Parameters
	
Next prm

Add

Creates a new query parameter.

Add (Name, iDataType)

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

Arguments

The following argument is required

Name (String) - The name of the specified parameter. The parameter name should match the parameter clause in the SQL statement.

Optional arguments

The following argument is optional

iDataType (XlParameterDataType) - The data type of the parameter. Can be any XlParameterDataType constant. These values correspond to ODBC data types. They indicate the type of value that the ODBC driver is expecting to receive. Microsoft Excel and the ODBC driver manager will coerce the parameter value given in Excel into the correct data type for the driver.

Here you can find possible values for XlParameterDataType

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveCell.QueryTable.Parameters.Count

Delete

Deletes the object.

ActiveCell.QueryTable.Parameters.Delete

Item

Returns a single object from a collection.

The text name of the object is the value of the Name and Value properties.

Item (Index)

Index: The name or index number for the object.

Dim prm As Parameter
Set prm = ActiveCell.QueryTable.Parameters(Index:=1)