Tools of the Trade V: Kicking Excel VBA Macros from SAS

The code below enables SAS programs to launch Excel VBA macros. Let's assume that you have an Excel Spreadsheet named Indat.xls that contains a macro which generates a bar chart when certain cells are populated. You have named the macro ShoChrt. Use the following code to populate the necessary cells and launch the ShoChrt Macro:

The first step is assigning the file references to the Excel spreadsheet containing the VBA macro(s), and opening the spreadsheet.

options xsync;

filename EXCEL DDE 'EXCEL|SYSTEM';
filename EXPORT DDE 'EXCEL|Sheet1!r1c1:r2c6' notab ;

data _null_;
file excel;
put '[open("C:\TEST\INDAT.XLS")]';
run;

Write the code to perform your analyses in SAS.

/* Perform your analysis in SAS */
proc summary data = indat nway; class size;
var price;
output out = outdat sum=;
run;
Export your output data to Excel.

data _null_;
set outdat;
tab = '09'x;
file EXPORT;
run;

The SAS code below will kick off the VBA macro(s).

/* Execute the previously created VBA macro named 'SHOCHRT' */
data _null_;
file excel;
put '[RUN("ShoChrt")]';
run;

Finally, the code below will remove the file references for your Excel spreadsheet(s).

filename EXCEL clear;
filename EXPORT clear;

Note: If you have any problems launching the Excel file using the code above, try this block of code instead:

options noxwait noxsync;
x '"c:\program files\microsoft office\office11\excel.exe"';

data _null_;
x=sleep(5);
run;

filename excel DDE 'EXCEL|SYSTEM';