CODEWITHSUNDEEP
excelsasexcel-2016dde
CCP
CCP

Reputation: 129

SAS DDE to Excel 2016 fix/ alternative?

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.

Upvotes: 0

Views: 1761

Answers (1)

Tom
Tom

Reputation: 51621

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

Upvotes: 1

Related Questions