Excel DDE

What is OPC ?  OPC stands for Openness, Productivity, and Connectivity. OPC is a specification that has been developed over the last 8 years by a team of 150 companies and represents a shining example of how industry specification organizations can be effective.  The OPC Foundation is an independent organization that is supported by the fees payed on a equitable basis by the members of the foundation. Software Toolbox was a Charter Member of the OPC Foundation.

The goal of the OPC specification is to provide an open, flexible, plug-and-play software standard for modular software interoperability in the automation industry. The specification is based upon the Microsoft Component Object Model (COM) and addresses the specific needs of the automation industry such as data access (i.e. drivers), alarming, historical data access and trending, batch, and more. You may ask “what about ActiveX – I thought it was the standard?” Well, OPC takes the concepts of properties, methods, and events that you learned about for ActiveX controls (see related articles in Object Technology Center) but goes a step further to address automation specific issues and actually provide specific names for many of the interfaces based on the application type. For some products, ActiveX will be all you need, for others you may choose to use an OPC compliant product.

The long-term benefits for the user of OPC come from the ability to pick best-of-breed application modules such as trending, alarming, graphics, etc. from various sources and bring them together with common interfaces. For example, your graphics environment could function as an OPC client, getting it’s data from an OPC data server connected to your PLCs. The trending package you choose could function as an OPC client to the graphics package to get it’s data, which also happens to be an OPC server! Or it could go direct to the OPC data server.  Although this concept may sound like the old DDE server model that has been around since the late eighties, but it is not. When DDE was invented, COM and the whole 32 bit world did not exist.  The OPC specification is built upon COM and uses Windows technologies to provide superior performance and robustness that will never be found when using DDE. Simply put, 15 years of technological advancements since DDE was first used (circa 1988 in Windows 2.0) make a huge difference!

So as you begin looking at manufacturing software packages, whether they are HMI or others, find out if they are OPC compliant. If they are, you’ll be able to shop Software Toolbox and find all the OPC products that you need in one place. To learn more, visit our website as we’ll be adding more technical articles there as OPC becomes more and more popular.

ActiveX — you have probably heard the term a few times at this point, haven’t you. Most people hear the term ActiveX as it relates to their use with their Internet browser software.  There’s more to the story. ActiveX controls are the present and the future of modular software objects in the marketplace.

To learn what an ActiveX control is, let’s first do an object-based software primer. For a moment, think of all the pieces of software running on your PC as “black boxes”. We’ll call those black boxes objects. Don’t worry about what they do, for now. All you know is that you setup certain parameters, tell the software to do something, and you get results. Well if all these pieces of software are going to play together nicely, they all need to be setup, activated, and provide results in a consistent fashion.  Microsoft’s Component Object Model (called COM for short), the basis for all of Windows95 and NT, defines the way these software objects can interact as follows:

* Properties — These settings define what the software object will do when you say “go” — think of properties like setup parameters on a machine that can make a variety of parts.
* Methods — Once you’ve setup the properties, you need to say “go do something” to the object. Like a machine that may have several buttons an operator can push to activate it, an object can have several methods that cause different things to happen. When you call a method from another program, depending on the method it looks at some or all of the properties, and executes some code inside that does something, which leads us to the results.
* Events — When you set properties and invoke a method, you expect something to happen. You expect some output or results from your object. The results are called events. When the event happens, the object may display something on the screen, it may just pass some parameters back to your other program as a result, or it may do nothing but say “I’ve done what you said, if you want the data, come and get it”.

An ActiveX control is an implementation of a software module based on the COM specification. The ActiveX control specification defines these standard interfaces. Take your Excel97 spreadsheet as an example. Did you know that Excel itself and every workbook you create is really just a big collection of objects? Every cell is an object. If you add a chart, that’s an object. Every worksheet in the workbook is an object. When you click on the pull down menus and change formatting of text, you are setting properties on objects. When you print a worksheet, you are calling Excel’s print method! It’s really that simple.

