topic Re: run excel macro with parameter in SAS Programming

run excel macro with parameter

makset — Thu, 27 Sep 2018 11:22:13 GMT

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

Re: run excel macro with parameter

RW9 — Thu, 27 Sep 2018 11:29:15 GMT

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.

Re: run excel macro with parameter

s_lassen — Thu, 27 Sep 2018 11:46:34 GMT

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.

Re: run excel macro with parameter

makset — Thu, 27 Sep 2018 12:34:34 GMT

is any other way to run the excel macro

I have sas 9.2 ts 2mo ms office 2010

thx

Re: run excel macro with parameter

Kurt_Bremser — Thu, 27 Sep 2018 12:58:32 GMT

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

Re: run excel macro with parameter

Reeza — Thu, 27 Sep 2018 14:17:23 GMT

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.

Re: run excel macro with parameter

makset — Thu, 27 Sep 2018 16:45:17 GMT

it just copies the file and updates the link

Re: run excel macro with parameter

makset — Thu, 27 Sep 2018 16:55:40 GMT

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

Re: run excel macro with parameter

RW9 — Fri, 28 Sep 2018 07:15:50 GMT

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.

Re: run excel macro with parameter

Reeza — Fri, 28 Sep 2018 15:08:02 GMT

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.