DDE Calling Excel Macro with additional Parameters

arnouxvr — Wed, 10 Mar 2021 02:22:01 GMT

I am currently calling an Excel Macro from withing SAS which works perfectly using DDE commands.

*execute the excel marco;
put "[run(""&excelMarcoName."")]";

The Excel macro opens another excel file and saves it in a different location.

ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\testuser\src\reports\new_excel_file.xlsx"

Currently, the path of the file is hardcoded in the Excel Macro.

When calling the Excel Macro is it possible to include a parameter or additional argument that will contain the location of the directory into which the file should save?

That parameter would then be used inside the Excel Marco to determine where to save the new file.

https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/154-31.pdf

Many thanks,

Re: DDE Calling Excel Macro with additional Parameters

PhilC — Fri, 12 Mar 2021 18:38:55 GMT

I found this, in a SAS global forum paper. Can you use these code snips to make it work?

Put '[open("'"&dir"'\'"&&FileNm&i."'")]'; /* top of p.7 */

Data _NULL_;   /* bottom of p.6 */
 File sas2xl; 
 Put '[error(false)]'; 
 Put '[save.as("'"&dir"'\'"&Table."'")]'; 
Run; 
%MACRO I

topic DDE Calling Excel Macro with additional Parameters in SAS Programming

DDE Calling Excel Macro with additional Parameters

Re: DDE Calling Excel Macro with additional Parameters