DDE - simple copy & paste existing values?

[RB1Kenobi]

Hello

I have a process where I export data from SAS into an Excel workbook using DDE.  However, I also have to manipulate existing values within the target excel file.   My attempts at a simple copy and paste of existing values in the worksheet are not working as expected, if anyone has done this previously please could you point me in the right direction?

Thanks

my code:

* copy and paste previous snapshots;
filename ongcopy dde "excel|Sheet1! r2c1:r11c14";

data _null_;
put '[error(false)]';
file ongcopy notab lrecl=500;
put '[select("r2c1:r11c1")]';
put '[copy()]';
put '[SELECT("r2c4")]';
put '[PASTE()]';
run;

I can't attach an excel file, but here's what the cells look like if you open Excel and paste these into Sheet1

copy from here			paste to here
Aug-15
1
25
0
0
1
27
18
67%
26/05/2015

[RW9]

Hi,

The simplest answer to this is don't.  DDE is old technology, its doesn't have full functionality beyond the scope it was initially used for.  I would read up on ods tagsets.excelxp or excel.  These have far more functionality in generating XML which is either native to Excel, or can be read by Excel.  

If there is something outside the scope of the tagsets, maybe you have a complicated template Excel file the data needs to go over to, then re-think the approach you take.  Insteand of writing to the Excel file from SAS, just drop the data out to a CSV file from SAS.  Now in your Excel file write a small bit of VBA macro to read in the CSV file and process it.  This option gives you all the functionality of Excel, i.e. you can read the data, create graphs, update pivots, create links etc. but you need to know VBA.

[RB1Kenobi]

Thanks RW9. I've used tagsets to produce reports from scratch but not in conjunction with an existing document. I hadn't thought of the CSV/VBA option so thanks for pointing that one out.

[art297 · Accepted solution]

It's been a while since I've used DDE, but your code "almost" works as is. With a couple of minor changes, it will do exactly what you intended.

From your code I'm assuming that you have already opened the workbook, in Excel, prior to running the code. If so, then simply run:

* copy and paste previous snapshots;
options noxsync noxwait;
filename sas2xl dde 'excel|system';

data _null_;
  file sas2xl;
run;

data _null_;
  file sas2xl;
  put '[workbook.activate("Sheet1")]';
  put '[select("r2c1:r11c1")]';
  put '[copy()]';
  put '[SELECT("r2c4")]';
  put '[PASTE()]';
run;

If you want to do anything more complex, do a Google search for any of Koen Vyverman's papers. One example of his excellent papers on the topic is: http://www2.sas.com/proceedings/sugi26/p011-26.pdf

[RB1Kenobi]

Thanks Art297.