How to run a SAS program using VBA?

[riskassure]

Hi,

I am trying to write a program using VBA in Excel to run a SAS program (a .sas file). Does anyone know how this can be accomplished?

Thanks in advance!

~~CW~~

 

[mdieckman]

This can be done, but in my opinion it is much easier to use DDE commands to launch excel, call the excel macro within SAS do all you coding for the excel in the VBA code, have SAS quit excel after the VBA macro is done, and launch SAS using batch files.

You can use digital signatures in Excel so it doesn't prompt you if the macro is trusted.

I have code if you are interested, but do not have it handy at the moment.

 

[mdieckman]

Sorry for not replying sooner:

Code to open excel and call excel macros using DDE.

options noxwait noxsync; run;
x '"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE"';
                  

data _null_;
     x=sleep(5);
run;

FileName cmds dde 'excel|system'; Run;

DATA _NULL_;
     File cmds;
     Put '[OPEN("C:\excel.xls")]';
     X=SLEEP(5);
RUN;

Data _Null_;
     File cmds;
     Put '[RUN("RUN_REPORT")]';
Run;

Data _Null_;
     File cmds;
     Put '[RUN("SAVE_REPORT")]';
Run;

Data _Null_;
     file cmds;
     put '[QUIT()]';
run;