Date:         Mon, 10 Apr 2006 10:18:21 -0400
Reply-To:     Don Henderson <donaldjhenderson@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
Comments:     RFC822 error: <W> MESSAGE-ID field duplicated. Last occurrence
              was retained.
From:         Don Henderson <donaldjhenderson@HOTMAIL.COM>
Subject:      Re: How to access stored process with VBA or Excel API?
In-Reply-To:  <001701c65c9e$619cec20$0202fea9@alan>
Content-Type: text/plain; charset="iso-8859-1"

Another addition/correction to what Jeff stated.

The "logic that prompts for parameters" is not, strictly speaking, part of
the Add-In for MS Office (or any of the clients for that matter).

Each stored process can have metadata registered for its parameters in the
Metadata Server. That information is accessible to clients of stored
processed. So each client can get that information and decide whether and
how to provide the values (including prompting the user). So, for example in
the Portal, the SAS Stored Process Web App will either use the customer
supplied HTML page, or will build one on the fly based on the metadata. The
Excel Add-in will do something similar, and so on.

A more fundamental question is what does your VBA app do that Add-In for MS
Office does not do.

-don

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Alan
Churchill
Sent: Monday, April 10, 2006 8:58 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: How to access stored process with VBA or Excel API?

Jeff,

Small addition to what you stated.

Built into Base SAS is the Local Provider. This can be used if SAS is local
to the machine. IOM is needed if SAS is remote. There is also the Share
provider but I've never seen it used.

Alan Churchill
Savian "Bridging SAS and Microsoft Technologies"
www.savian.net

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jeff
Wright
Sent: Monday, April 10, 2006 5:49 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: How to access stored process with VBA or Excel API?

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