Programming Excel with VBA and .NET

Name

parameter.SetParam[= setting]

Synopsis

Defines the specified parameter. The following code creates a query table that uses the value in cell A1 as the parameter:

Dim strConn As String
Dim strSQL As String
Dim qt As QueryTable
Dim param As Parameter
 
strConn = "ODBC;DSN=MS Access Database;" & _
    "DBQ=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
 
Set qt = ActiveSheet.QueryTables.Add(Connection:=strConn, _
    Destination:=ActiveSheet.Range("C1"))
qt.CommandText = "SELECT * FROM Products WHERE (Products.ProductID=?)"
Set param = qt.Parameters.Add("ProductsParam")
param.SetParam xlRange, Range("A1")
qt.Refresh