Inserting Output of SAS Stored Process Into Excel Sheet Using VBA

RowanB1986 — Mon, 09 Dec 2019 15:43:57 GMT

I am trying to get the output of the following SAS stored procedure to appear in an Excel sheet via a VBA macro.

proc sql;
 CREATE TABLE RV_LIST AS
 SELECT F_RS_DESC FROM FUTURA.to_rv_Sets WHERE F_RS_PROV_ID = 23 And UPPER(F_RS_DESC) LIKE '%LIVE RV%' ORDER BY F_RS_DATE DESC;
quit;
proc print DATA=RV_LIST;
run;

Here is the VBA code I'm using:

Sub UpdateDataValidation()
Dim DV As Worksheet
Dim SAS As SASExcelAddIn
Dim prompt As SASPrompts
Dim Output As Range
Dim data As SASDataView

Set DV = ThisWorkbook.Worksheets("Data Validation")
Set SAS = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

SAS.InsertStoredProcess "/User Folders/rbarua/My Folder/RVListUpdate", DV.Range("A4")


End Sub

The VBA macro runs successfully and the output of the SAS stored process appears in the "SAS Add-In For Microsoft Office" pane on the right.

However, I want the output to appear in cell A4 of the "Data Validation" worksheet. Can anyone spot why this is not happening?

Any help is much appreciated.

Re: Inserting Output of SAS Stored Process Into Excel Sheet Using VBA

JosvanderVelden — Wed, 11 Dec 2019 12:22:11 GMT

Have you seen the examples in the documentation?

https://documentation.sas.com/?docsetId=amodg&docsetTarget=p1u415y2bdwtpsn1pt9hiyc27j45.htm&docsetVersion=8.0&locale=en

topic Re: Inserting Output of SAS Stored Process Into Excel Sheet Using VBA in SAS Programming

Inserting Output of SAS Stored Process Into Excel Sheet Using VBA

Re: Inserting Output of SAS Stored Process Into Excel Sheet Using VBA