Sample Object Block DiagramFor an industrial example, take a look at the ActiveX Control at the right, a PLC communications driver ActiveX control. You have to admit, if you know anything about PLC communications, the interface names used are pretty intuitive. In general, ActiveX controls are not separate applications. They are used inside of some other application such as Excel97, Visual Basic, an HMI package, or an Internet browser. The application that the ActiveX control plugs into is called the container application. The container application and the ActiveX control interact through the standard property, method, and event interfaces described above.

So the next time you’re looking at doing a software application, keep ActiveX controls in mind. They could save you hundreds of hours of work by allowing you to purchase at an affordable price a software module that someone else developed and easily interface to the module using a standard interface.

We are often asked the question “which is better – a DDE server or an ActiveX control ?” The answer depends upon what other software applications you are using and your application requirements.

DDE Servers have been around since the late 1980’s (see related article) as a means of connecting PLCs to Windows-based software. Wonderware pioneered the use of DDE servers in their InTouch applications and improved upon Microsoft’s standard DDE with FastDDE to provide faster, more reliable throughput. In general, DDE servers are good if you are using an existing HMI package that supports DDE or one of its variants FastDDE or AdvanceDDE. The HMI software is designed to work with DDE servers and setups are generally pretty easy. A DDE server is also useful if you need some simple data brought into an Excel spreadsheet or other DDE client application and need a simple to setup user interface.

All the ease and friendliness of DDE requires a price though, particularly if you are developing an HMI application using Visual Basic. First, although Visual Basic includes native support for DDE, you must have an object such as a text box on a form for every data point you want to gather from the DDE server. This is because VB uses data binding to “bind” to a value in the DDE server. For a small amount of data, it’s a convenient and easy way to set things up. But if you need a lot of data or you just want to gather data for logging to a database, then this approach can quickly become cumbersome. Using DDE requires a great deal of system resources. You have the DDE client application, the DDE server application, and the DDEML.dll program that Windows uses to tie the two together. The DDE server maintains in memory a list of all the items assigned to each topic and any client application that needs item data. Each time an item is added or removed to the topic list in the DDE server, the server must reoptimize the topic and link lists. With Visual Basic, you have a limited number of open topics allowed, so you may find yourself opening and closing topics, which causes the DDE server application to slow down. This constant maintenance work performed by the DDE server consumes system resources. To complicate matters further, the path a data request takes from the client to the server and back has numerous steps and bottleneck points. The DDE client requests data and the request is routed by the operating system to the DDEML.dll which in turn sends the request via another message to the DDE server, which in turn must get the data from the PLC. When the server has the data, it sends it to all clients that requested it via the DDEML.dll which in turn sends the data to the DDE clients. All the while, this is happening in separate process spaces in the operating system, which inherently slows things down.

By contrast, if you use an ActiveX control with your Visual Basic (VB) application, things are significantly streamlined. First, you only have one process space to be concerned with. The ActiveX control gets loaded into memory automatically when your VB application loads. When your application needs the ActiveX control to do something, it makes the call to the control directly. There are no intermediaries or messaging that your data request gets routed through. The ActiveX control can get the data and hand it right back to your application without any intermediaries. This inherently saves a great deal of time and system resources. Also, the ActiveX control does not have to deal with complicated topic and item lists to maintain and constantly re-optimize. It gets the data when told to and returns it to the calling instance of the ActiveX control in an expedient manner.

So if you’re building an application using Visual Basic and have the choice to use an ActiveX control, you’ll be rewarded with a clean, fast application. If you need simple PLC data access for your existing HMI or office applications, then the DDE server will rise to the task and serve you well.

If you’ve been around Human-Machine Interface (HMI) software much you may have heard the term DDE used in conjunction with device communications. DDE stands for Dynamic Data Exchange. DDE is a method for exchanging information between applications running on the Windows operating system, all the way back to Windows 2 .0! That’s right, there was a Windows 2.0 back in the late 1980’s and believe it or not it ran just fine on a 20 Mhz 286 PC and was screaming fast on a 386! One of the earliest demonstrations of DDE was using it to exchange data between Word and Excel.

