topic Re: Pass a Parameter from Excel VBA to a SAS EG project. in SAS Enterprise Guide

Pass a Parameter from Excel VBA to a SAS EG project.

CalebSturges — Mon, 27 Mar 2017 22:46:49 GMT

I'm having trouble passing a parameter through Excel VBA to my SAS Enterprise Guide project. My problem is basically the same as this user's problem, but I tried all the proposed solutions and none worked (it was in 2009 and the technology is all different. I'm using Enterprise guide 5.1 and Excel VBA 2010 on Windows 7. He was using enterprise guide 4 and visual basic scripts.)

Option Explicit
Public ApplicationObj 'Application
Public Proj 'Project object
Public sasProgram 'Code object (SAS program)
Public log1 As String
Public parmList
Public parm

Sub RunSAS()
On Error GoTo ErrHandler
Set ApplicationObj = CreateObject("SASEGObjectModel.Application.5.1")
Set Proj = ApplicationObj.Open("C:\Users\sturgec\Documents\PD0TO11completeupdRIDErecalib.egp", "")
Set parmList = Proj.Parameters
MsgBox "Project has " & parmList.Count & " parameters."
Set parm = parmList.item(0)
MsgBox parm.Name & " parameter has default value of " & parm.DefaultValue
parm.Value = "M"

MsgBox parm.Name & " parameter has been set to value of " & parm.Value
Proj.Run

Proj.SaveAs "C:\Users\sturgec\Documents\NewPD0TO11completeupdRIDErecalib.egp"
Proj.Close
ApplicationObj.Quit
End Sub

The main solution, as shown above was to set some default value "Paramters", but when I tried to access it says: "Project has 0 parameters" and then in the line:

Set parm = parmList.item(0)

it just gets stuck and run's forever. Also even if it did somehow work I'm not sure how I would find out in my SAS program where to call that first variable.

To clarify I want to:

1. Open a SAS enterprise .egp file from Excel VBA (this part works)

2. Pass it a paramter

3. Run/exacute it (this also works)

4. Grab the csv file it prints out off the SAS server

I'm also open to using "SYSPARM" but I havn't been able to get that to work.

Re: Pass a Parameter from Excel VBA to a SAS EG project.

CalebSturges — Mon, 27 Mar 2017 23:26:32 GMT

When line:
Set parm = parmList.item(0)
it doesn't get stuck, it says invalid index 0 (because the list is empty.)

Re: Pass a Parameter from Excel VBA to a SAS EG project.

CaseySmith — Tue, 28 Mar 2017 02:55:50 GMT

Hi @CalebSturges,

It looks like you are really close... I attempted using EG 5.1 (32-bit w/ HF21) and Excel 2013 (32-bit) and your code works for me with one change...

I commented out this line, since retrieving the DefaultValue property was causing an error, presumably since I didn't specify a default value for the prompt I defined:

'Retrieving parm.DefaultValue causes error because no default value was set
'on the prompt I defined
'MsgBox parm.Name & " parameter has default value of " & parm.DefaultValue
MsgBox parm.Name & " parameter"

In EG's View->Prompt Manager, make sure you have at least one prompt defined. For example, I accepted the defaults, which created a text prompt named "Prompt_1". Then, I created a program in EG with this code:

%put "&Prompt_1";
proc print data=sashelp.class;
where sex eq "&Prompt_1";
run;

Then, make sure you associate the prompt with the program by clicking Properties on the program, Prompts page, then add the prompt you want to use with that program. Run the program/project in EG to make sure you get prompted and runs as expected. If so, delete the SAS results output (so you can easily tell if the project was re-run... results recreated), save the project and close EG.

Now when I run the VBA code, the value I specify ("M" or "F") is properly passed into the EG project and used in the code for me.

Casey