topic Re: Can I check to see if a DDE session is ready before trying to use it? in SAS Programming

Can I check to see if a DDE session is ready before trying to use it?

PaigeMiller — Mon, 22 Jul 2013 13:53:18 GMT

In the documentation for DDE, there is an example that allows you to invoke commands in Excel, using a SAS data step.

The example goes like this:

filename cmds dde 'excel|system';

/* These PUT statements are */

/* executing Excel macro commands */

data _null_;

file cmds;

put '[SELECT("R1C1:R20C3")]';

put '[SORT(1,"R1C1",1)]';

put '[SAVE()]';

put '[QUIT()]';

run;

When I try to do this, sometimes I get the message that the DDE session is not ready, and so there is an ERROR in the SAS Log, just after data _null_.

ERROR: DDE session not ready.

FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase.

I believe this happens because either I or someone else has opened the Excel file and not closed it before the SAS program executes. Is there a way that the SAS program can check to see if the DDE session is ready?

Re: Can I check to see if a DDE session is ready before trying to use it?

Vince28_Statcan — Mon, 22 Jul 2013 14:42:35 GMT

Last I read about DDE, it's actually an issue with that the DDE software has to be opened first hence why in most examples you will see a data _null_; z=sleep(3);run; step in most DDE examples online to force SAS to wait 3 seconds (or more) to let excel open prior to sending the first few commands.

If you already had such a sleep in your program and still were getting the error from another user having the file open, I'm sorry I can't be of much help beyond that. I can't think of an easy way to verify if an excel file can be open with write priviledges in SAS. Maybe check in excel-VBA documentation for a command to do so and instead of opening the workbook/sheet directly in your DDE statement, simply open excel.exe and use the open() excel command in your series of put statements to open the appropriate workbook/worksheet after using excel commands to verify that it's availible for writing.

Vincent