topic Re: DDE for Excel PasteSpecial to paste values in SAS Programming

DDE for Excel PasteSpecial to paste values

texasmfp — Sat, 10 Dec 2022 06:25:25 GMT

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 ;

Re: DDE for Excel PasteSpecial to paste values

Kurt_Bremser — Sat, 10 Dec 2022 07:11:45 GMT

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.

Re: DDE for Excel PasteSpecial to paste values

texasmfp — Sat, 10 Dec 2022 07:21:06 GMT

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.

Re: DDE for Excel PasteSpecial to paste values

texasmfp — Sat, 10 Dec 2022 09:26:26 GMT

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