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

Chapter 14: Create an Excel Workbook That Runs
SAS Programs
14.1 Introduction............................................................................................... 231
14.2 Purpose ..................................................................................................... 232
14.3 Guidelines for Building an Excel User Form Interface ............................... 233
14.3.1 Common Excel and Excel User Form Terms .......................................................233
14.3.2 Introduction to the Integrated Development Environment (IDE) .......................235
14.3.3 Using the Integrated Development Environment (IDE) Toolbox Menu .............236
14.3.4 Building a Sample Integrated Development Environment (IDE) Menu .............237
14.3.5 Linking the Integrated Development Environment (IDE) Menu and the Data ..239
14.3.6 Storing Control Information in the Excel Workbook Worksheets .....................240
14.3.7 Set Up Control Variables to Access Data Stored in the Workbook ..................241
14.3.8 Learn How to Make the Excel UserForm Execute...............................................245
14.4 Excel VBA Routines to Make the Workbook UserForm Active.................... 248
14.4.1 Initialize the User Form...........................................................................................248
14.4.2 Write the User Parameters to a File in a Working Directory ..............................253
14.4.3 Copy Source Program from a Production Directory to the Working
Directory ..............................................................................................................................253
14.4.4 Verify the Output Batch File Points to the Correct SAS Run Time Module ......254
14.4.5 A Routine to Save the Changes and Exit the Program .......................................255
14.4.6 Directory Structure Associated with the Processing..........................................255
14.4.7 Common Issues That Might Occur. ......................................................................257
14.4.8 Prepare a VBA Macro to Process Your Output Report.......................................258
14.5 Conclusion ................................................................................................ 259
14.1 Introduction
Chapters 12, 13, and 14 each define separate, but increasingly powerful tools that combine SAS and Excel
features and that integrate your ability to transfer data between SAS and Excel. At the beginning of each of
these chapters I have placed a short list of all of the tools--just in case you look at only one of the chapters
today.
This chapter will describe and show you how to build a simple set of Excel forms and macros. It could
provide a starter system to allow you to store parameters in an Excel workbook and run SAS code using
only Base SAS and Excel. The tool described here is not intended to replace any SAS software and has
some limitations that do not exist in SAS applications. The intended user audience for this Excel workbook
application will need to know how to program in both SAS and Microsoft Visual Basic languages. Some of
the limitations of this Excel workbook are listed below.
● If the workbook is on a user's computer, only the user has access to the workbook.
● If the workbook is on a server drive, many people can use the workbook one at a time.
● If the workbook is on a server drive, any changes are visible by the next user.
● Knowledge of VBA and VBS is required to install the workbook and add new programs.
• Setting up the workbook for someone new to use requires working knowledge of Excel options.