As the first Windows-based HMI software came to market between 1989 and 1991, the developers chose to use DDE to connect the HMI software to their device drivers. The device driver that connected to your PLCs or process equipment ran as a separate application called a DDE Server. The HMI software was a DDE client application that requested and received data from the DDE Server. By having the DDE Server as a separate piece, it was very easy for 3rd parties to create drivers that would work with any HMI or other software application that used DDE. We remember the amazement in people’s faces the first time they saw an Excel spreadsheet connected to a PLC through a DDE server back in the early 1990’s. The DDE client application accessed data by specifying the request in the format of Application|Topic!Item, where Application was the name of the DDE server .EXE program, the Topic was a user configured name representing a group of data, such as a PLC, and the Item was a specific tag or data point in the PLC.

DDE by itself has some shortcomings, primarily performance. The communications between the DDE client (HMI software, Excel, etc) and the DDE Server (the PLC driver) have to pass through a software message routing mechanism in Windows, which back before Windows 95 and NT was not very fast, and even today by itself is slow with large data blocks. Data throughput speed and volume of data was limited by this factor. To address this issue, Wonderware developed and released FastDDE to the market along with a toolkit for building FastDDE servers. The extensions to DDE developed by Wonderware in FastDDE gave the technology a huge boost. Later, in the mid 1990’s, Rockwell Software released AdvanceDDE as their performance boosting version of DDE.

Along with these performance enhancing evolutions came NetDDE. NetDDE allows a client running on one PC to access data from a DDE server running on another PC! At the time this feature came on in the early 1990’s, it was revolutionary. All users had to do was specify a machine name on the network in front of the Application|Topic!Item naming convention.

Today, almost all DDE servers support at least 2 of the 3 if not all 3 of the forms of DDE: DDE (also called CF_Text or XL_Macro), FastDDE, and AdvanceDDE in one driver, giving the user the maximum of flexibility. Software Toolbox offers a variety of DDE servers.

Foreword

Definition of Dynamic data exchange: Dynamic data exchange (DDE) is a form of interprocess communication that uses shared memory to exchange data between applications. Applications can use DDE for one-time data transfers and for ongoing exchanges and updating of data.

In this report, the use of DDE functions will be made clear and a detailed description will be given too. You need to use a lot of functions to let two or more applications work together, as one function needs another one.

This report also describe all the functions in detail. The syntax, return values and the arguments will be explained with a little description with each argument.

1. Introduction to DDE

DDE is an interprocess communication method that allows two or more applications running simultaneously to exchange data and commands. This means that two or more applications can work together to create a new application One way to enable DDE is by use of the function DDEAdvise ( )

1.1 DDEAdvise ( )
DDEAdvise( ) is used to create a notify link or an automatic link to an item name in a server application. When a notify link is created with DDEAdvise( ), the server application notifies that the item name has been modified. If an automatic link is created, the server application notifies that the item name has been modified and passes the new data to Visual C++. You can also use DDEAdvise( ) to turn off notification from the server. Before you can create a link, you must establish a channel to the server application with DDEInitiate( ).

1.2 DDEInitiate( )
DDEInitiate( ) establishes a DDE channel between Visual C+++ and a DDE server application. Once a channel is established, Visual C++ can request data from the server by referring to the channel in subsequent DDE functions. Visual C++ acts as the client, requesting data from the server application through the channel. If the channel is successfully established, DDEInitiate( ) returns the channel number. Channel numbers are non-negative, and the number of channels you can establish is limited only by your system resources. DDEInitiate( ) returns –1 if the channel cannot be established. If the server application isn’t open, Visual C++ asks if you would like to open it. If you choose Yes, Visual C++ attempts to open the application. (You can use DDELastError( ) to determine why a channel cannot be established.) To avoid being asked whether you want to open the application, set the DDESetOption( ) SAFETY option. You can also use RUN with the /N option to start the application. A channel can be closed with DDETerminate( ).

