Error: DDE session not ready. ERROR: Critical YP Subsystem error opening message library.

[SASuserlot]

Hi there, 

I am trying to extract the title and footnotes from word file and copy into the excel. I found the an article and macro. I tried to execute, I encountering the errors in the log that I am not at all familiar with especially at excel file creation.  I greatly appreciate your help in figuring out the issues in the macro. I am attaching the macro and the reference article link and log error. 

article reference:https://www.lexjansen.com/pharmasug/2011/CC/PharmaSUG-2011-CC11.pdf

%Macro Read_Title_Footnote(in=, out=raw, location=, section=,page=);
options noxwait noxsync symbolgen mprint mlogic ;
%let rc=%sysfunc(system(start winword));
data _null_;
 x=sleep(5);
run;
filename word DDE 'Winword|System';
data _null_;
 file word;
 put '[FileOpen.Name = "' "&in" '"]';
 put '[EditGoto.Destination="' "&page" '"]';
 put "[View&section]";
 put '[EditSelectAll]';
 put '[EditCopy]';
 put '[FileClose]';
 put '[FileExit]';
run;
data _null_;
 x=sleep(5);
run;
%let rc=%sysfunc(system(start excel));
data _null_;
 x=sleep(10);
run;
filename excel DDE 'Excel|System';
data _null_;
 file excel;
 put '[Error(false)]';
 put '[Paste]';
 put '[SAVE.AS("' "&location\content.xls" '")]';
 put '[Close]';
 put '[Quit]';
run;
data _null_;
 x=sleep(7);
run;
libname xls excel "&location\content.xls" header=no mixed=yes;

data &out._&section;
 set xls."sheet1$"n;
 page=&page;
 if missing(f1) then delete;
run;
libname _all_ clear;
FILENAME db "&location\Content.xls";
%let rc=%sysfunc(fdelete(db));
FILENAME db clear;
%Mend Read_Title_Footnote; 

%Read_Title_Footnote(in= %str(D:\OneDrive - MSOPROD\xx\Desktop\New folder1\lab-table.rtf), 
					out=raw, 
					location= %str(D:\OneDrive - MSOPROD\xx\Desktop\New folder1), 
					section=header ,page=1);

Log:

Note: you see  boxes in log may be  because of copy paste issues

1344 %Macro Read_Title_Footnote(in=, out=raw, location=, section=,page=);
1345 options noxwait noxsync symbolgen mprint mlogic ;
1346 %let rc=%sysfunc(system(start winword));
1347 data _null_;
1348 x=sleep(5);
1349 run;
1350 filename word DDE 'Winword|System';
1351 data _null_;
1352 file word;
1353 put '[FileOpen.Name = "' "&in" '"]';
1354 put '[EditGoto.Destination="' "&page" '"]';
1355 put "[View&section]";
1356 put '[EditSelectAll]';
1357 put '[EditCopy]';
1358 put '[FileClose]';
1359 put '[FileExit]';
1360 run;
1361 data _null_;
1362 x=sleep(5);
1363 run;
1364 %let rc=%sysfunc(system(start excel));
1365 data _null_;
1366 x=sleep(10);
1367 run;
1368 filename excel DDE 'Excel|System';
1369 data _null_;
1370 file excel;
1371 put '[Error(false)]';
1372 put '[Paste]';
1373 put '[SAVE.AS("' "&location\content.xls" '")]';
1374 put '[Close]';
1375 put '[Quit]';
1376 run;
1377 data _null_;
1378 x=sleep(7);
1379 run;
1380 libname xls excel "&location\content.xls" header=no mixed=yes;
1381
1382 data &out._&section;
1383 set xls."sheet1$"n;
1384 page=&page;
1385 if missing(f1) then delete;
1386 run;
1387 libname _all_ clear;
1388 FILENAME db "&location\Content.xls";
1389 %let rc=%sysfunc(fdelete(db));
1390 FILENAME db clear;
1391 %Mend Read_Title_Footnote;
1392
1393 %Read_Title_Footnote(in= %str(D:\OneDrive - MSOPROD\xx\Desktop\New folder1\lab-table.rtf),
MLOGIC(READ_TITLE_FOOTNOTE): Beginning execution.
1394 out=raw,
1395 location= %str(D:\OneDrive - MSOPROD\xx\Desktop\New folder1),
1396 section=header ,page=1);
MLOGIC(READ_TITLE_FOOTNOTE): Parameter IN has value
D:\OneDrive  MSOPROD\xx\Desktop\New folder1\lab-table.rtf
MLOGIC(READ_TITLE_FOOTNOTE): Parameter OUT has value raw
MLOGIC(READ_TITLE_FOOTNOTE): Parameter LOCATION has value
D:\OneDrive  MSOPROD\xx\Desktop\New folder1
MLOGIC(READ_TITLE_FOOTNOTE): Parameter SECTION has value header
MLOGIC(READ_TITLE_FOOTNOTE): Parameter PAGE has value 1
MPRINT(READ_TITLE_FOOTNOTE): options noxwait noxsync symbolgen mprint mlogic ;
MLOGIC(READ_TITLE_FOOTNOTE): %LET (variable name is RC)
MPRINT(READ_TITLE_FOOTNOTE): data _null_;
MPRINT(READ_TITLE_FOOTNOTE): x=sleep(5);
MPRINT(READ_TITLE_FOOTNOTE): run;

