Passing Prompt values to SAS Stored Procedure using SAS Excel ADD-in VBA

[VinitXen]

Hi.

I am trying to trigger (run) a SAS stored process from Excel using SAS Excel ADDIn using VBA code. I am able to trigger Stored Process and get output in Excel. But when I am passing prompt values via VBA it is giving a "Run-time Error 429" as mentioned in the attached file. Please find my code below -

Sub InsertStoredProcesswithInputStream()

Application.DisplayStatusBar = True
Application.StatusBar = "Please Wait. Connecting SAS EG..."

Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

Dim rngRange As Range
Set rngRange = Range(Cells(5, 1), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents

Dim a1 As Range
Set a1 = Sheet1.Range("A5")

Dim prompts As SASPrompts
Set prompts = New SASPrompts
prompts.Add "dpt_nm", "Living"

sas.InsertStoredProcess "/Shared Data/ACoE_Stored_Processes_Test/reorder_sheet_test2", a1, prompts

End Sub

without this -

Dim prompts As SASPrompts
Set prompts = New SASPrompts
prompts.Add "dpt_nm", "Living"

Code runs fine and gives desired output.

Also, when I opened VBA I couldn't find SAS ADD-in 7.1 for Microsoft Office under tools->references

So, I browsed and selected "SAS.OfficeAddin.tlb" from sas installation folder from program files. after that I got SAS ADD-in 7.1 in the tools->references

Please help me resolve this issue

[TimBeese]

I would suggest that you change the way that you create the SASPrompts object.  Try the following:

    Dim prompts As SASPrompts
    Set prompts = sas.CreateSASPromptsObject

Creating the SASPrompts this way allows the add-in to create it within our process and there are less problems.

Tim Beese