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
... View more