1.3 DDETerminate( )
If the channel is successfully closed, DDETerminate( ) returns true (.T.). If the channel cannot be closed, DDETerminate( ) returns false (.F.). Be sure to close channels as soon as they are no longer needed to conserve system resources. All channels are automatically closed if you exit Visual C++ by choosing Exit from the File menu

1.4 DDERequest ( )
Another way to enable DDE is by using DDERequest. Before you can request data using DDERequest( ), you must establish a channel to the server application with DDEInitiate( ). If the request for data is successful, DDERequest( ) returns the data as a character string. If the request fails, DDERequest( ) returns an empty string and DDELastError( ) returns a nonzero value. If you include the asynchronous user-defined function cUDFName, DDERequest( ) returns a transaction number if successful, or –1 if an error occurs.

1.5 DDEExecute ( )
A third method to enable DDE is DDEExecute. The command sent with DDEExecute( ) must be understood by the application. Before you can execute the command, you must establish a channel to the server application with DDEInitiate( ). For example, Microsoft Excel has an extensive set of macro commands, including DDE commands that let you request data from Visual C++ from within Microsoft Excel. If you establish a channel to Excel, you can then use DDEExecute( ) to send macro commands to Excel from within Visual C++ If the receiving application successfully executes the command, DDEExecute( ) returns true (.T.). If the receiving application cannot successfully execute the command or if the channel number you include is not valid, DDEExecute( ) returns false (.F.). If the optional asynchronous user-defined function cUDFName is included, a transaction number is returned. If an error occurs, DDEExecute( ) returns –1.

1.6 DDELastError ( )
You can use DDELastError( ) to help determine the cause of an error when a DDE function doesn’t execute successfully. DDELastError( ) returns 0 if the last DDE function executed successfully. It returns a nonzero value if the last DDE function was unsuccessful. The following table lists the error numbers and their descriptions (figure number 1).

Error number
Description

1 Service busy

2 Topic busy

3 Channel busy

4 No such service

5 No such topic

6 Bad channel

7 Insufficient memory

8 Acknowledge timeout

9 Request timeout

10 No DDEInitiate( )

11 Client attempted server transaction

12 Execute timeout

13 Bad parameter

14 Low memory

15 Memory error

16 Connect failure

17 Request failure

18 Poke timeout

19 Could not display message

20 Multiple synchronous transactions

21 Server died

22 Internal DDE error

23 Advise timeout

24 Invalid transaction identifier

25 Unknown

Figure 1: Error numbers

2. Explanation of the DDE functions
This paragraph will explain the different functions by giving an example of the use of the functions. For each function will the syntax, return value and the arguments be given.

2.1 DDEAdvise ( ) details Syntax

DDEAdvise(nChannelNumber, cItemName, cUDFName, nLinkType)

Return values

Logical

Arguments

nChannelNumber

Specifies the channel number.

cItemName

Specifies the item name. For example, Microsoft Excel uses row and column notation to refer to cells in a worksheet. The item name R1C1 designates the cell in the first row and first column of the worksheet.

cUDFName

Specifies the user-defined function that is executed when a notify link or an automatic link is established and the item cItemName is modified. When the user-defined function is executed, it is passed the following six parameters in the order given below:

2.2 DDEInitiate ( ) details Syntax

DDEInitiate(cServiceName, cTopicName)

Return values

Numeric

Arguments

cServiceName

Specifies the service name of the server application which, in most cases, is the name of the executable file without its extension. If you are establishing a channel to Microsoft Excel, cServiceName is Excel.

cTopicName

Specifies the topic name. The topic is application-specific and must be understood by the application. For example, one topic supplied by most DDE servers is the System topic. See the application documentation for the service and topic names supported by the application.

2.3 DDETerminate details Syntax