NOTE: DATA statement used (Total process time):
real time 5.02 seconds
cpu time 0.01 seconds

MPRINT(READ_TITLE_FOOTNOTE): filename word DDE 'Winword|System';
MPRINT(READ_TITLE_FOOTNOTE): data _null_;
MPRINT(READ_TITLE_FOOTNOTE): file word;
SYMBOLGEN: Macro variable IN resolves to D:\OneDrive - MSOPROD\xx\Desktop\New folder1\lab-able.rtf
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(READ_TITLE_FOOTNOTE): put '[FileOpen.Name = "' "D:\OneDrive - MSOPROD\xx\Desktop\New folder1\lab-table.rtf" '"]';
SYMBOLGEN: Macro variable PAGE resolves to 1
MPRINT(READ_TITLE_FOOTNOTE): put '[EditGoto.Destination="' "1" '"]';
SYMBOLGEN: Macro variable SECTION resolves to header
MPRINT(READ_TITLE_FOOTNOTE): put "[Viewheader]";
MPRINT(READ_TITLE_FOOTNOTE): put '[EditSelectAll]';
MPRINT(READ_TITLE_FOOTNOTE): put '[EditCopy]';
MPRINT(READ_TITLE_FOOTNOTE): put '[FileClose]';
MPRINT(READ_TITLE_FOOTNOTE): put '[FileExit]';
MPRINT(READ_TITLE_FOOTNOTE): run;

NOTE: The file WORD is:
DDE Session,
SESSION=Winword|System,RECFM=V,LRECL=32767

NOTE: 7 records were written to the file WORD.
The minimum record length was 10.
The maximum record length was 90.
NOTE: DATA statement used (Total process time):
real time 13.23 seconds
cpu time 0.07 seconds

MPRINT(READ_TITLE_FOOTNOTE): data _null_;
MPRINT(READ_TITLE_FOOTNOTE): x=sleep(5);
MPRINT(READ_TITLE_FOOTNOTE): run;

NOTE: DATA statement used (Total process time):
real time 5.00 seconds
cpu time 0.01 seconds

MLOGIC(READ_TITLE_FOOTNOTE): %LET (variable name is RC)
MPRINT(READ_TITLE_FOOTNOTE): data _null_;
MPRINT(READ_TITLE_FOOTNOTE): x=sleep(10);
MPRINT(READ_TITLE_FOOTNOTE): run;

NOTE: DATA statement used (Total process time):
real time 10.00 seconds
cpu time 0.03 seconds

MPRINT(READ_TITLE_FOOTNOTE): filename excel DDE 'Excel|System';
MPRINT(READ_TITLE_FOOTNOTE): data _null_;
MPRINT(READ_TITLE_FOOTNOTE): file excel;
MPRINT(READ_TITLE_FOOTNOTE): put '[Error(false)]';
MPRINT(READ_TITLE_FOOTNOTE): put '[Paste]';
SYMBOLGEN: Macro variable LOCATION resolves to D:\OneDrive - MSOPROD\xx\Desktop\New folder1
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(READ_TITLE_FOOTNOTE): put '[SAVE.AS("' "D:\OneDrive - MSOPROD\xxDesktop\New folder1\content.xls" '")]';
MPRINT(READ_TITLE_FOOTNOTE): put '[Close]';
MPRINT(READ_TITLE_FOOTNOTE): put '[Quit]';
MPRINT(READ_TITLE_FOOTNOTE): run;

NOTE: The file EXCEL is:
DDE Session,
SESSION=Excel|System,RECFM=V,LRECL=32767

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase.
NOTE: 1 record was written to the file EXCEL.
The minimum record length was 14.
The maximum record length was 14.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.08 seconds
cpu time 0.00 seconds

MPRINT(READ_TITLE_FOOTNOTE): data _null_;
MPRINT(READ_TITLE_FOOTNOTE): x=sleep(7);
MPRINT(READ_TITLE_FOOTNOTE): run;
NOTE: DATA statement used (Total process time):
real time 7.00 seconds
cpu time 0.00 seconds

