Building a System of Excel Macros Executable by SAS - Exchanging Data Between SAS and Microsoft Excel - page 190

Table 12.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.

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

12.2 Purpose
On a computer, a “Hello World” project is a proof-of-concept program demonstrating the ability to do
something like write to a disk and read it back. What I will show is the ability to retain control of program
execution starting within a SAS program and continuing after Excel begins processing. That will be
followed by Excel processing the code I want to execute. This ability to establish a process in which you
can maintain control is an advantage you can exploit. It gives programmers a path from which they can
branch out and do more.

12.3 General Design of a Tool to Control Excel Macros from SAS
The code we will develop here is a push-button process. There are several steps that need to be
programmed to make the code work. The object of the tool is to run a SAS program, have the SAS program
load a file into Excel, and take control of processing the workbook and all of its worksheets before Excel
allows the user to modify anything. This is the dream of every programmer who has ever lost a leading zero
when Excel opened his or her workbook. But, enough of the dreaming. Let's define this concept and put it
into action.

Here we will describe two processes. The first is the “Hello World” program, and the second is a real-world
application of the steps. The task of having SAS create an Excel workbook is simple when you use ODS.
The tagset “EXCELXP” is designed directly for this task. The next part is getting Excel to do what you
want it to do. This is accomplished with Excel macros that you store on your computer, not in the generated
workbooks. Table 12.3.1 shows a list of the tasks needed for this “Hello World” project.