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

Now that we know the “Hello World” project can be done, we can move forward and take some of the
manual steps out of the process.
12.4 Automate the Tool So That SAS Creates a Formatted Excel Output
Workbook
The reason that computer programmers write programs is so that they do not have to do things manually.
So, the next question becomes “How do we get this to run seamlessly?” Well, in order for us to take control
so that we can make Excel do what we want, we need to find:
“The First Place a Programmer Can Get Control of Excel”
It turns out that once your PERSONAL.XLSB (or .xls) Macro Workbook is created, it is always the first
workbook opened when Excel is started. The PERSONAL.XLSB (or .xls) workbook also contains our
modules with our macros and an Excel structure called “ThisWorkbook” (see Figure 12.3.5). The VBA
code module “ThisWorkbook” is given control of the Excel start-up activities. If there is a subroutine called
“Workbook_open”, is executed. This is where a programmer can capture control of Excel. The VBA code
shown will reference only the “PERSONAL.XLSB” Excel files.
For this “Hello World” project, we will insert code into the “Workbook_open” subroutine to execute our
macro to write “World” in cell B2 of the current sheet. See Figure 12.4.1 for the code.
12.4.1 Eliminate the Manual Steps from the Processing
The VBA code in Figure 12.4.1 defines a counter and two character (string) variables, and then checks the
name of all open workbooks. If the name of the workbook is, in this case, “PERSONAL.XLSB”, then no
action is taken. If any other workbook is open, then Macro1 would execute and place the word “World”
into cell B2 of the first worksheet. When opened from our SAS program, the generated workbook opens
and the macro executes.
Figure 12.4.1: VBA Code to Run the Workbook_open Subroutine.