Use excel query parameters to also be column header

printmedia
printmedia used Ask the Experts™
on
Hi all.

I have an excel 2007 file whose data source is a sql table. When the user opens the file it asks them for 2 parameters how can have the 2 parameters the user enters appear as a column header using vba?

Thank you in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
You could use a function like this:
Function GetParam(rng As Range, ParamNum As Long)
    GetParam = rng.ListObject.QueryTable.Parameters(ParamNum).Value
End Function

Open in new window


used in a cell:
=GetParam(A1,1)

to get parameter 1 from the query in A1. You could also simply use two cells as the input values for the query so that altering them refreshes the query.

Author

Commented:
Thanks for the reply. Currently, when the file is opened by the user it prompts the user for the 2 parameters.

So what you're suggesting is that I put the parameters in a cell?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yes. If you right-click the table, choose  Table-Parameters... you should be able to choose cells as the input ranges and also specify that the table should update automatically if their values are changed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial