Using DDE to Read and Write to Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 171

11.4 List of User-Written SAS Macros That Can Enhance DDE
Processing
This section describes the SAS macros that were written as a companion to the topic. These macros are
available on the SAS Author page. What follows here are written descriptions of the macros, their
parameters, and their functions. These macros execute a DATA step, and therefore must be executed
between DATA steps and procedure calls within your code, rather than within them. The macros were not
tested with file or sheet names that had embedded blanks; this was left as an enhancement that you could
work on to make the macros your own. The file type (*.xls or *.xlsx) must be the same for the input and
output filenames because the file output formats are different and no checks are made to convert the Excel
file formats. Because of the complexity of these examples they will be stored on the SAS Author page as
Appendix C.

Figure 11.4.1: Summary of SAS Macros to Process DDE Commands.

Macro Name General Description

Start_Excel Use the SAS X command to open an Excel workbook.

Open an Excel DDE-triplet to issue Excel commands from SAS.

Open_cmd

Out_range Open an Excel DDE-triplet to transfer data between SAS and Excel.

Save_excel This macro will save an Excel workbook, overwriting an existing one.

Close_excel This macro will close an Excel workbook, preventing further updates

to this session.

SAS_2_Excel This macro uses all of the macros defined here to write out data from a

SAS dataset to an Excel workbook.

11.4.1 SAS Macro to Start Excel

Macro Name:
Start_Excel

FUNCTION:
Open an Excel workbook

PARAMETERS:
This macro accepts the following parameters:

● Full Excel Path and File name
• Length of time to wait for the Excel file to open

This macro accepts the full path and file name of an existing Excel file to open for use by SAS to interface
with Excel. The macro verifies that a path and file name were provided, but does not verify that the path or
file exists. The SAS code shows two possible locations of where the Excel executable file exists to aid the
user in finding the Excel application location.

When the “X” command is executed by SAS to start Excel, the SAS program pauses to allow Excel to
become fully operational before returning control back to the SAS runtime environment. If the default
waiting period of 8 seconds is not the right amount of time, then the “wait” parameter can be adjusted to
fine-tune the wait time needed.