SetParam Method 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