2

Good Morning Everyone,

I am trying to create a form in Excel which passes contents of X number of cells (in the POC below I am only utilising a one) as parameters within a SAS Stored Process using the Add-in for Microsoft Office. I don't have any issues performing this task on a single query, however when I change the value in A1 and submit the code again I get the following popup box, because the results of the previous query already appear within cell A10:

Query

Sub InsertStoredProcessWithPrompts()

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


   'Delete and entries in the existing log

  sas.ClearLog

   'Setting Options

  sas.options.ResetAll
  sas.options.AutoInsertResultsIntoDocument = True
  sas.options.PromptForParametersOnRefreshMultiple = False
  sas.options.ShowStatusWindow = False

   'Specify the Cell Used For Parameter Input

  Dim age As Range
  Set age = Sheet1.Range("A1")

   'Capture The Prompts To Be Used As Parameters Within The Stored Process

  Dim prompts As SASPrompts
  Set prompts = sas.CreateSASPromptsObject

  prompts.Add "AGE", age

  Dim stp As SASStoredProcess
  Set stp = sas.InsertStoredProcess("/User Folders/scmitchell/My Folder/Test Streams", Sheet1.Range("A10"), prompts)


End Sub

Second Execution Result

enter image description here

As a result I added an If statement to identify whether the object associated with the Stored Process already exists. If it does then create the stored process, if not then modify the parameters of the stored process to include the newly defined value in the AGE prompt.

Query

Sub InsertStoredProcessWithPrompts()

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

If stp Is Nothing Then

   'Delete and entries in the existing log

   sas.ClearLog

   'Setting Options

   sas.options.ResetAll
   sas.options.AutoInsertResultsIntoDocument = True
   sas.options.PromptForParametersOnRefreshMultiple = False
   sas.options.ShowStatusWindow = False

   'Specify the Cell Used For Parameter Input

   Dim age As Range
   Set age = Sheet1.Range("A1")

   'Capture The Prompts To Be Used As Parameters Within The Stored Process

   Dim prompts As SASPrompts
   Set prompts = sas.CreateSASPromptsObject

   prompts.Add "AGE", age

  Dim stp As SASStoredProcess
  Set stp = sas.InsertStoredProcess("/User Folders/scmitchell/My Folder/Test Streams", Sheet1.Range("A10"), prompts)

  Else
  Set stp = stp.Modify("Test Streams")

  End If

End Sub

First or Subsequent Execution Result

enter image description here

The following is highlighted as the issue:

Dim stp As SASStoredProcess

There is a possibility that I am headed in entirely the wrong direction, so please feel free to offer alternative solutions, however the above seems reasonably sound thus far. I pulled the 'If object' exists code from a StackOverflow post (VBA check if object is set), but maybe I am making a rudimentary mistake as I rarely use VBA.

Any help would be greatly appreciated.

Regards.

Scott

user2337871
  • 450
  • 1
  • 5
  • 14
  • Are you looking to overwrite the value in cell A10 ? You could try clearing the cell prior to calling the STP -- `Range("A10").Clear` – Richard Jun 25 '19 at 19:39
  • Hi @Richard. Thank you for your reply. I don't actually want to replace the query, I just want to refresh the parameters being passed to the query using Set stp = stp.Modify("Test Streams") which should then return the results from the stored process. Does that make sense? – user2337871 Jun 26 '19 at 05:52
  • 1
    Based on the Add-In dialog about $A$10, I presumed the second argument to `sas.InsertStoredProcess` is where the computed output of the STP is placed. Because there is an earlier result there, the Add-In complained. So either empty the result location first, or (if available) use an additional `sas.InsertStoredProcess` parameter to say it is ok to overwrite existing results. – Richard Jun 26 '19 at 10:32
  • Thanks Richard. I ended up doing just that in the end. I just find it frustrating that you can't submit new parameters without deleting the existing STP first. I would think the Modify method would allow you to do this. Thank you for your help. – user2337871 Jun 27 '19 at 00:38

0 Answers0