DDE Calling Excel Macro with additional Parameters

[arnouxvr]

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, 

[PhilC]

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