Database Reference
In-Depth Information
The object is to send information to Excel to tell Excel what to do next. It is nice to have a constant
location; I always use cell “A1” of the first sheet of the Excel workbook, and bracket the code with
characters not normally found in titles or variable names. The same thing can be accomplished by changing
the variable name to “_graph_1_Region” for the first column of the first sheet. Your Excel macro will need
to remove any control information that you send for the Excel macro.
When we run this SAS code and send the data to Excel, our new output Excel workbook looks something
like Figure 12.4.7b.
Figure 12.4.7b: Excel Results for Output When New SAS Routine Is Executed.
Since we have not re-written the Excel “Workbook_open” macro, we still find the word “World” in cell B2
of the Excel document. Remember that the PERSONAL.XLSB (or *.XLS) workbook is always opened.
Since this workbook was opened with the SAS “X” command, the Excel MACRO1 was also executed. You
will see the messages boxes described in Figures 12.4.3 to Figure 12.4.6 also were visible. Now that we are
confident that our little message boxes are doing their job, we will eliminate them. If you use the code by
cutting or copying and then pasting the code, you may not get exactly what you want. This process does
not always convert the quotation marks correctly. You may need to retype them.
12.4.3 Create an Excel Macro to Process the Output SAS File
Now we have something automated, even though we still have to press Enter when the text boxes appear.
We now have a procedure that starts by running SAS code and ends by displaying an active Excel
workbook that not only has our data, but has made a change to our data. But, since we do not want to
change cell “B2” to the word “World” for every workbook we create, let's change the VBA code for the
 
Search WWH ::




Custom Search