DATA Step, Macro, Functions and more

DDE - simple copy & paste existing values?

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

DDE - simple copy & paste existing values?

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        
         

Accepted Solutions
Solution
‎09-25-2015 06:23 AM
PROC Star
Posts: 7,468

Re: DDE - simple copy & paste existing values?

Posted in reply to RB1Kenobi

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: DDE - simple copy & paste existing values?

Posted in reply to RB1Kenobi

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.

Contributor
Posts: 51

Re: DDE - simple copy & paste existing values?

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.
Solution
‎09-25-2015 06:23 AM
PROC Star
Posts: 7,468

Re: DDE - simple copy & paste existing values?

Posted in reply to RB1Kenobi

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

Contributor
Posts: 51

Re: DDE - simple copy & paste existing values?

Thanks Art297.
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1371 views
  • 0 likes
  • 3 in conversation