DDE SAS to run VBA code

4
votes

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;
2
vbaexcelsasdde
If the VBA code works and you want to execute it automatically then place the code in the Workbook_Open :) - Siddharth Rout
I've tried that now, but excel runs the macro before the data is exported from SAS to Excel. I've tried putting a delay in the VBA macro, but that didn't help either. How to I get the VBA macro to wait on executing the command until the data from SAS is in the spreadsheet? - NHansen
A delay won't help as the workbook_open will fire first. I am not too sure but how is the data exactly getting updated in the Excel? - Siddharth Rout
Well Excel opens up empty and the four variables from the "rap._konc_selskab" data set are put in each column and that's basically it. After the data has been pasted in, I'd run to run the macro automatically. - NHansen
I don't understand the SAS code above :( so what I am going to suggest might seem absurd... Does your SAS code have a syntax where it can open an excel file and run a macro? If yes, then what you could do is populate the excel file and then via your SAS code run the Excel macro? - Siddharth Rout

2 Answers

1
votes

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 ;
0
votes

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.