10-02-2013 10:10 PM
I have a stored process containing a multi-item text prompt, i.e. Text, User Enters Values, Multiple Values. In the prompt manager GUI, I enter the text, press Add, and all current values display in the listbox.
I would like the end user to be able to specify this list in an Excel range (column). I would then use VBA to retrieve those values, set the value(s) of the prompt, then refresh the stored process.
I have this simple subroutine, modified from the AMO help, SASStoredProcess.SetParameter Method topic:
Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim list As SASStoredProcesses
Set list = sas.GetStoredProcesses(Sheet1)
Dim stp As SASStoredProcess
Set stp = list.Item(1)
stp.SetParameter "where_value_1", "foo"
stp.SetParameter "where_value_1", "bar"
stp.SetParameter "where_value_1", "blah"
Ok, I didn't really expect this to work; as I expected, the last setting ("blah") wins.
Can I set a multi-item text prompt value using AMO and VBA? My stored process uses the resultant macro variables set by the prompt manager to build an IN ( ) list. It will be much faster for them to just paste the desired list into Excel and run the macro than to Modify the stored process and key in the new list one item at a time.