Building a System of Microsoft Windows Scripts to Control Excel Macros - Exchanging Data Between SAS and Microsoft Excel - page 211

Chapter 13: Building a System of Microsoft
Windows Scripts to Control Excel Macros

13.1 Introduction............................................................................................... 211
13.2 Purpose ..................................................................................................... 212
13.3 Guidelines for Building and Using a VBS/VBA Macro Library..................... 214
13.3.1 Create Naming Conventions for Storing and Executing VBS/VBA Macros......214
13.3.2 Set Up Workstation Options ..................................................................................215
13.3.3 Where to Store VBS/VBA Scripts and Macros.....................................................217
13.3.4 SAS Code to Execute a Visual Basic Script .........................................................219
13.3.5 Build a Parameter-Driven VBS Script to Control the Execution of Excel .........220
13.3.6 Build a Control Macro for Each Excel Report......................................................223
13.4 Conclusion ................................................................................................ 229

13.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.

Computer systems and software packages like SAS and Excel are really great at crunching numbers. They
even consider letters to be a number. An eight-bit computer byte can contain a letter in the form of a
number between 0 and 255. Most software packages start out as stand-alone packages. After they mature
and become accepted, the software developers work to interface with other software systems. Operating
systems, on the other hand, need to communicate with the applications and must have methods of passing
data between the different software packages. On Windows, one of those methods of passing and
controlling data movement between computer systems, software packages, and the operator is called Visual
Basic Scripting (VBS). SAS can communicate with this feature through the “X” command.

The Visual Basic Scripting capability, included in the Windows operating systems, is very powerful. It can
open, manipulate, control, and close an Excel program and other Microsoft products. This power permits
libraries of Excel macros to be stored as individual code modules such as files with the extensions *.vba,
*.vbs, *.bas, *.cls, *.frm, *.frx, and others. When stored in a common directory, the code can be used by
any team member on a project. The ability to call these macros at any time allows the creation of a
standardized set of macros in a library for report formatting. Community-level routines can be stored in a
macro library for departmental use, while report-specific macros can be stored in separate code files,
thereby creating a departmental set of standards and reports that can survive personnel changes.

Table 13.1.1: Tools Described in Chapters 12, 13, and 14.

Tool Chapter Description

Personal Workbook Tool
SAS tool to run personal Excel
macros, already included in Excel
(under Macros ► Record Macro ►
Store macro in).

12 This tool uses the SAS “X” command to execute

Excel macros in your Personal Excel “Xstart”
directory. It allows Excel workbooks to be
delivered without embedded macros.