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

11.4.4 SAS Macro to Save the Contents of an Excel Workbook

Macro Name:
Save_Excel

FUNCTION:
This macro issues an Excel “SAVE.AS” command after turning off the Excel error messages. This will
overwrite an existing Excel file without notifying you that the file was overwritten.

PARAMETERS:
This macro accepts the following parameters:

file_ref - FILEREF name up to 8-character SAS reference name

●

path_name - full path of Excel file

●

• file_name

- full name of Excel file

This macro accepts an open fileref name, which is used to issue commands to Excel via DDE. The
FILEREF must point to an “excel|system” triplet that is currently open. The macro verifies that a FILEREF
name was provided but does not verify that the FILEREF is valid.

The second parameter is the full path of an existing Excel file that is opened for use by SAS to interface
with Excel. The macro verifies that a path is provided, but does not verify that the path exists.

The third parameter is the file name of an existing Excel file that is opened for use by SAS to interface with
Excel. The macro verifies that a file name is provided, but does not verify that the file exists.

Result:
The contents of the Excel workbook are saved.

RESTRICTIONS:
Directory paths and file names are assumed to have spaces within the values and are quoted within the
macro. Enclosing parameters in quotes may produce unpredictable results.

11.4.5 SAS Macro to Close Excel Workbook

Macro name:
Close_excel

FUNCTION:
This macro issues an Excel “QUIT” command after turning off the Excel error messages. This will exit the
Excel file without notifying you that the Excel is closing.

PARAMETERS:
This macro accepts an open fileref name, which is used to issue commands to Excel via DDE. The
FILEREF must point to an “excel|system” triplet that is currently active. The macro verifies that a
FILEREF name is provided but does not verify that the FILEREF is valid.

Results:
This macro will close the Excel workbook to further activity.