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?
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.