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

The workbook will be built to contain two jobs that can be set up to run periodically. One is called JOB_01
that will write the SASHELP.Shoes file to an Excel workbook and then format it using the VBS/VBA tools
described earlier in the topic. The second program, called JOB_02, will read an Excel file that contains the
output from JOB_01 and place that data into a SAS dataset. Of course, the object of this chapter is not
JOB_01 or JOB_02, but rather the tool to run the jobs.
This process can be set up using Base SAS and any version of Excel that is written using Visual Basic for
Applications (VBA). Excel should also be able to both store a macro and contain more than one Excel
worksheet. By writing this code and creating the UserForm yourself, you can answer the question, “Did
you download this from the web?” with a resounding, “NO!” This is another reason why this version of the
tool is simple.
OK, let's get started. The version of Excel used here is Excel 2010, and the workbook type is an *xlsm
workbook. Next, we will open Excel and save the workbook as
“My_Excel_Tool_To_Run_SAS_Jobs.xlsm”. We will save the workbook in directory
“Q:\My_Excel_Tool”.
14.3.2 Introduction to the Integrated Development Environment (IDE)
We start by opening the new Excel file (this version is Excel 2010 with the ribbon minimized to conserve
space on the page).
Figure 14.2: A Blank Excel Macro-Enabled Workbook with the Ribbon Minimized.
To locate the Integrated Development Environment (IDE), first select the worksheet, and then press the
keypad buttons “ALT” and “F11” at the same time. This will open another screen, shown below, which is
virtually the same for all versions of Excel that can open the IDE.