pass %let from excel (VBA)

[YoungMin]

is it possible to pass %let to an existing EGP from VBA?  here is an example of what I'm trying to do:

in VBA:

dim app

dim project

dim prjNM 'the SAS EG project

set app = createobject("SASEGObjectModel.Application.4.3")

prjNM = "c:\text.egp"

set project = app.open(prjNM, "")

'add code that allows you to pass %let passme=10 in the SAS code of prjNM

a sample code in prjNM

libname dat 'sasdata\yada';

proc sql (outobs=[pass the %let passme here; &passme]);

create table data as

select *

from dat.data;

quit;

thanks in advance!

[LinusH]

Why are you calling an EG project?

If you have a remote SAS server, it seems simpler to deploy the project as a Stored Process, and have %let be prompt instead.

If you have a local SAS server, export the project as a SAS program file, and the call it in the background. Then you can pass any parameters to SAS using the SYSPARM SAS command line option.

[YoungMin]

thanks for the response linush!

1. is there a reason not to use EG?  i have no preference; this is an existing EG i've inherited.  the data from the EG is copied into an excel dashboard.  i want to automate this so that the users who have access to the excel dashboard can refresh the data.  users who want this information, want it periodically and are not SAS people either. 

2. i haven't tried this before.  i'll can look into  it.