DDETerminate(nChannelNumber | cServiceName)

Return values

Logical

Arguments

nChannelNumber

Specifies the channel number to close.

cServiceName

Specifies the service name to close.

2.4 DDERequest ( ) details Syntax

DDERequest(nChannelNumber, cItemName [, cDataFormat [, cUDFName]])

Return values

Character

Arguments

nChannelNumber

Specifies the channel number of the server application.

cItemName

Specifies the item name. The item name is application-specific and must be understood by the application. For example, Microsoft Excel uses row and column notation to refer to cells in a worksheet. The item name R1C1 designates the cell in the first row and first column of the worksheet.

cDataFormat

Specifies a format for the data requested. The default format is CF_TEXT. In this format, fields are delimited with tabs and records are delimited with a carriage return and a line feed.

cUDFNameAllows an asynchronous data transfer. If you omit cUDFName, Visual C++ waits for the data from the server for the period specified with DDESetOption( ). If you specify the name of a user-defined function with cUDFName, Visual C++ continues program execution immediately after the request is made. When the data is available from the server application, the user-defined function specified with cUDFName is executed. The user-defined function is passed six parameters in this order: (see figure number 2)

Parameter
Contents

Channel Number
The channel number of the server application.

Action
XACTCOMPLETE (successful transaction). XACTFAIL (failed transaction).

Item
The item name; for example, R1C1 for a Microsoft Excel worksheet cell.

Data
The new data (REQUEST) or data passed (POKE or EXECUTED).

Format
The data format; for example, CF_TEXT.

Transaction Number
The transaction number returned by DDERequest( ).

Figure 2: cUDFName parameters

If the transaction fails, you can use DDELastError( ) to determine why it failed. When you include cUDFName, DDERequest( ) returns a transaction number equal to or greater than 0 if successful, or –1 if an error occurs.

2.5 DDEExecute ( ) details Syntax

DDEExecute(nChannelNumber, cCommand [, cUDFName])

Return values

Logical

Arguments

nChannelNumber

Specifies the channel number.

cCommand

Specifies the command you want to send to the other application. The format of the command is determined by the application you are sending it to. Consult the application’s documentation for the correct syntax.

cUDFNameAllows asynchronous command execution requests. If you omit cUDFName, a client application waits for the period specified with DDESetOption( ). If you specify a user-defined function with cUDFName, client program execution continues immediately after the command execution request is made. When the server application finishes executing the command, the user-defined function you specify with cUDFName is executed. The user-defined function is passed six parameters in the order shown in the above table (see figure number 2).

2.6 DDELastError ( ) details Syntax

DDELastError( )

Return values

Numeric

Afterword
As you could read in this report, the use of DDE has several advantages and disadvantages. One of the main advantages is that the use of DDE results in applications using DDE for one-time data transfers and for ongoing exchanges and updating of data.

When an application is not responding, the function DDELastError will give an error number, of which a description can be found in the following table: (see figure number 1). One minor problem is that not al error descriptions are clear. For example: error number 13 / bad parameter. Usually no further description is given so you have to find out for yourself what to change.

Read-Only Open: Excel DDE Command

1. Try this:

channel = DDEInitiate(”Excel”, “System”)
result = DDEExecute(channel,’[open("temp.xls",,TRUE)]‘)
DDETerminate(channel)

2. I figured it out by grabbing the Excel 4.0 Macro function help file from the microsoft site. The Excel Macro language is 99% the same as the DDE language.

Here’s a link to the file on the MS site:

http://support.microsoft.com/download/support/mslfiles/Macrofun.exe

Search for open. The DDE commands are the same as the macro commands. It’s the stuff that goes inside the square brackets.

An OPC Server is a software application that acts as an API (Application Programming Interface) or protocol converter. An OPC Server will connect to a device such as a PLC, DCS, RTU, or a data source such as a database or User interface, and translate the data into a standard-based OPC format. OPC compliant applications such as an HMI, historian, spreadsheet, trending application, etc can connect to the OPC Server and use it to read and write device data. An OPC Server is analogous to the role a printer driver plays to enable a computer to communicate with an ink jet printer. An OPC Server is based on a Server/Client architecture.

