BookmarkSubscribeRSS Feed
CalebSturges
Fluorite | Level 6

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.

2 REPLIES 2
CalebSturges
Fluorite | Level 6
When line:
Set parm = parmList.item(0)
it doesn't get stuck, it says invalid index 0 (because the list is empty.)
CaseySmith
SAS Employee

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


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2973 views
  • 0 likes
  • 2 in conversation