topic Running Excel/Access VBA from SAS EG in SAS Enterprise Guide

Running Excel/Access VBA from SAS EG

Dreamer — Wed, 25 Feb 2015 02:13:05 GMT

Hi,

Previously I could run Excel/Access VBA macro from my SAS code as shown in below example. But now my organization has moved to SAS EG 6.1 and SAS Server 9.4 architecture.

Now code like below doesn't work from SAS EG. Does it need MS Office to installed at server to support these kind of codes?

OPTIONS NOXWAIT NOXSYNC;

DATA _NULL_;

RC=SYSTEM('START EXCEL');

RC=SLEEP(5);

RUN;

FILENAME CMDS DDE 'EXCEL|SYSTEM';

DATA _NULL_;

FILE CMDS;

PUT "[OPEN(""D:\ANALYSIS-REPORTING.XLS"")]";

PUT '[RUN("Macro1")]';

PUT '[SAVE.AS("D:\FORMATTED_FILE.XLS")';

PUT "[QUIT()]";

RUN;

QUIT;

Re: Running Excel/Access VBA from SAS EG

Reeza — Wed, 25 Feb 2015 05:00:39 GMT

The code above is DDE, I don't think you can call DDE from SAS EG.

If you have Base SAS on your desktop you can.

You may also have your X commands restricted, but if not you can create a VBScript file instead that does the same thing and call that.

Re: Running Excel/Access VBA from SAS EG

Kurt_Bremser — Wed, 25 Feb 2015 06:17:56 GMT

For this to work, the SAS server needs to be installed on a Windows system and said Windows system needs to have Office installed.

And you will need to have access to a directory resource on the server via AD to work with the created files.

The prudent solution for this problem is to move the logic of the macro into SAS and create the final result with a more modern and platform-independent tool like ods tagsets.excelxp.

Doing business logic in Excel is a recipe for disaster.