SAS WPS DDE Exporting Data to Excel: Error DMLERR_NO_CONV_ESTABLISHED

Question

I'm trying to use DDE (Dynamic Data Exchange) in order to export a SAS dataset to pre-formatted Excel template document. I'm using SAS code within WPS v4 and Excel 2016. I'm using the following code:

options noxwait noxsync;

x "'C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Excel 2016.lnk'";

data _null_;
   T = sleep(10);
run;

filename xl 
   DDE "Excel|[N:\Analysis\DDE\Template.xlsx]sheet1!r2c2:r1245c78" notab 
lrecl=650;
run;

data _null_;
   file xl dlm='09'x dsd;
   set &sasData.;
   put var1 var2 var3;
run;

This code produces the following error:

ERROR: Failed to connect to Excel|[N:\Analysis\DDE\Template.xlsx]sheet1!r2c2:r1245c78 : The error DMLERR_NO_CONV_ESTABLISHED was returned

The first bits of the code are working as Excel opens up when this is run.

I have tried disabling protected view on my Excel workbook and also using an older (xls) version of Excel for my template document. This did not solve the problem.

Can anybody help with this please?

Answer 1

Adding the following code seems to fix this particular error:

 filename xl dde 'excel|system';
   data _null_;
      file xl;
      put '[FILE-OPEN("N:\Analysis\DDE\Template.xlsx")]';
 run;

This associates the .xlsx template file with DDE/Excel.