SYMBOLGEN: Macro variable LOCATION resolves to D:\OneDrive - MSOPROD\xx\Desktop\New folder1
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(READ_TITLE_FOOTNOTE): libname xls excel "D:\OneDrive - MSOPROD\xx\Desktop\New folder1\content.xls" header=no mixed=yes;
ERROR: ERROR: UNABLE TO ACCESS MESSAGE 728.1

ERROR: Critical YP Subsystem error opening message library.

ERROR: Error in the LIBNAME statement.
SYMBOLGEN: Macro variable OUT resolves to raw
SYMBOLGEN: Macro variable SECTION resolves to header
MPRINT(READ_TITLE_FOOTNOTE): data raw_header;
MPRINT(READ_TITLE_FOOTNOTE): set xls."sheet1$"n;
ERROR: Libref XLS is not assigned.
SYMBOLGEN: Macro variable PAGE resolves to 1
MPRINT(READ_TITLE_FOOTNOTE): page=1;
MPRINT(READ_TITLE_FOOTNOTE): if missing(f1) then delete;
MPRINT(READ_TITLE_FOOTNOTE): run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.RAW_HEADER may be incomplete. When this step was stopped there were 0 observations and 2 variables.
WARNING: Data set WORK.RAW_HEADER was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds

MPRINT(READ_TITLE_FOOTNOTE): libname _all_ clear;
ERROR: Unable to clear or re-assign the library TEMPLATE because it is still in use.
SYMBOLGEN: Macro variable LOCATION resolves to D:\OneDrive - MSOPROD\xx\Desktop\New folder1
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(READ_TITLE_FOOTNOTE): FILENAME db "D:\OneDrive - MSOPROD\xx\Desktop\New folder1\Content.xls";
MLOGIC(READ_TITLE_FOOTNOTE): %LET (variable name is RC)
MPRINT(READ_TITLE_FOOTNOTE): FILENAME db clear;
NOTE: Fileref DB has been deassigned.
MLOGIC(READ_TITLE_FOOTNOTE): Ending execution.

[SASKiwi]

DDE is no longer supported by Microsoft in more recent versions of MS Office so I'm not surprised by the errors. Reading from OneDrive would most likely reduce the already slim chances of success. Try reading from a real disk drive to see if that works any better. 

[SASuserlot]

Thanks for the suggestion,  I tried reading it from real disk, I am getting the same error. Interesting is that  DDE statement able to work with 'Word' file opening, selecting the content and closing  the file . However ,at 'Excel' code,  it just opening the excel but not creating any excel files.🤔

[ballardw · Accepted solution]

One possible issue is with using XLS files with current versions of Excel engines.

The last project I had that used DDE that involved Excel and SAVE AS required providing a file-type code as part of the syntax

[SAVE.AS("path\filename.xlsx",51)]

where 51 was the file type for XLSX. If the filet type option did not match the extension then that was an error. So you may be running into one or both of 1) need to provide the file type to work with XLS, 2) the current version of Excel really expects to create XLSX by default but needs to be told.

When I was doing this the CODE number changed depending on the version of Office installed. I had to update this code value several times as our Office suite was updated, i.e research Microsoft websites to find the values for this code value for specific versions of Office. Which was just one reason that DDE was such a pain.

[SASuserlot]

Your explanation worked the code '51' did the magic. I also realized that, it only creating the file, If  kept Excel blank file open. Not sure what it is exactly happening since this is the first time I am dealing DDE. Thanks again for your time.

[ballardw]

It has been a long time since I needed DDE code. When my project ran instead of:

put '[Close]';
put '[Quit]';

I was using

 x= sleep(1);
 put '[File.Close(false)]';
 put '[Quit()]';

I don't know if the sleep was really needed but it seemed like some time was needed to do the Save before the close was attempted. Or it was there because of the example I found to base stuff on. I quit playing with the code as soon as I got something that worked and then left it alone (until the next Office upgrade and finding the file type codes).

[Patrick]

@SASuserlot 

DDE is depreciated and shouldn't get used anymore. https://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/ 

...unless this is just a one-off task.

If you can't do it directly with SAS then you can manipulate Word and Excel documents via vb-script or for something not only working under Windows it might be worth to look into Python. I've used Python lately to create and Excel file with dropdown validation and got quite fond of what's available. ...and Python is SAS endorsed open source available on many SAS platforms (with the SAS Studio version for Viya now even providing an IDE for Python).

[SASuserlot]

Thanks for suggestion. I don't have the strong skills either in python or VBA  macros, that's the reason I went with SAS even though it is  little old. I definitely need to be get updated😉