I would like to ask about DDE with Excel. I tried SAS 9.3 with Excel97-2003 (.xls) as well as Excel2013(.xlsx).
The statement
put '[select("C2")]';
always leads to the Error:
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.
Aborted during the EXECUTION phase.
This is surprising because the statement before:
put '[freeze.panes(true,0,1)]';
did work, which should mean that Excel was ready.
Can someone give a tip how the select statement can be made working.
Here is the example code where the error occurred:
data example;
a=1; b=2; c=3; output;
a=4; b=5; c=6; output;
a=7; b=8; c=9; output;
a=10; b=11; c=12; output;
run;
PROC EXPORT DATA=example OUTFILE="M:\SAS_test\example.xlsx"
DBMS=excel REPLACE;
RUN;
options noxsync noxwait;
filename DDEcmds dde "excel|system";
x '"C:\Program Files\Microsoft Office\Office15\EXCEL.exe"';
data _null_;
dummy = sleep(3);
run;
data _null_;
file DDEcmds;
put '[open("M:\SAS_test\example.xlsx")]';
dummy = sleep(3);
run;
data _null_;
file DDEcmds;
put '[freeze.panes(true,0,1)]';
run;
data _null_;
file DDEcmds;
put '[select("C2")]';
put '[insert]';
run;
data _null_;
file DDEcmds;
put '[save]';
put '[error(false)]';
put '[quit()]';
run;
I suppose that you have to reassigne filename before using select.
From
filename DDEcmds dde "excel|system";
To
filename DDEcmds dde "excel|sheet_name_!r3c1:r29C256" notab;
Notice, that you should type real sheet name in the filename statement.
Oleg.
I suppose that you have to reassigne filename before using select.
From
filename DDEcmds dde "excel|system";
To
filename DDEcmds dde "excel|sheet_name_!r3c1:r29C256" notab;
Notice, that you should type real sheet name in the filename statement.
Oleg.
Thank you very much, Oleg!
This was the right hint.
Following example works perfectly now:
data example;
a=1; b=2; c=3; output;
a=4; b=5; c=6; output;
a=7; b=8; c=9; output;
a=10; b=11; c=12; output;
run;
PROC EXPORT DATA=example OUTFILE="M:\SAS_test\example.xlsx"
DBMS=excel REPLACE;
RUN;
options noxsync noxwait;
filename DDEcmds dde "excel|system";
x '"C:\Program Files\Microsoft Office\Office15\EXCEL.exe"';
data _null_;
dummy = sleep(3);
run;
data _null_;
file DDEcmds;
put '[open("M:\SAS_test\example.xlsx")]';
dummy = sleep(3);
run;
data _null_;
file DDEcmds;
put '[freeze.panes(true,0,1)]';
run;
filename DDEcmds dde "excel|EXAMPLE![M:\SAS_test\example.xlsx]r1c1:r1c1" notab;
data _null_;
file DDEcmds;
put '[select("C2")]';
run;
filename DDEcmds dde "excel|system";
data _null_;
file DDEcmds;
put '[insert(4)]';
put '[save]';
put '[error(false)]';
put '[quit()]';
run;
Best regards,
Ulrich
You should've been able to achieve similar results by first selecting a targetted sheet between open and select. I forget the syntax though but I had used the "excel|system" incomplete DDE triplet for an example on how to insert a jpg image in a targetted worksheet cell a few months ago.
Here was the code I used to achieve this:
options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office12\EXCEL.exe"';
data _null_;
z=sleep(8); /* 5seconds sleep, needs to be long enough for excel to open */
run;
filename DDEcmds dde "Excel|SYSTEM";
data _null_;
file ddecmds;
put '[open("C:\testexcelfile.xlsx"]';
z=sleep(3);
put '[workbook.activate("Sheet1")]';
put '[select("R4C12")]';
put '[insert.picture("C:\users\public\pictures\sample pictures\forest.jpg")]';
put '[save()]';
put '[error(false)]';
z=sleep(5); /* give it enough time to save, if it's a huge excel document, increase this value */
put '[quit()]';
run;
*options xwait xsync;
Anyway point being, you could've stuck to
filename DDEcmds dde "excel|system";
all along for so long as you activated a worksheet prior to attempting to select cells as with the workbook.activate("sheet_name") command above.
Vince
Hello Oleg and Vince,
as I wrote before, at a first glance, Oleg's suggest did work because there was no error message no more.
However, there was also no really selection of cells.
Vince's suggest also didn't solve this.
Now I found a way by using key strokes, for instance:
data _null_;
file DDEcmds;
put '[workbook.activate("EXAMPLE")]';
put '[app.activate("example - Excel")]';
put '[send.keys("^{home}")]';
put '[send.keys("{right}")]';
put '[send.keys("+{right}")]';
put '[send.keys("+{right}")]';
put '[send.keys("+{down}")]';
put '[font.properties(,"Bold",,,,,,,,3)]';
run;
This worked under most circumstances.
It could certainly be developped as a select cell macro.
Thanks again for your suggestions, they helped on this topic anyway,
Ulrich
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!