DDE for Excel PasteSpecial to paste values

[texasmfp]

I have a SAS program that prepares data and then pours data into an Excel file.  This is followed by a macro that goes in and "pretties it up" such as changing fonts, column widths etc.  I want to add a new function, tell Excel to copy one column an paste it back to the same place, but as values - which is Paste Special rather than the regular paste.  In Excel macro language, the language is:

Columns("L:L").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But when I try to make that run in a SAS DDE session, along with the other prettying up, it does not work.  I've done some trial and error, but I just don't know enough about translating Excel-speak to DDE-speak.

Can you tell Excel to special paste or is this a lost cause?

if tab EQ 'Unit Value Series' then do;
put '[select("c12")]';
  put '[copy()]';
  put '[SELECT("c12")]';
  put '[xlPasteValues()]';
  end ;

[Kurt_Bremser]

DDE is deprecated by Microsoft. The ports have been taken over by other software, causing DDE to not work at all. Even if you could make it work now, you only take out a loan at the future.

Use ODS to build the Excel completely with all styles, or prepare a VBA script which you can run as an external command.

[texasmfp]

Kurt:  thanks, but I am working on an isolated system with Office 2016.  Works great and no need to change at this time.  I'd appreciate help with the issue rather than a suggestion to abandon hundreds of lines of code and start from scratch.

[texasmfp · Accepted solution]

Kurt:

I created a VBA script in Excel and then added a put line to call that script in the long cue of formatting in the "prettyup" macro.

 put '[workbook.activate("Unit Value Series")]';
  put '[run("macro4")]';

Thanks