Desktop productivity for business analysts and programmers

Running Excel/Access VBA from SAS EG

Reply
Frequent Contributor
Posts: 96

Running Excel/Access VBA from SAS EG

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;

Super User
Posts: 19,039

Re: Running Excel/Access VBA from SAS EG

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.

Super User
Posts: 7,399

Re: Running Excel/Access VBA from SAS EG

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 882 views
  • 0 likes
  • 3 in conversation