Building a System of Microsoft Windows Scripts to Control Excel Macros - Exchanging Data Between SAS and Microsoft Excel - page 219

computer that is currently being used. However, the macros used by the Macro Library Tool in this chapter
can be stored neatly anywhere. This difference also allows better control of the actual code used in the
macros. So, it really does not matter where you store the macros; just leave a place in your naming
convention for them.

13.3.4 SAS Code to Execute a Visual Basic Script
The SAS code shown in Figure 13.3.5 does not need to appear in the order listed in this figure. The only
prerequisite for it to operate is that the “X” command has to have everything else defined before it can
work.

Figure 13.3.5: SAS Code to Prepare Data and Parameters and Then Call a VBS Script
Module.

proc sort data=sashelp.class out=class; ➊
by sex Height;
run;

ods tagsets.ExcelXP body='C:\MY_Excel_Files\my_sorted_class_data.xml'; ➋
proc print data=class noobs;
run;

ods tagsets.ExcelXP close; ➌

* VBA subroutine name to execute ;
%let vbs_code = C:\My_VBA_macros\VBS_Execute_script.vbs ; ➍

* Full File path and Input file name ;
%let Input_Excel = C:\MY_Excel_Files\my_sorted_class_data.xml; ➎

* Full File path and Output file name ;
%let output_excel = C:\MY_Excel_Files\my_sorted_class_data.xlsx;

➏

* Full File path Location of bas file ;
%let bas_code_path= C:\My_VBA_Macros\Chapter_13\;

➐

* VBA Module name (without the bas) ;
%let vba_module = Class_Graph;

➑

* VBA subroutine name to execute ;
%let vba_code = Class_Graph; ➒
X "'&VBS_code.' ""&Input_Excel"" ""&output_excel""
""&bas_code_path"" ""&vba_module"" ""&vba_code"" "; ➓

Here is an explanation of the code in Figure 13.3.5.
➊ Use any SAS code to create a dataset to work with.
➋ This step creates a *.xml file. Any code that can produce a dataset that will open using Excel will work
here.
➌ This line of code closes the ODS output routine and releases the output file for use by other programs.
➍ This defines the path and file name of the VBS module that will process the Excel macros and file
formatting. The details of the code referenced here are explained in Figure 13.3.7a.
➎ This is the name of the file to be input to Excel and processed by the macros.
➏ This is the name of the output file given to the saved output when the Excel macros have finished
processing the input file. If this file path and name are the same as the input file, then the file is saved
and replaced the original file.
➐ This is the path to the directory where the Excel macros are stored. Only a path is defined here so that
many different macro files (*.bas) can be stored here. This file is a candidate for being placed into a
secure location and Write-protected.