SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Super Contributor
Posts: 392

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


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"


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.



Senior User
Posts: 1

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

Posted in reply to ScottBass

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

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation