'Prompting' for parameter

JuanSombrero

New member
Joined
May 12, 2016
Messages
4
Reaction score
0
Points
0
Hi all,
After heaving read Ken’s blog about paramaters, I now frequently use parameter tables and I think it’s a really helpful feature. The only thing that bothers me about it is that I have to ‘type’ my parameter in the parameter table. Is there a workaround so that, whenever I refresh a query using a parameter, PQ prompts a screen asking me to input the parameter?
I use it to select the correct version of a file (V01, V02,…) from a folder containing multiple version. When I load my query I would like a prompt screen asking me to input the version I want to see.
I assume VBA could help, but was wondering whether there is a non-VBA way? Like the prompt you get when you double click a function in the query pane for example.

Thx,
Juan
 
Hi Juan,

Not that I'm aware of. VBA does work pretty well for this though.

Code looks something like this:

Sub SelectLastWeekMasterFile()


NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Sheets("Produce Report").Range("A2") = NewFN

End If
End Sub
 
Back
Top