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?
Comments: To: "keybdftj@yahoo.fr" <keybdftj@YAHOO.FR>
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

http://support.sas.com/rnd/itech/doc9/dev_guide/dist-obj/winclnt/index.html

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.

--Jeff Wright
  ThotWave Technologies

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of keybdftj@yahoo.fr
Sent: Friday, April 07, 2006 9:51 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to access stored process with VBA or Excel API?

Hello,

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
requested parameters.

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
parameters:
PER_REF=M200201
SENS=DP

2°) We activated the SAS_OfficeAddin library (menu tools/references,
then open the SAS.OfficeAddin.dll file):

3°) The VB macro code was:

Sub AddIn()
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
Opt.Save
On Error Resume Next

'Here we try to refresh the worksheet called "testgraph2"

Add.Refresh Worksheets("testgraph2")

If Err.Number <> 0 Then
   Debug.Print Err.Number
   Debug.Print Err.Description
End If
End Sub