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

Figure 14.31: Security Warning: Press “Yes” to Convert the Workbook to a Trusted
Document.
Some versions of Excel will issue this type of a message, however, the newer versions just ask if you want
to make the document a trusted document when you open it.
14.4.8 Prepare a VBA Macro to Process Your Output Report
This part of the project is really geared toward working with the individual reports. Not all of the reports or
processes will need printed output or a formatted spreadsheet. JOB_02 of this group of programs just
creates a SAS dataset, while JOB_01 creates a formatted Excel workbook. Other jobs could be created to
process data files, print reports directly, or input data from text files or any other job you have ever run on a
computer. To work with this file, we need a copy of the output Excel workbook created by JOB_01 so that
we can format the report for printing.
This process involves opening the output file and then opening the IDE (with an Alt/F11 keystroke
combination). Any Excel VBA macro can be recorded to do the work for us. These VBA code modules can
then be exported to our “My_BAS_Files” directory to be used later. Here, we will create several files that
will be used to do the processing and make the whole process run from start to finish without intervention.
The VBS script “Q:\My_BAS_Files\VBS_Execute_script.vbs” can load and execute VBA macros within
an Excel workbook. The following four steps describe that processing.
1. SAS code (to be stored in each SAS program) sets up SAS macro variables and executes an “X”
command to link the SAS program to the Excel file using the VBS routine below such as
JOB_01_Write_data_to_Excel_File.sas.
2. A VBS program “Q:\My_BAS_Files\VBS_Execute_script.vbs” controls the execution of Excel
and the VBA macro code modules to format the report. This process does not leave Excel macros
in the final Excel file, so they can be given to anyone as workbooks without embedded macros.
3. The VBA program “Q:\My_BAS_Files\Dept_common.bas” houses commonly used VBA
subroutines for departmental use.
4. The VBA program “Q:\My_BAS_Files\JOB_01.bas” stores the VBA subroutines to format the
report.
The object of a system of reporting or processing tools like this is to have stable code programs that work
right every time. The programs just use different data each time. Big IT departments have been doing this
for 50 years, since the early 1960s. Now we can bring the processes to your desktop PC. Of course, stable
programs like stable data, so this might not be for everyone. But, front-end processes can make the data
ready for the tools. A tool like the one we just described here is simple to operate. After the data is ready,
there are just a few steps in the process.