Can I set a multi-item text prompt via AMO and VBA?

ScottBass — Thu, 03 Oct 2013 02:10:18 GMT

Hi,

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:

Sub RefreshStoredProcessWithPrompts()

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"

stp.Refresh

End Sub

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.

Thanks,

Scott

Re: Can I set a multi-item text prompt via AMO and VBA?

jgutierrez2 — Thu, 20 Aug 2015 20:25:44 GMT

I have the same question and have been unable to find an answer. Did you ever figure this out?

topic Re: Can I set a multi-item text prompt via AMO and VBA? in Microsoft Integration with SAS

Can I set a multi-item text prompt via AMO and VBA?

Re: Can I set a multi-item text prompt via AMO and VBA?