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

VBS routine
◦ Open Excel.
◦ Locate and load VBA code into Excel workbook.
◦ Execute the VBA code modules.
◦ Remove the VBA code modules.
◦ Close and convert the *.xml file to a *.xls (or *.xlsx) file.
◦ Terminate Excel.
◦ Terminate the VBS script.

●

This chapter will explain this new method and why building a system of directories, *.BAS files, and
*.VBS code is important. Then, I will show how to execute these macros from SAS to format Excel
reports. A basic working knowledge of Excel Visual Basic for Applications (VBA) is assumed. The rest of
the concept will be sketched out here to allow you to build and expand upon your project needs. Previously,
PROC EXPORT, ODS, DDE, the EXCELXP tagset template, and other methods of creating an Excel file
have been discussed and examples provided. The concepts shown here will allow you to process Excel and
other types of files in many different ways.

13.3 Guidelines for Building and Using a VBS/VBA Macro Library
When individuals have a tool that enables them to easily process multifaceted computer programs, they can
be more productive to their company. Linking tasks together with software can eliminate human interaction
with the data. This can eliminate errors related to manually entering or moving data. But, as we saw in
Chapter 12, some software can be restricted to use on one computer. The XLSTART directory used by
Excel is available only on the computer where Excel is installed. Hardware failure or upgrades can cause
the loss of all of these tools.

The process described in this chapter stores the Excel macros in a directory as separate code modules that
are used only while the macro is processing the workbook sheets. These macros can be stored in a Read-
only directory with limited Update access that will make them more secure and allow a wider access to the
report processing when new data is available. These VBA macros will allow you to take control of Excel or
other products as a computer programmer and make it do your bidding. The process is simple and can be
set up using the following general guidelines.

● Establish a disk directory where the VBA and VBS code modules can be stored. If multiple users
need access, it works best to have it available somewhere like a server location, where everyone
can read the directory.
● Publish a standard set of parameters that the VBS routine users can use to access the VBA code
modules.
● Unique code modules for individual reports can be placed into the directory and called from the
VBS script by using parameters set up by a SAS routine.
● SAS can use the “X” command to start the VBS routine and assign the parameter values to process
the report.
• SAS can process the data files to be used as input to the VBS control module. The output from
SAS can be any format that the VBA code modules can write.

13.3.1 Create Naming Conventions for Storing and Executing VBS/VBA
Macros
Let's think about naming conventions for a minute. We can ask the simple questions about them like a
newspaper reporter would ask--questions like “Who”, “What”, “When”, “Where”, and “Why”.