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

Term Structure Use in the Tool

CommandButton A component of a UserForm that, when

pressed, activates an action.

The Command buttons will be used
to initiate some action requested by
the user (You).

CheckBox A component of a UserForm that toggles

between the on and off states.

Here we will use this to toggle the
on and off states of a requested
condition.

Properties Window A window on the Integrated

Development Environment (IDE) that
shows the state of each element of an
item on the UserForm.

We will use this window to
associate data elements on an Excel
spreadsheet with text fields on the
UserForm.

PropertySheet A window on the Integrated

Development Environment (IDE) that
shows the state of each element of an
item on the UserForm.

Property sheets associate Excel
spreadsheet data elements with text
fields on the UserForm, or apply
values to the item.

ControlSource A field on the PropertySheet that points

to an Excel cell that is used to store the
contents of the item at run time.

We will use this to point to where
the data value for this UserForm
item is stored in the Excel
spreadsheet.

Name A field on the PropertySheet that

describes the name of a component.

We will not use this field for most
items on the UserForm, making the
component items and types clearly
identifiable. However, some items
on the UserForm will have the
name changed, so we can reference
specific spreadsheet values, cell, or
other items without special coding.

The object of this workbook tool is to be able to double-click on an icon on your desktop to start Excel,
store parameters, and access information about SAS programs. This tool can be selected to create a user
copy of a departmental report program. When the SAS code runs, it will produce the requested output
based upon periodic execution parameters entered or stored in the Excel workbook. The tool presented here
will be a simple implementation with a minimum number of features to demonstrate the concept and show
you some basic steps that may be needed to build your own enhanced version of the tool. The general
process we will use is the following.

1. Identify the IDE and how to use it to build an Excel UserForm.
2. Identify the Excel objects needed to be placed upon the UserForm.
3. Build storage locations within the Excel tool to store program-unique parameters.
4. Set up objects to access system-global and program-unique data stored in the workbook.
5. Learn how to make the Excel UserForm run.
6. Link the data from worksheet “Common_Parm_Text” to the UserForm1 data fields.
7. Build Excel VBA routines to make the tool work.
8. Build Excel VBA routines to format the output files for printing.
9. Learn how to enter data and execute commands stored in the VBA code supporting the tool.
10. Build data structures that will support the input, output, and working directories.
11. Set up the programs to store output log and list files for audit purposes.
12. Show a step-by-step process to execute the tool and produce a report.
13. Build a command file (*.bat) to execute the tool by clicking on an icon.