How to type SAS prompt value in Excel to run store

问题:

My experience with VBA is very limited. The following VBA code runs a SAS stored process and places the results in an Excel spreadsheet. The stored process prompt "EUID" is set to return the value of 5555. How do I revise the code so that the value of the prompt is pulled from a cell in the spreadsheet (e.g. cell B1), rather than being defined in the code? I want the end user to be able to type the EUID value in a cell, press a button to run the macro, and return the data set. Thank you for your help.

Sub InsertStoredProcessWithPrompts()
Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim prompts As SASPrompts
Set prompts = New SASPrompts
prompts.Add "EUID", "5555"
Dim a1 As Range
Set a1 = Sheet5.Range("A1")
sas.InsertStoredProcess "/User Folders/Stored Process 1", c1, prompts
End Sub

回答1:

You could try this:

Sub InsertStoredProcessWithPrompts()
Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim prompts As SASPrompts
Set prompts = New SASPrompts
prompts.Add "EUID", activesheet.cells(1,2).value 'This is cell B1
Dim a1 As Range
Set a1 = Sheet5.Range("A1")
sas.InsertStoredProcess "/User Folders/Stored Process 1", c1, prompts
End Sub