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

Microsoft Excel 2007 and 2010 have a feature that allows them to ignore DDE commands. So, if DDE does
not work, check to see if you have the proper settings to allow Microsoft products to process DDE
commands. This option is found by opening the “Excel Options” and looking under the “Advanced”
features. Also, DDE is a “real-time” application, so sometimes your SAS program has to wait. This can be
accomplished through the SAS SLEEP function. SAS Enterprise Guide can execute DDE commands.
However, SAS Enterprise Guide does not have access to the SAS “X” command; therefore, you must open
the Excel files manually before running DDE commands when using SAS Enterprise Guide.

11.3 Basic Concept of the DDE Client-Server Environment

11.3.1 How the DDE Client-Server Relationship Works
A client-server relationship as it relates to computers means that one computer or program issues
commands, or requests data, from another computer or program. That action in turn fills the request and
returns the data to the original computer or program. When SAS uses DDE to communicate with Excel, the
two programs are working together to transfer data or commands between SAS and Excel when they are
running programs. Here are some of the actions available as DDE commands:

● Use the SAS ”X” command to open an Excel workbook. (This is not really a DDE command, but
the workbook needs to be open, and SAS needs to know how to reference the Excel file,)
● Read data from specific cells in the Excel worksheet.
● Write data to specific cells in the Excel worksheet.
● Open a DDE session.
● Close a DDE session.
• Save the output Excel file.

11.3.2 General DDE Syntax and Options
The DDE filename has two main forms: the first form points to Excel cells; the second form issues
commands. The general syntax form is shown below.

Format 1: FILENAME fileref DDE 'DDE-triplet' <DDE-options>;

Format 2: FILENAME fileref DDE "excel|system";

Figure 11.1: DDE Filename Options for Reading and Writing Data.
Format 1 - FILENAME fileref DDE 'DDE-triplet' <DDE-options>;

This is the standard 8-character pointer used to identify any file that is not a
SAS file.

Fileref

DDE Is the engine name for the fileref to use for the output commands.

Has the general form of 'application-name|topic!item'. An example would be
'excel|[book1.xls]Sheet1!r1c1:r1c2'. This “Triplet” served as the pointer to a
location within the Excel file. The SAS “INPUT” and “PUT” commands start
the process of transferring the actual data.

The Application Name is
separated by “|” from the topic
value.

The example shows 'excel' as the
Application Name.

'DDE-TRIPLET'

The Topic Name is separated
from the “item” by a “!”.

The example shows
'[book1.xls]Sheet1' as the Topic
Name.