Hello, I'm having an issue exporting data to excel through DDE. It worked perfectly good on Excel 2010 until new excel version (Excel 365) was installed. The issue occurs when DDE statement "[error(false)]" is used. It should (and was, before update) turn off all subsequent error and alert messages on excel file. Now it doesn't seem to be working. Example code to recreate mentioned issue: /* Test dataset */
proc sql;
create table DATA as
select *
from SASHELP.CLASS;
quit;
/*** Paste data using DDE ***/
/* Start DDE session */
options noxwait noxsync missing=' ';
filename sys dde "Excel|system";
data _NULL_ ;
length FID RC STOP 8 ;
FID = FOPEN("sys","S");
if FID = 0 then do ;
RC = SYSTEM("start excel");
STOP = datetime() + 5 ;
do while (FID = 0) ;
FID = FOPEN("sys","S");
if (Datetime() > STOP) then FID = 1 ;
end ;
end ;
RC = fclose(FID);
run ;
data _null_ ;
rc = sleep (5) ;
run ;
/* Open template */
data _null_; file sys; put '[open("D:\Model.xlsm")]'; run;
/* Paste data */
filename paste dde "Excel|Sheet!R5C2:R23C6";
data _NULL_ ;
set DATA;
file paste;
put NAME SEX AGE HEIGHT WEIGHT
;
run;
/* Close template */
data _null_; file sys; put "[error(false)]";run;
data _null_; file sys; put "[QUIT()]";run; The code opens excel, then opens specific template (blank file can be used instead), pastes data, turns off error messages and closes file (there is no practical reason to close file without saving - it is used just to recreate issue). However as I mentioned a massage still appears asking: "Want so save your changes to 'Model.xlsm'?". Because of the same reason existing excel files can not be overwritten as well - the "[error(false)" statement just doesn't seem to be working anymore. Maybe someone has encountered this issue? P.S. I'm using SAS 9.4 M6. P.S2. I have tried using "[error(false)] " directly on excel (in Macro 4.0 sheet) and it worked - no prompts occur when I tried to close file or overwrite existing file with it. So it seems that the statement still exists on excel 365, but excel just doesn't want to take it from SAS.
... View more
