CODEWITHSUNDEEP
vbaexcelsasdde
NHansen
NHansen

Reputation: 101

DDE SAS to run VBA code

I can't get Excel 2010 to execute a macro from SAS. I'm exporting data from some SAS tables to Excel, which works fine, and when I run the VBA macro manually it also does what it should, but it just won't execute automatically. The Excel file is called "FIH.xls" and the macro is called "Opryd" as Module1. The SAS Log doesn't come up with any errors, neither does Excel. I've allowed all macros to run in the Excel settings. Still it doesn't execute. 

options noxwait noxsync;
x '"C:\FIH.xls"';
/* Putting SAS in sleep mode to give Excel the necessary time to open the file */
data _null_; x=sleep(5);
run;

FILENAME excel DDE 'EXCEL|Grp!r5c8:r7c20' notab;
    DATA _NULL_;
   SET gem.rap_konc_selskab;
    FILE excel;
    PUT '09'x selskab_rap '09'x gr_vis_start '09'x man_amt '09'x '09'x '09'x rest_2 '09'x ;
    RUN;
data _null_;
file excel;
put '[run ("FIH.xls!Opryd")]';
run;

Upvotes: 4

Views: 4400

Answers (2)

Reef
Reef

Reputation: 33

It seems this issue has been resolved long ago but here's a tip for the benefit of those who still google for a solution:

Assume file is open.

filename xl dde 'excel|system';
data _null_;
  file xl;
  put %unquote(%str(%'[run("FIH.xlsm!Opryd")]%'));
run;

Note the xlsm extension.

Upvotes: 0

Chris J
Chris J

Reputation: 7769

The problem is that you're putting the run(...) to the workbook itself and not the Excel application.

You need a 2nd fileref which you use for your run(...) command :

filename cmdexcel dde 'excel|system' ;
data _null_;
  file cmdexcel;
  put '[run("FIH.xls!Opryd")]'; /* no space between run and ( */
run;
filename cmdexcel clear ;

Upvotes: 1

Related Questions