Programming Excel with VBA and .NET

Working with Parameter Objects

The Parameter object lets you supply parameter criteria to limit the data returned by a query. This is useful if you want to create a query that returns a general set of data but you want to work with different subsets of that data or different individual records. You can supply different parameters rather than creating a new query for each subset or record.

You create a parameter by adding a Parameter object to the Parameters collection of a QueryTable object. You can then supply a specific parameter value or use a value in a cell on your worksheet. For example, 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