LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2006, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


-----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?


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"

-----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

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 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?


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

Back to: Top of message | Previous page | Main SAS-L page