Multiple value selection from excel sheet for Multivlued prompt

[Dreamer]

Hi,

I have to generate a report for selected group ids from a list of group ids in the sheet.

GroupID

123

456

789

012

Currently I have developed a stored procedure with multiple value prompt for Group id, which I call from Excel using SAS office Add In to do this. But since its a big list and its not a good user experience to add them one by one manually in the prompt dialog box.

So is there a solution where user can select multiple ids from this Group Id list and prompt get those values automatically.

Btw I also tried using input stream "_webin_sasname". But challenge here is that user has to select header (Group id) also and items provided should be continuous to header cell. Basically complete row.

[Dreamer]

Hi,

I wrote following code to create a dataset from excel data which was supposed to be a prompt values.

This dataset is then input to respective Stored Process. This way I'm able to automate this process.

But problem is that it only works from my ID or other admin's id and not with other common SAS user's id.

Function GetInput() As Boolean

Dim sasObjectFactory As New SASObjectManager.ObjectFactory

Dim obSAS As SAS.Workspace

Dim ServerDef As New SASObjectManager.ServerDef

Dim sasObj As SASExcelAddIn

Dim SASCode As String

SASCode = GetDataStep()

If Trim(SASCode) <> "" Then

    Set sasObj = Application.COMAddIns.Item("sas.ExcelAddIn").Object

    ServerDef.MachineDNSName = sasObj.ActiveProfile.HostName

    ServerDef.Port = sasObj.ActiveProfile.Port

    Set sasObjectFactory = New SASObjectManager.ObjectFactory

    Set obSAS = sasObjectFactory.CreateObjectByServer(sasObj.ActiveProfile.Name, True, ServerDef, "", "")

    Set sasLanguageEvent = obSAS.LanguageService

    sasLanguageEvent.Submit (SASCode)

    GetInput = True

Else

    MsgBox "Please provide Mamber Ids for this report in Column A", vbInformation

    ActiveSheet.Range("A3").Select

    GetInput = False

End If

End Function

Above code gives following error when tried to execute from other user's id:

Any help or suggestion on this will be much appreciated!!