run excel macro with parameter

[makset]

Hello

I have a problem with running vba macros with the parameter from sas.

Someone has a solution?!

my macro:

Sub Refre(Nr As Integer, TF As Integer, NrCoopy As Integer)
    FileCopy "C:\FX\Terminal\Ex_" & Nr & "_" & TF & "_1.xlsx", "C:\FX\Terminal\Ex_10" & NrCoopy & "_" & TimeFrame & "_1.xlsx"
    ActiveWorkbook.UpdateLink Name:=ThisWorkbook.Path & "\Ex_10" & NrCoopy & "_" & TF & "_1.xlsx", Type:= _
        xlExcelLinks
End Sub

Sub Run()
Call Refre(0, 5, 1)
End Sub

this work

FILENAME CMDS DDE 'EXCEL|SYSTEM';
DATA _NULL_;
FILE CMDS;
PUT %unquote(%str(%'[RUN("Run")]%'));
RUN;
QUIT;

this does not work

FILENAME CMDS DDE 'EXCEL|SYSTEM';
DATA _NULL_;
FILE CMDS;
PUT %unquote(%str(%'[RUN("Refre(0, 5, 1)")]%'));
RUN;
QUIT

there is some way that the secend way would work:

thank you for help

best regards

[RW9]

I would highly recommend you drop the DDE, its is decades out of support and doesn't work in a lot of situations.

As for the parameter, can you not put that into a cell, and point your macro to that cell rather than using a parameter?

Looks like your using VBA to copy a file, seems like a mess of a process, SAS -> VBA -> File System.  SAS can move files, but there are better tools available to IT to do these kind of logged moves.

[makset]

As for the parameter, can you not put that into a cell, and point your macro to that cell rather than using a parameter?

it is possible to turn on a button in a specific sheet

[RW9]

Sorry, what do you mean button, do you mean an Excel VBA Button object?  SAS provides functionality to move data between the two systems, it does not implement all Excel or VBA functionality.  If you want to use that then you need to do that in Excel.

[Reeza]

Buttons are calls to macros so yes, you should be able to do that. However, DDE commands are for Excel Version 4 (that numbering was used once upon a time) and a lot of the newer commands are not available.

[s_lassen]

No idea if this is relevant, but have you tried with semicolons between the parameters instead? If you are running Excel in a national language setting that uses semicolons rather than commas between parameters, the Excel command interface may want the same thing in VBA calls.

[makset]

ok

is any other way to run the excel macro

I have sas 9.2 ts 2mo ms office 2010

thx

[Kurt_Bremser]

Go back to square one: what is this macro supposed to do?

[makset]

it just copies the file and updates the link

[Reeza]

Test it with a very basic Excel macro. Sorry this isn't text but here's what I usually use. 

You also want to verify  you have XCMD enabled, though I assume you've already tested all of this. 

You can see the macro calls at the bottom of the programs.