Analytics Tools

VBA-RUNNING VBA FROM SAS

USING SAS TO RUN VBA MACROS

You can find a lot of papers out there which ramble on and on about this particular topic. Our idea is to give a short and an easy description of this process and after going through this article you will be able to run an Excel macro through SAS.

Let us first look at a sample code (code S65ZG7KNVSQG)

Sample code:

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;

Here’s what it means.

1. OPTION NOXSYNC

This option tells SAS to close the prompt window that opens automatically when it is calling up Excel

2. OPTION NOXWAIT

This option tells SAS to continue to run the program after it is done calling up Excel

3. rc=system('start excel');

We need to execute some command to launch Excel. The DOS command START is very useful in opening Windows based applications. It uses the Windows Registry file to determine the location of the executable file for the application referenced (in this case, Excel). Therefore, there is no need to know or determine the exact path of the executable file for Excel. This doesn’t work with all applications, but it does with many, including Microsoft applications and the SAS System.

4. rc=sleep(5):

SLEEP command to stop SAS system from processing for 5 minutes. This is to make sure that the template in Excel is opened before SAS proceeds with the data updating in the steps followed. You can increase it if your system is slow.

5. filename cmds dde `excel|system' ;

data _null_; ………… put "[Quit()]";

The FILENAME statement establishes a link with Excel system, opened with a keyword ‘DDE’. CMDS is the handle defined to refer to this link . A temporary dataset is created to invoke the template. FILE statement tells SAS which link (established with excel) is to be used. PUT command – passes on the instructions to EXCEL system which includes executing Excel macro commands & saving the work book

PEOPLE WHO READ THIS ALSO READ : Excel, SAS, SAS-NOXSYNC, SAS-NOXWAIT, SAS-Run VBA, VBA, VBA-Running from SAS

This entry was posted on Sep 7, 2009 at 1:24 AM and is filed under Excel, SAS, SAS-NOXSYNC, SAS-NOXWAIT, SAS-Run VBA, VBA, VBA-Running from SAS. You can follow any responses to this entry through the RSS 2.0. You can leave a response.