Here is a PDF file about Using DDE to connect SAS with Excel

http://www.exceldde.com/Using_DDE_to_connect_SAS_with_Excel.pdf

DDE (Dynamic Data Exchange) allows one to import or export data between PC applications. In this document I’ll focus primarily on how it works between SAS and Excel and briefly describe data exchange between SAS and WORD. DDE will not only allow you to read data from an Excel spreadsheet into a SAS dataset but also will place information from in a SAS dataset into an Excel spreadsheet with more control over the placement of data and formats than available with PROC EXPORT. You can also write Excel commands  to specific cells in a specified worksheet from the DATA step.

Please note that Excel must be running and the existing workbook open from which data are to be read or to which data are to be written. This is the opposite of the requirement for importing data with PROC IMPORT (which assumes the Excel workbook is closed).

* How to open an unnamed excel workbook with an empty worksheet;

options noxwait noxsync;

x ‘”c:\program files\microsoft office\office10\excel”‘;

data _null_;
x=sleep(2);
run;

filename cmd dde ‘excel|system’ ;

Read Data from Microsoft Excel with DDE

This section demonstrates how SAS can read data from an Excel workbook into a SAS dataset through DDE. It is more flexible than PROC IMPORT in that it allows you more flexibility to specify the desired range from a worksheet. It also allows you to write commands and formulas and enter them into specific cells of a worksheet.

In the following FILENAME statement, the DDE link is first established using Microsoft Excel to a workbook called pt.xls that contains a
worksheet named zr in the lower left-hand corner. The statement also specifices that data are to be read from rows 1 through 22 and columns 1 through 10 (note that variable names, as described earlier, are assumed to be placed in the first row of the specified range).

FILENAME dat DDE ‘Excel|C:\regress\zeros\[pt.xls]zr!R1C1:R22C10′;
DATA zr;
INFILE dat missover;
INPUT id $ z1 z2 z3 z4 z5 z6 z7 z8 yPT;
RUN;

PROC PRINT DATA=zr; run;

The string after DDE in the FILENAME statement is called the DDE triplet which consists of:

application | topic ! item.

The specific form of the DDE triplet differs across applications. Fortunately, there is a simple way to determine the contents of the DDE triplet without typing them. First, open the Excel spreadsheet you want to read, highlight the rows and columns to transport to SAS and copy them to the clipboard. Then go into the PC SAS menus and choose

Solutions – Accessories – DDE Triplet

The dialog box that appears contains the DDE triplet. Copy it from the dialog and add it between the quotes on the filename statement
(right-click on the shaded area with the mouse and then click Copy with the left side of the mouse). The triplet actually tells SAS to read data from the spreadsheet beginning in row 2 since the first row is assumed to contain the variable names.

The MISSOVER option on the INFILE statement prevents SAS from going to a new input line if it does not find values in the current line for all the INPUT statement variables. The MISSOVER option specifies that when the INPUT statement reaches the end of the current record values that are
expected but not found on that record are set to missing.

Reading Missing Data

Missing data or blank cells can pose a problem when they are read from an Excel spreadsheet with DDE. In this example, data will be read from
columns 1 through 3 and rows 2 through 15. Some of the data cells may be blank. Also, some of the character data have blanks. Both situations may require special handling when reading with DDE. The first few rows of the example data look like:

row  name    town         team
2   John    Raleigh      Cardinals
3   Jose    North Bend   Orioles
4   Kurt    Yelm         Red Sox
5   Brent                Dodgers
…

The SAS code that will read these data correctly into a dataset. Note the INFILE statement is structured exactly as if one were reading a text file.

FILENAME mydata DDE ‘excel|sheet1!r2c1:r15c3′;

