Create an Excel Workbook That Runs SAS Programs - Exchanging Data Between SAS and Microsoft Excel - page 248

The information in the form in Figure 14.16 will be used to create the Working directories for executing the
tool reports. The fields “Work directory”, “Year”, and “Month” will be used in the naming structure where
the Tool will send the working copies of the SAS code file, Tool-Unique Parameter file, and the *.bat job
file that can execute the SAS code in a batch mode. Listing output options for SAS 9.3 and later could send
the listing output to the “Results” window, which may prevent the listing from going to the List output file.
This is due to a change in the default shipping value on the listing output from list to html in SAS 9.3. You
can change this value in SAS on the Tool ribbon by selecting “Options/Preferences/Results”.
Figure 14.17: Directory Path for the Working Copies of the Report Code and Temp Files.
14.4 Excel VBA Routines to Make the Workbook UserForm Active
Now comes the fun part--creating VBA routines to make all of these switches and buttons work. The initial
task is to figure out what routines we need to write. Since Visual Basic is an object-oriented language,” we
do not really need to worry about the order of the tasks. We just need to write them so that they are
independent of each other. By looking at the GUI, we can get a good idea of the types of code routines we
need to have. Let's give it a whirl. Here are some of the things we need to do.
1. Initialize the UserForm.
2. Write the user parameters to a file in a working directory.
3. Copy source programs from a production directory to the working directory.
4. Verify the output batch file points to the correct SAS run-time module.
5. Write a routine to save the changes and exit the program.
6. Set up the directory structure associated with the processing.
7. Prepare for common issues that might occur.
8. Prepare a VBA macro to process your output report.
Each of these groups of VBA routines will have several subroutines and can run in any given order. They
need to operate independently because the user (you) will have the ability to select any part of the screen to
update or execute in any given order. The descriptions in the rest of this section will give general guidelines
about what the code will be designed to accomplish. The actual code will be available for download in the
example code and data folder, accessible from the SAS Press author page
at http://support.sas.com/publishing/authors/benjamin.html
14.4.1 Initialize the User Form
14.4.1.1 Install VBA Code to Start the UserForm When Excel Begins
First we need to gain control of the Excel routines and start our UserForm. The following VBA in Figure
14.18 shows how to gain control of Excel.