DDE session not ready when using the Excel select function

[Ulrich]

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;

[Oleg_L · Accepted solution]

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.

[Ulrich]

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

[Vince28_Statcan]

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

[Ulrich]

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