topic Re: DDE session not ready when using the Excel select function in Microsoft Integration with SAS

DDE session not ready when using the Excel select function

Ulrich — Tue, 01 Oct 2013 11:38:09 GMT

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;

Re: DDE session not ready when using the Excel select function

Oleg_L — Tue, 01 Oct 2013 13:39:44 GMT

I suppose that you have to reassigne filename before using select.

From

filename DDEcmds dde "excel|system";

filename DDEcmds dde "excel|sheet_name_!r3c1:r29C256" notab;

Notice, that you should type real sheet name in the filename statement.

Oleg.

Re: DDE session not ready when using the Excel select function

Ulrich — Tue, 01 Oct 2013 14:49:10 GMT

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

Re: DDE session not ready when using the Excel select function

Vince28_Statcan — Tue, 01 Oct 2013 15:12:44 GMT

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

Re: DDE session not ready when using the Excel select function

Ulrich — Fri, 11 Oct 2013 15:14:19 GMT

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("+{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