Wednesday, July 8, 2009

SAS: Running Excel Macro From SAS

options noxwait noxsync;
x '"C:\Program Files (x86)\Microsoft Office\Office12\excel.exe"
"C:\Documents and Settings\Sobhan\Desktop\book2.XLS"'; *Pls choose appropriate files 1.to enable excel, 2. open a specified excel file;

data _null_;
x=sleep(5); *for opening Excel it take some time;
run;

filename EXPORT DDE "EXCELSheet1!r1c1:r3c2" ;

data exp;
input name$ marks;
cards;
abc 46
xyz 28
bcd 60
;

data _null_;
set exp;
file EXPORT;
put name marks;
run;

filename savec DDE 'EXCELSYSTEM'; *relating between Excel and SAS;

DATA _NULL_;
FILE savec;
PUT '[run("macro1")]'; *Pls write a macro named macro1 in excel and put macro security in last option i.e.High;
PUT '[save()]';
PUT '[quit()]';
RUN;

3 comments:

  1. That's a good work Srikanth. Keep Posting such stuff which helps a lot.
    Thanks for this.

    ReplyDelete
  2. Nice.. This stuff will come in pretty handy! Get yourself a website soon... :)

    ReplyDelete
  3. Hi. Can you please elaborate on how to save the macro1 ?

    ReplyDelete