SAS put data to Excel via DDE

问题:

I want to write some data to Excel via DDE and have the following code:

option noxwait noxsync;
x call "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE";

%let delay=5;
data _null_;
    rc=sleep(&delay); 
run;

filename random dde 'excel|Tabelle1!r1c1:r100c3';
data _null_;
    set sashelp.class;
    file random;
    put name sex age;
run;

Excel opens successfully however the sheet remains empty. The log tells me that 19 records were written to the file RANDOM.

Any suggestions why the data is not written to my Excel sheet? Could it be connected with my language settings (German) in Excel?

回答1:

NOXSYNC is a mistake here.

Basically, what happens when I run this at least is it won't work if NOXSYNC is set, because it tries to write to the excel sheet before Excel is ready for it. You need XSYNC to have SAS wait for the X command to complete.

If XSYNC is something you can't deal with, then you will need to add a manual delay.

I'd also verify that you don't have Excel open before running this, as if you do it may be writing to a different Excel workbook than the one it's opening.

回答2:

Very mad story from my end: When I changed my language settings from German to English it works fine with the shown code.

Update: It also works with German language settings, however I have to translate all statements, e.g. the range written to:

English Excel

filename random dde 'excel|Sheet1!r1c1:r100c3';

German Excel use z(eile) and s(palte) instead of r(ow) and c(olumn)

filename random dde 'excel|Tabelle1!z1s1:z100s3';