Your SAS programs, embedded in web apps and elsewhere

passing prompt values to stored process from SAS Excel ADD-in VBA

Reply
Highlighted
New Contributor
Posts: 4

passing prompt values to stored process from SAS Excel ADD-in VBA

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


SAS Excel ADDin Run-time error.jpg
Ask a Question
Discussion stats
  • 0 replies
  • 276 views
  • 1 like
  • 1 in conversation