Programming Excel with VBA and .NET

Name

parameter.PromptString[= setting]

Synopsis

If the specified parameter uses a prompt string, this property returns the prompt string. The following code creates two parameter query tables on the active worksheet and uses the same prompt string for both:

Dim strConn 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 qt1 = ActiveSheet.QueryTables.Add(Connection:=strConn, _
    Destination:=ActiveSheet.Range("A1"))
qt1.CommandText = "SELECT * FROM Products WHERE (Products.ProductID=?)"
Set param1 = qt1.Parameters.Add("ProductsParam1")
param1.SetParam xlPrompt, "Please enter a Product ID."
 
Set qt2 = ActiveSheet.QueryTables.Add(Connection:=strConn, _
Destination:=ActiveSheet.Range("A5"))
qt2.CommandText = "SELECT * FROM Products WHERE (Products.ProductID=?)"
Set param2 = qt2.Parameters.Add("ProductsParam2")
param2.SetParam xlPrompt, param1.PromptString 
 
 
 
 

 
qt1.Refresh
qt2.Refresh