EXPORT SAS DDE doesn't communicate with windows 10 Excel 2016

[Marwa_Se]

Hello everybody,

I'm working with the DDE export to generate a dashboard, i used to work with this code below to open my excel file before Moving to Windows 10

SYSTASK COMMAND "Start Excel";

Data _NULL_;
CALL SLEEP (5,1);
RUN;

/* Open excel */
filename xc dde 'excel|system';
DATA _null_ ;
 file xc;
  put '[ouvrir("file\Challenge.xlsx")]' ;
   t=sleep(5) ;
RUN ;

Now since we moved to Windows 10 even when i'm using  the same PC SAS version 9.4 but Excel 2016. When I run the same code with the same dataset, the PC SAS gets hung for an eternity after a couple of seconds and then I have to just kill the process. Nothing happens to the open excel file. I can't even see the log since the PC SAS gets hung. Please suggest a solution for this

thank u in advance!

Marwa

[JOL · Accepted solution]

DDE is a legacy method, and SAS does not encourage using DDE. Instead, consider using the SAS Add-In to MS Office or alternatives listed below.

SAS programs that use DDE can still work, as long as you situate your SAS software and Microsoft Excel to run on the same Windows PC.

Here are some alternatives to DDE:

SAS Add-In for Microsoft Office

Use Excel to pull your SAS content into the spreadsheet. You can use SAS stored processes to encapsulate whatever SAS operation you need.

Office Scripting

You can use Office scripting to automate the process like a batch job, so that the process can run unattended. Those who use your Excel documents do not need to install the SAS Add-In for Microsoft Office in order to view the results.

PROC EXPORT

supports adding sheets to existing files or replacing entire sheets.

ODS EXCEL

For batch SAS users, ODS tagsets. ExcelXP and ODS EXCEL can be used to place formatted report content into an Excel spreadsheet. See ODS EXCEL for an example.

Scripts

enables the use of scripts like VBS and PowerShell to plug SAS data results into a spreadsheet as a post-process, which users can spawn within their SAS programs.

[ChrisNZ]

> DDE is a legacy method, and SAS does not encourage using DDE

Just to add:

DDE is a legacy method, and Microsoft does not encourage using DDE. It's not a stance by SAS.

It only remains enabled, with no support, for compatibility purposes. 

See this devblog entry from 2007 https://devblogs.microsoft.com/oldnewthing/?p=27863

[ballardw]

In addition to @JOL's very useful information the communications links that DDE use can be interfered with by other newer applications.

I discovered that Cisco Jabber for one, if running, disables DDE even when I had Excel installed locally. So to use DDE I had to KILL the Jabber process (not stop the application, go into Task Manager and KILL the process). If you have other applications that interfere you may not have the option of killing them to allow DDE to run.

Luckily the reason I was using DDE was replaced by ODS options.