Macro for Parameter Query
 

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]

--
bcha
-----------------------------------------------------------------------
bchan's Profile: http://www.excelforum.com/member.php...nfo&userid=827
View this thread: http://www.excelforum.com/showthread.php?threadid=27008

Macro for Parameter Query
 

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"bchan" wrote in message
...

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


--
bchan
------------------------------------------------------------------------
bchan's Profile:
http://www.excelforum.com/member.php...fo&userid=8274
View this thread: http://www.excelforum.com/showthread...hreadid=270081