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

SAS features we have seen:
● PROC EXPORT produces Excel files in native Excel format, but the number of columns and rows
that can be output are limited by the Microsoft JET and ACE engines. PROC EXPORT has
limited file formatting options.
● DDE can use a template to build an output Excel file with great (pre-assigned) formatting, but
running multiple reports can cause timing issues and the job stream may fail. The template *.xls
file may have Excel macros that need to be removed.
● ODS output files that are generated with TAGSET TEMPLATE processes (CSVALL, HTLM,
EXCELXP, and others) whether using PROC PRINT, PROC REPORT, or other processes
generate *.xml files (not *.xls files).
● Methods of placing graphs into Excel files are limited, and some formatting options are either not
available to SAS programs, or customers need to purchase software packages that may exceed
their needs.
● Many Excel formats are not available in SAS. While it is possible to generate code with SAS
PROC TEMPLATE, it is not for the faint of heart. Programmers unfamiliar with PROC
TEMPLATE may find the code difficult to update or write.
• Some companies do not use all SAS products that are available and therefore do not have access to
some of the more powerful features of SAS. The routines described here do not require SAS
Enterprise Guide, SAS Business Intelligence, JMP, or SAS Add-In for Microsoft Office.

New Concepts to Be Introduced
This chapter builds upon Chapter 12, which introduced the program model in which SAS produces an
output file with control information. Excel uses that information to determine which Excel macro to
execute. When Excel starts, the Excel macro is executed. The tool developed in this chapter is based upon a
model that passes control information to a Windows operating system script. That script then controls all of
the Excel processing. The script opens Excel, loads the macros, executes the macros, unloads the macros,
and closes the Excel workbook. This tool can even reformat the Excel output--say from an xml file--to an
xls or xlsx file format. The Excel macros that the tool uses can be stored in a departmental directory rather
than on an individual computer.

The Updated Program Model
●

Excel pre-processing setup
◦ Open Excel.
◦ Write/Store Excel macro in a disk file *.bas.

SAS processing
◦ Start SAS.
◦ Run SAS program.
◦ Define the file name and path of a Windows *.xbs script to execute.
◦ Define the file name and path of generated *.xml file.
◦ Define the file name and path of final *.xls output file.
◦ Define the file name and path of stored Excel macros (*.bas code).
◦ Define the name of the Excel macro to execute.
◦ Create *.xml file.
◦ Use “X” command to start running a VBS routine to process the generated workbook.

●