4

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;
NHansen
  • 101
  • 2
  • 8
  • If the VBA code works and you want to execute it automatically then place the code in the `Workbook_Open` :) – Siddharth Rout Sep 23 '13 at 07:20
  • 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 Sep 23 '13 at 08:03
  • 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 Sep 23 '13 at 08:44
  • 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 Sep 23 '13 at 08:49
  • 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 Sep 23 '13 at 09:04
  • Well yes and no, the last "put '[run ("FIH.xls!Opryd")]';" line should run the macro in Excel, which is opened earlier in the code, but it doesn't work for some reason. I've read that it should work but it doesn't. I'm not sure I understand your suggestion though, the problem is that the macro won't execute. – NHansen Sep 23 '13 at 10:30

2 Answers2

1

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 ;
Chris J
  • 7,549
  • 2
  • 25
  • 25
  • When I do that I get an error in the SAS Log: "ERROR: DDE session not ready. FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase." – NHansen Sep 23 '13 at 10:48
  • I assume the workbook is still open? Try wrapping square brackets around the workbook name... `put '[run ("[FIH.xls]!Opryd")]';` – Chris J Sep 23 '13 at 10:56
  • 1
    Or potentially without the ! `put '[run ("[FIH.xls]Opryd")]';` – Chris J Sep 23 '13 at 11:03
  • That didn't help either. The macro doesn't run and I still get the error in the log. I can't believe how stubborn Excel is being – NHansen Sep 23 '13 at 11:04
  • I've tried a number of different combinations of the PUT statement, but none of them seems to get the macro started: Put '[run("macro1!r1c1")]'; Put '[run("module1!r1c1")]'; Put '[run("opryd!r1c1")]'; Put '[run("opryd!)]'; Put '[run("opryd)]'; put '[run ("[FIH.xls]Macro!Opryd")]'; put '[run ("FIH.xls!Opryd")]'; – NHansen Sep 23 '13 at 12:03
  • 1
    Made it work with: filename excel dde 'excel|system'; Data _NULL_; File excel; Put '[run("opryd")]'; Run;. not using the "filename cmdexcel clear;" in the end. – NHansen Sep 23 '13 at 12:51
  • After reviewing your code and testing myself, the problem all along has been the space between `run` and `(`. I've amended my answer, please could you accept it. – Chris J Sep 27 '13 at 09:05
0

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.

Reef
  • 35
  • 5