launch StoredProcess from VBA

[Greg]

we are trying to use sas Stored Processes from VBA with IOM interface:
It seems to be ok but the password stored in metadata server is propagated and we don't want to store password in SAS Metadata.

Here is an example :
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim spManager As New SASStoredProcessService.Manager
Dim spInstance As SASStoredProcessService.Instance
Dim spExecution As SASStoredProcessService.Execution
Dim spReader As SASStoredProcessService.StreamReader
obObjectFactory.SetMetadataFile "C:\Test\VBA\oms_serverinfo.xml", "", False

....and so on

3 questions :
-where is the complete documentation ?
-is IOM the best way to execute STP from VBA ?
-If yes, it is possible to force IOM objets to use the password write in the configuration file ? For the moment this password is used only for connection to metadata server. After that,the wrong password stored in metadata is propagated....

BR

Greg

[Vince_SAS]

The experimental COM API is used internally by SAS applications -- you can use it at your own risk. In SAS 9.2 we plan to formally release a .NET API that will be documented and supported.

One thing you can do with VBA is try the techniques described in this paper:

Techniques for SAS-Enabling Microsoft Office in a Cross-Platform Environment
http://www2.sas.com/proceedings/sugi27/p174-27.pdf

Some of the information is dated, but you may find the section on VBA helpful. The paper uses SAS/IntrNet as an example, but it can be extended to SAS Stored Processes because they are basically "The Son of SAS/IntrNet"(tm).

For example, this Excel macro, adapted from the paper (only changed the URL), runs the "Hello World" stored process sample and inserts the output into the current worksheet.

Sub insertHTML()

Dim currentCell As Range

Dim tempWorkbook As Workbook

Dim tempRange As Range

Set currentCell = Application.Workbooks(ActiveWorkbook.Name).ActiveSheet.Application.ActiveCell

Set tempWorkbook = Workbooks.Open("http://your-server:your-port/SASStoredProcess/do?_PROGRAM=/Samples/Stored Processes/Sample: Hello World")

Set tempRange = tempWorkbook.ActiveSheet.UsedRange

tempRange.Copy currentCell

tempWorkbook.Close False

End Sub

You will get prompted for a username and password in order to run the stored process. It look like Excel caches this information, so you should only have to enter it for the first stored process that you execute in a given workbook.

Documentation for the SAS Stored Process Web Application is at:
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/stpwebapp.html

Excel Web Queries, also discussed in the paper, are something else you should probably evaluate.

Vince
SAS R&D