DATA in;
INFILE mydata NOtab dlm=’09′x dsd missover;
INFORMAT name $10. town $char20. team $char20.;
INPUT name town team;
RUN;

PROC PRINT DATA=in; run;

The INFILE statement includes the NOtab option which tells SAS to not convert tabs sent from the Excel worksheet into blanks. Therefore, the tab character can be used as the delimiter between data values. The DLM=option specifies the delimiter character: ‘09′x is the hexadecimal
representation of the tab character. The dsd option specifies that two consecutive delimiters represent a missing value. The default delimiter is a comma.

The MISSOVER option prevents SAS from going to a new input line if it does not find values in the current line for all the INPUT statement variables. With the MISSOVER option, when an INPUT statement reaches the end of the current record, values that are expected but not found are set to missing.

The INFORMAT statement forces the DATA step to use modified list input, which is crucial to this example. If you do not use modified list input,
you receive incorrect results. The necessity of using modified list input is not DDE specific. You would need it even if you were using data in a
CARDS statement, whether your data were space or comma delimited.

Data Transfer from SAS to Excel with DDE

Dynamic Data Exchange (DDE) allows full programmatic control over exactly where data are to be inserted in the Excel worksheet. Values from SAS datasets may be placed into either a blank Excel workbook or into an existing one.  DDE allows you to specify the name of the workbook, the
worksheet that is to receive the data, and the data range through the upper-left to lower-right designation of the data-block. The worksheet
cells can even be formatted with font, font-size, and column width. You can also apply Excel commands from a DATA step.  The following examples show only a few of the features this method of data transfer offers.

In order to submit these commands, the Excel must be running, the respective workbook opened, and the designated worksheet assigned the
given name in the tab found in the lower left corner. Worksheets are given default names of Sheet1 – Sheetn (depending on the number of worksheets available). You need to enter these particular worksheet names if you haven’t assigned your own.

In this first example, the DDE link is established to Microsoft Excel by sending data to SHEET 1 of a yet unnamed workbook with sheet1 opened (see next example for an existing workbook and worksheet). Variable names are placed in the first row and the data are placed into rows 2-21 and columns B, C, and D (i.e., c2,c3,c4).

FILENAME data DDE “EXCEL|sheet1!r1c2:r26c4″;

DATA _null_;
FILE data;
PUT ‘x’ ‘09′x ‘y’ ‘09′x ‘z’; *place variable names in row 1;
DO i=1 to 20;
x=3*ranuni(i)-2;  y=x+10;  z=x/2;
PUT x y z;
END;
RUN;

If two or more EXCEL workbooks are open simultaneously, or if you want to write data to an open workbook with a specific worksheet name, the drive and path are entered along with the name of the workbook placed in square brackets (be sure it has an .xls extenstion) next to the worksheet name followed by an ! as shown in the next example.

*                 ‘Excel|drive:<Path>[<file_name>.xls]<sheet>!range’;

FILENAME rndm DDE ‘Excel|C:\sas\excel\[brand.xls]sales!R2C1:R10C5′;

DATA _null_;
Brand=1; month=’Jan’; year=1999; product=’Shoes’;
FILE rndm;
PUT ‘Brand’ ‘09′x ‘month’ ‘09′x ‘year’ ‘09′x ‘product’;
PUT Brand month year product;
RUN;

The NOTAB Option

SAS automatically places a tab character between adjacent variables when data are transmitted across the DDE link. When the NOtab option is entered at the end of the FILENAME statement that specifies the DDE keyword, SAS accepts character delimiters other than tabs between variables.

The NOtab option also can be entered to store character strings which include embedded blanks, into one spreadsheet cell. For example, when a
link is established between SAS and the Excel application each word of a character string is normally stored in a single cell. To store the entire
string, including embedded blanks in a single cell, enter the NOtab option as shown in the following example:

/* Without the NOTAB option,
column 2 contains ‘test’ and column 3 contains ‘one’
column 4 contains ‘test’ and column 5 has ‘two’*/

