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

  1. gravatar

    # by Albert Jr - November 30, 2009 at 8:59 PM

    quick and easy..Thanks...

  2. gravatar

    # by Anonymous - February 10, 2013 at 7:09 PM

    Hi!
    I tryed hard to make this code works...and finally i did it.
    I had problems with excel's protection rules about macro. I had to select the option "activate all macro" to make it works.
    The question is: is there a way to make the macro run automatically from SAS without changin this option?
    Other articles show SAS codes in which the string run is like:

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

    What does that FALSE means? Has it a relation with excel's protection rules?

    Thanks!
    Valerio

  3. gravatar

    # by Jim - April 2, 2014 at 9:24 PM

    My question is. How do you save the macro without saving the xlsx file as xlsm? Saving the file as xlsm prohibits be from exporting data to it.

Blog Widget by LinkWithin

Search this blog..

Loading