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

Tool Chapter Description

Macro Library Tool
SAS tool to run externally stored Excel
macros. You can use the “Macro
Library Tool” within Excel to take the
same macro you would have created in
the Personal Workbook Tool and
export it to a departmental library.

13 This tool uses the SAS “X” command and the

Windows operating system scripting language to
control processing of Excel macros. It allows
Excel workbooks to be delivered without
embedded macros.

Excel Workbook Tool
An Excel tool to store parameters for
SAS programs and either execute the
SAS code or place the code into a
directory for execution.
(My_Excel_Tool, available in the
example code and data folder on the
author's SAS Press page)

14 This Excel workbook tool will save parameters

for a SAS program and either execute or copy
the code to a directory. It uses features of the
other two tools and allows for storage of SAS
code in a production-type area so the original
code is not modified when the reports are
processed. .

13.2 Purpose
In Chapter 12 the concept of maintaining control of the execution of a task was introduced. The procedures
shown in Chapter 12 introduced the idea that a programmer can create a system of software commands to
control the capability of multiple software packages. The ability to control and cause multiple tools to work
together enables you as a programmer to create far more powerful tools using what you already know. This
chapter will build upon the concepts of Chapter 12 and enable us to create a system of tools that will be
expandable and flexible enough for departmental rather than simply individual use.

Here is the basic program model, as described in Chapter 12.

Excel pre-processing setup
◦ Open Excel.
◦ Write/Store Excel macro to process control information.

●

SAS processing
◦ Start SAS.
◦ Run SAS program.
◦ Set up control information for Excel.
◦ Create *.xml file.
◦ Use “X” command to start Excel and open the generated workbook.

●

Excel processing
◦ Locate the control information.
◦ Execute the Excel macro.
◦ Release control to Excel.

●