FILENAME rndm DDE ‘Excel|C:\sas\excel\[brand.xls]brnd!R1C2:R2C5′;

DATA _null_;
file rndm;
a=’test one’; b=’test two’;
put a $9. b $9.;
run;

/* You can enter the NOTAB option to store each variable in a separate cell.
To do this, you must force a tab (’09′x) between each variable, as in the PUT statement.
After running this DATA step, Column 2 contains ‘test one’ and column 3 contains ‘test two’.*/

FILENAME rndm DDE ‘Excel|C:\sas\excel\[brand.xls]brnd!R1C2:R2C5′ NOtab;

data _null_;
file rndm;
a=’test one’; b=’test two’;
put a $9. “09″x b $9.;
run;

/* Microsoft defines the DDE topic SYSTEM to enable commands to be invoked within Excel.*/

Suppose you want to write a formula to a specific Excel cell with DDE in SAS. For testing, write the formula 1+1 in cell A1, so that Excel
recognizes it as a formula and displays “2″.

Excel formulas start with an equals sign (=).

OPTIONS NOxwait xsync;

FILENAME cmds DDE ‘Excel|system’;

%let openfile = ‘[open("C:\sas\excel\test.xls")]‘;

DATA _null_; FILE cmds; PUT &openfile; RUN ;

FILENAME ddedata DDE ‘Excel|Sheet1!R1C1:R1C1′;

data _null_; file ddedata notab; put ‘=1+1′; run;
data _null_; file cmds; put ‘[quit()]‘; run ;

************************ ;

FILENAME cmds dde “excel|system”;

/* SAS PUT statements in a DATA step execute Excel macro commands */

data _null_;
file cmds;
put ‘[SELECT("R1C1:R25C2")]‘;
put ‘[SORT(1,"R1C1",1)]‘;
put ‘[SAVE()]‘;
put ‘[QUIT()]‘;
run;

DDE allows you to specify the name of the workbook and worksheet to receive the data. In this example the upper-left cell of the data-block
was chosen.

Dynamic Data Exchange was first introduced in 1987 with the release of Windows 2.0. It used the “Windows Messaging Layer” functionality within Windows. Therefore, DDE continues to work even in modern versions of Windows. Newer technology has been developed that has, to some extent, overshadowed DDE (e.g. OLE, COM, and OLE Automation), however, it is still used in several places inside Windows, e.g. for Shell file associations and for the copy, cut and paste functions.

The primary function of DDE is to allow Windows applications to share data. For example, a cell in Microsoft Excel could be linked to a value in another application and when the value changed, it would be automatically updated in the Excel spreadsheet. The data communication was established by a simple, three-segment model. Each program was known to DDE by its “application” name. Each application could further organize information by groups known as “topic” and each topic could serve up individual pieces of data as an “item”. For example, if a user wanted to pull a value from Microsoft Excel which was contained in a spreadsheet called “Book1.xls” in the cell in the first row and first column, the application would be “Excel”, the topic “Book1.xls” and the item “r1c1″.

Note: In DDE, the application, topic and item are not case-sensitive.

A common use of DDE is for custom-developed applications to control off-the-shelf software. For example, a custom in-house application might use DDE to open a Microsoft Excel spreadsheet and fill it with data, by opening a DDE conversation with Excel and sending it DDE commands. Today, however, one could also use the Excel object model with OLE Automation (part of COM). The technique is, however, still in use, particularly for distribution of financial data.

While newer technologies like COM offer features DDE doesn’t have, there are also issues with regard to configuration that can make COM more difficult to use than DDE. Also, DDE is a generic protocol that allows any application to monitor changing data provided by any other application, while to achieve similar results in COM one would generally need to know details of the application that is either to produce or consume the data. For example, a single DDE financial data distribution application can provide live prices to either Excel or a financial charting application without needing to know which it is doing, while to achieve the same results with COM would usually require the distribution application’s authors to write custom code for each use scenario.