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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.