07-22-2013 09:53 AM
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 */
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?
07-22-2013 10:42 AM
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.