SAS DDE to Excel 2016 fix/ alternative?

Question

I'm relatively new to SAS programming and have inherited a code that has been written with DDE to open and export data to a template.

Below is the code used :

** Use DDE to port the data to Excel template.;
* Start Excel.;

OPTIONS NOXWAIT NOXSYNC;

X "'C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE'";

DATA _NULL_;
   zzzz = sleep(20);
RUN;

* Open the Excel file.;

FILENAME CMDEXCEL DDE 'EXCEL|SYSTEM';

DATA _NULL_;
   FILE CMDEXCEL;
    PUT '[OPEN("I:\J3 O\J3 OP Integration Division\Customer Support\RESEARCH\Weekly Workload\Open Ticket SAS Reports\Open_detail_Template.xlsx")]';
RUN;

DATA _NULL_;
   zzzz = sleep(20);
RUN;


* Move labels.;

FILENAME DDEWRITE DDE "EXCEL|Open!r2c1:r7000c14" NOTAB;

DATA _NULL_;
   FILE DDEWRITE;
   set todays_detail;
    if crm_group = 'OP';
   put
       color '09'x
       employee '09'x
       ticket_number '09'x
       pri_text '09'x
       open_date '09'x
       due_date '09'x
       classification '09'x
       requisition_num '09'x
       nsn '09'x
       dodaac '09'x
       depot '09'x
       status '09'x
       tot_tix '09'x
       tot_actions
   ;
run;

DATA _NULL_;
   zzzz = sleep(2);
RUN;



DATA _NULL_;
   FILE CMDEXCEL;
  PUT '[SAVE.as("'"I:\J3 O\J3 OP Integration Division\Customer Support\RESEARCH\Weekly Workload\Open Ticket SAS Reports\Open Ticket Detail Report &rpt_date2..xlsx"'")]';
  PUT '[QUIT()]';
run;

What Ive noticed is that the X statement opens up the excel app fine. However that is where it gets hung up. On my colleagues computers, the template opens up and you can see the data being written into the appropriate template fields. I have tried many different things to try to get the DDE connection to work. Ive tried to disable all add ins, disable and enable the ignore other applications check box in excel, closed all apps and only run SAS EG, Restart my computer and run it etc etc.

i have also looked into the answers from

SAS put data to Excel via DDE

Problems with SAS DDE with Office 2010

Change from DDE to proc export because office 2016 does not support it

The last link i think is the most helpful but i would like to try to keep the code as is.

I do have the SAS PC/ACCESS license so i was thinking proc import and proc export might be an option. Im not quite sure how i would replicate it to do the same steps as the DDE code.

Thank you for the help. I greatly appreciate the time.

Note: I have read an option to repair microsoft works, however at the moment this is not an option i can take.

Does Excel have DDE enabled as an option? It may open (system command) but may not accept DDE commands if that isn't enabled. And you need to provide more information on where it's going wrong and if there's an error with SAS. — Reeza
@Reeza Where would i find that setting? Is that the same setting under the options-advanced-general- ignore applications using DDE? The error occurs during the : DATA NULL; FILE CMDEXCEL; PUT '[OPEN("I:\J3 O\J3 OP Integration Division\Customer Support\RESEARCH\Weekly Workload\Open Ticket SAS Reports\Open_detail_Template.xlsx")]';. Excel opens up fine but the template does not open at all. It hangs there until i kill SAS in the task manager. — CCP
No, it's in the Trust Center, see instructions about halfway down this page. You likely need both options set though. support.office.com/en-us/article/… — Reeza
@Reeza I took a look at the page. I enabled the first part of the settings, interesting enough the DDE portion is missing. Any ideas of why that may be the case? — CCP

Tom Tom · Accepted Answer · 2019-01-29T21:28:46

DDE is ancient technology. There are some strange interactions with other processes on your PC that can make it break. You might get it to work if you can figure out what other program you are running that is hijacking the DDE signals and close it.

Or switch to another method. Such as Visual Basic.

Take a look at this paper. See example 8 for something close to your situation.

http://www.sascommunity.org/mwiki/images/d/d6/2444-2018.pdf

SAS DDE to Excel 2016 fix/ alternative?

1 Answers