topic Re: Run a vba excel macro with SAS in SAS Enterprise Guide

Run a vba excel macro with SAS

FRAFLUTE — Tue, 21 May 2019 17:00:54 GMT

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-batch/

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

Re: Run a vba excel macro with SAS

Reeza — Tue, 21 May 2019 17:47:10 GMT

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;

Re: Run a vba excel macro with SAS

FRAFLUTE — Wed, 22 May 2019 08:33:49 GMT

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!

Re: Run a vba excel macro with SAS

Reeza — Wed, 22 May 2019 14:54:05 GMT

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.

Re: Run a vba excel macro with SAS

FRAFLUTE — Wed, 22 May 2019 16:33:33 GMT

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!

Re: Run a vba excel macro with SAS

Reeza — Wed, 22 May 2019 16:54:02 GMT

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.