Run a vba excel macro with SAS

[FRAFLUTE]

Hi, i want to use SAS to run a macro in a excel .xlsm file. I read this useful guide:

https://blogs.sas.com/content/sasdummy/2011/05/03/using-sas-enterprise-guide-to-run-programs-in-batc...

but i've a problem to use the filename "excel|system" option.

The code is:

options noxwait noxsync;
x '"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" ';
/* Sleep for 5 seconds to give Excel time to come up */
data _null_;
x=sleep(5);
run;
filename cmds dde "excel|system";

data _null_;
file cmds;
/* Open the excel file test.xlsm which contains the VBA macro */
put '[open("X:\temp\test.xlsm")]';
/* Run myVBAmacro */
put '[run("test.xlsm!myVBAmacro")]';
run;

When i run a program the log give back this error:

ERROR: Physical file does not exist, excel|system.

What is wrong?

Thanks for the support

[Reeza]

First verify that XCMD is enabled and that you're not running on a server. If you are, DDE isn't a process that will work for you.

You can verify your XCMD option using hte following:

proc options option=xcmd;run;

[FRAFLUTE]

Thank you for the reply!

I didn't understand the log from the code submitted for XCMD... 😞

The log is:

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21         
22         GOPTIONS ACCESSIBLE;
23         proc options
24         option=xcmd;
25         run;

    SAS (r) Proprietary Software Release 9.4  TS1M2

 XCMD              Enables the X command in SAS.
NOTE: PROCEDURE OPTIONS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

26         
27         GOPTIONS NOACCESSIBLE;
28         %LET _CLIENTTASKLABEL=;
29         %LET _CLIENTPROJECTPATH=;
30         %LET _CLIENTPROJECTNAME=;
31         %LET _SASPROGRAMFILE=;
32         
33         ;*';*";*/;quit;run;
34         ODS _ALL_ CLOSE;
35         
36         
37         QUIT; RUN;

it's enable?

Sorry!

[Reeza]

Yes it is. The second thing is to check that SAS runs locally or server and then if it runs locally, verify that your path to Excel is correct. It changes for each version of Excel.

[FRAFLUTE]

Probably SAS run on server, because for starting the process we wait the connection to the server SAS App. It's possible?

Sorry for my ignorance and thank you for your support!

[Reeza]

SAS needs to have access to the file and Excel, whether that's on a server or locally. You need to ensure you have both in the same place. If you don't you cannot do this. Some indications you may not be able to - you have to manually download the file from the server to work with it, you can't import files from your desktop using code.