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

This is the name of the VBA code module to load into Excel and execute. The code described in Figure
13.3.7a also reads in a file of common reusable VBA macros. This other file holds reusable code that
can be called from the module in item ➐ above to aid programming by having standardized formatting
routines.

➑

This is the name of the macro to execute in the module described in item ➑ .

➒

➓ This command uses the SAS “X” command to execute the VBS routine listed in item ➍ .

13.3.5 Build a Parameter-Driven VBS Script to Control the Execution of
Excel
Let's dig a little deeper to see how those guidelines can be used to make a working system of VBS/VBA
macros. In Chapter 12 we saw the SAS “X” command demonstrate its power to control Excel. In addition
to running Excel, the SAS “X” command can execute nearly any operating system command by starting it
from within the SAS program code. Directories can be listed, other SAS jobs can be started, Excel
workbooks can be opened, and files can be copied or deleted. Now we will discuss how a Windows built-in
operating system function can be used to control Microsoft products like Excel and Word.

The VBS scripting language is similar to the VBA code language, but it does have a few minor differences.
You can execute the commands stored in a VBS code module (any_file_name.vbs) simply by double-
clicking on the filename. For instance, if you double-clicked a file named “broken_command_file.vbs” that
contained the words “this does not do anything and produces an error message”, the windows operating
system would produce something like the following output.

Figure 13.3.6: VBS Error Message Output Window.

We will look at the details of a VBS script shown in Figure 13.3.7a a little later in the chapter. In Chapter
12, we saw that a VBA macro can be built by recording the macro from within the Excel workbook. These
recorded macros generally have a lot of default commands embedded into the macro. Some of these
commands are not required for a final macro. A recorded macro may also have a lot of workbook- and
worksheet-specific cell references that may be too specific for a general purpose macro.

SAS has the feature of being a programming language, which prepares the code to execute and then starts
running at the first executable instruction. What that means to me is that code has to be defined before it
can be used. Code is presented to SAS as a text stream, and each macro, DATA step, and procedure call
must be executed one group at a time. Code at the bottom of the text file is not executed until the step it is
contained within is executed, usually at end of the job. However, object-oriented programming languages
like VBA read in the whole set of code routines, compile them, and then pass control of the program to a
routine that waits for something to happen. This is similar to a user moving a mouse pointer, clicking on a
menu, or pressing a key on the keyboard: each object has its own list of things you can make it do or do to
it. These are beyond the scope of this topic, but some simple things will be explored to show how to start
building a set of your own VBA macros to use to create your reports.