Macro for Parameter Query

[bchan]

I was able to make Excel pop-up a parameter dialogue box by creating
macro and changing the "SELECT ... " statement by adding the "WHER
field = ?" clause. However, it does not give a descriptive prompt
something like "Enter the branch code". In MS Query, it work
something like this: "SELECT ... WHERE branch = [Enter the branc
code]

 

[Dick Kusleika]

bchan

Parameters for PivotCache queries are not supported. You can trick it by
changing the SQL and putting in the ?, but there's no access to a Parameter
object so you can't change the prompt string. You can either roll your own
as amo suggested or create the QueryTable and PivotTable separately. The QT
can have a customize parameter and you can base the PT on that range.
Refreshing the PT doesn't automatically refresh the QT, which stinks.