Date: Mon, 10 Apr 2006 07:49:13 -0400
Reply-To: Jeff Wright <jwright@THOTWAVE.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jeff Wright <jwright@THOTWAVE.COM>
Subject: Re: How to access stored process with VBA or Excel API?
Content-Type: text/plain; charset=iso-8859-1
The API for invoking SAS, such as SAS stored processes, is called the Integrated Object Model (IOM). This is part of the "Integration Technologies" family of functions.
The documentation for developing Windows clients to the IOM API is at
However, the IOM API does not have any user interface built into it. It is capable of calling a stored process if you already have the parameters.
The logic that prompts for stored process parameters is part of the Add-in for MS Office, not part of the stored process itself. I don't know if it's possible to script the Add-in for MS Office, so that your VBA code can initiate the dialog that prompts for parameters.
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of firstname.lastname@example.org
Sent: Friday, April 07, 2006 9:51 AM
Subject: How to access stored process with VBA or Excel API?
We are trying to create a VBA application which is aimed to call stored
processes. Those stored processes have already been referenced on our
metadata server. They have been tested by SAS Enterprise Guide 3.02 and
AddIns for MS Office from the Service Pack 3. They are called with
We have found some ways to do it, which are described at the end of the
document. The problem is that it is impossible to change the values of
the parameters when we call the stored process by VBA. No prompt
appears. When we launch, it seems to enter values which had been
entered as we called the stored process by Excel addin.
We proceeded as following:
1°) We called the stored process by Excel addin, and entered its 2
2°) We activated the SAS_OfficeAddin library (menu tools/references,
then open the SAS.OfficeAddin.dll file):
3°) The VB macro code was:
Dim Add As SASAddIn
Dim Opt As OptionsGeneral
Set Add = Application.COMAddIns.Item("SAS.OfficeAddin.Connect").Object
Set Opt = Add.Options
'you can see below the code we used to reference the parameters
'unfortunately they are never called by the application
Opt.PromptForParametersOnRefresh = True
Opt.PromptForParametersOnRefreshMultiple = True
On Error Resume Next
'Here we try to refresh the worksheet called "testgraph2"
If Err.Number <> 0 Then