topic Re: SAS DDE to Excel 2013 freezes SAS in SAS Procedures

SAS DDE to Excel 2013 freezes SAS

Drez — Thu, 27 Aug 2015 17:38:39 GMT

Having issues since upgrading to Win 7 and 2013.

Code looks like this. There also is a close routine, etc. I added in the open in safe mode to see if that would help.

Any advice would be appreciated.

%macro OpenXL;

options noxwait noxsync;
x ' "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.exe /s" ';
data _null_;
x=sleep(5);
run;

options noxwait noxsync missing=' ';
filename sas2xl dde 'excel|system';
data _null_;
    length fid rc start stop time 8;
    fid=fopen('sas2xl','s');
    if (fid le 0) then do;
        rc=system('start excel');
        start=datetime();
        stop=start+10;
        do while (fid le 0);
            fid=fopen('sas2xl','s');
            time=datetime();
            if (time ge stop) then fid=1;
        end;
    end;
    rc=fclose(fid);
run;
%mend;

%macro OpenShell;
filename cmds dde 'excel|system';
data _null_;
    file cmds;
    put '[error(false)]'; /*This suppresses errors that may occur if Excel is already open*/
    put '[open("C:\Template.xlsx")]';
run;
%mend;

%macro write_header(actnum, period, sheetname, range);
   filename data dde "excel|&sheetname!&range" notab;
   data _null_;
   set details_&actnum(obs=1);
   file data lrecl=1000 stopover;
   put actnam;
   put actnum;
   put "&period";
   put '12345';
   put '12-3456789';
   run;
%mend;

Re: SAS DDE to Excel 2013 freezes SAS

Reeza — Thu, 27 Aug 2015 17:45:38 GMT

Please post the full code submitted.

Also, what version of Excel. I've run DDE on Windows 7 with Excel 2010 and 2013 with no issues.

Re: SAS DDE to Excel 2013 freezes SAS

ballardw — Tue, 08 Sep 2015 15:45:04 GMT

One possibly quick thing to look for may be in your environment. If you are operating in a server enviroment then your admin may have disabled the X command. Or it may have been disabled by configuration.

Run code:

proc options group=envdisplay;run;

And examine results for XCMD. If it does not say something like "The X Command is valid in this SAS session" that would be the first item to address. Note that this option does block DDE if off.

Re: SAS DDE to Excel 2013 freezes SAS

SASKiwi — Wed, 09 Sep 2015 19:38:25 GMT

Does the X command open an Excel session? If not paste the command into a Windows command line to check that it works. I'm assuming here that you are running this on your PC in a local SAS session - this is the only way DDE will work.

Re: SAS DDE to Excel 2013 freezes SAS

senorsmile — Fri, 02 Oct 2015 15:00:16 GMT

I'm having the same issue.
The code below works on a computer in my environment with Excel 2010, but freezes SAS on a separate computer with Excel 2013.

options noxwait noxsync xmin;

filename sas2xl dde 'excel|system';

data _null_;

length fid rc start stop time 8;

fid = fopen('sas2xl','s');

if (fid le 0) then do;

rc = system('start excel');

start = datetime();

stop = start+10;

do while (fid le 0);

fid = fopen('sas2xl','s');

time=datetime();

if (time ge stop) then fid = 1;

end;

rc=fclose(fid);

run;

Re: SAS DDE to Excel 2013 freezes SAS

senorsmile — Fri, 02 Oct 2015 15:16:31 GMT

Now I'm getting inconsistent results. The code above froze SAS from a brand new SAS session. I just tried a different program on the Excel 2013 computer with the same components and it worked.

Re: SAS DDE to Excel 2013 freezes SAS

senorsmile — Tue, 03 Nov 2015 18:41:37 GMT

The issue does not occur when I have Outlook 2013 closed.

If this resolves the issue for anyone else, please let us know.

Re: SAS DDE to Excel 2013 freezes SAS

t_ar_taat — Tue, 11 Oct 2016 13:53:13 GMT

Still freezing is happned with closing Outlook 2013.But DDE worked with closing skype!! (FYI:My environment is Win 7,SAS ver9.4,EXCEL 2013.)

Re: SAS DDE to Excel 2013 freezes SAS

BPatterson17 — Wed, 24 May 2017 13:48:31 GMT

Skype was the culprit for me too! Thanks very much!