Using DDE to Write Data to Microsoft Excel.

[deleted_user]

In the SAS Help and Documentation under the headline Using DDE to Write Data to Microsoft Excel you can find the following code:

/* The DDE link is established using */
/* Microsoft Excel SHEET1, rows 1 */
/* through 100 and columns 1 through 3 */
filename random dde
'excel|sheet1!r1c1:r100c3';
data random;
file random;
do i=1 to 100;
x=ranuni(i);
y=10+x;
z=x-10;
put x y z;
end;
run;


I am wondering about the lines

filename random dde
'excel|sheet1!r1c1:r100c3';

If the name of my workbook is MyWorkbook.xls and the name of the worksheet, where I want the numbers, is MySheet, and the pathname for MyWorkbook is

C:\MyDir\

then shouldn't the code be

filename random dde
'excel|C:\MyDir\[MyWorkbook.xls]MySheet!r1c1:r100c3';

or something similar?

For some reason it doesn't work. I get the error message: Physical file does not exist, excel|C:\MyDir\[MyWorkbook.xls]MySheet!r1c1:r100c3.

But the file exists in that directory.

And I have started Excel with


options noxwait noxsync;
x 'excel' /* YES, WITH THE CORRECT PATHNAME*/

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

What can be wrong?

Anne

[Cynthia_sas]

Hi:
I don't use DDE at all, since it is very old (Excel 4) Microsoft technology. But it was my understanding that both Excel and SAS had to be open at the same time for the DDE conversation to take place. If Excel is not open to -that- workbook when DDE starts, then, I think, as far as DDE is concerned the workbook doesn't exist.

There are a few ways to open the proper workbook, as described in these papers:
http://www.sas-consultant.com/professional/papers.html In particular, in this paper, there's a description of opening a workbook via DDE command and then saving it to the name you want BEFORE populating it:
http://www.sas-consultant.com/professional/SUGI27-HOW-DDE.pdf

Perhaps these will be of some help.

cynthia

[deleted_user]

Thank's for your answer.

But if DDE is an old method, then what should I use?

My worksheets are existing and formatted. The formats, the names of the workbook and its worksheets should not be changed, but some numbers from SAS output should be placed in specific cells.

I get the numbers by processing a SAS table in different ways.

What is the most appropiate form of the data that I am supposed to place in certain worksheet cells?
The output is NOT like a table with n rows and m columns.

And then if you could identify the method(PROC xxxx) or whatever, the main problem is to get the modern method(s) you are hinting about, to update the Excel sheet.

Anne

[Cynthia_sas]

Hi:
You might look at other technologies, as recommended in this Microsoft article:
http://support.microsoft.com/kb/247412

There are some user group papers that discuss the use of OLE-DB and .COM, .NET -- and a doc link that talks about the use of OleDB. Here are a few links:
http://www2.sas.com/proceedings/sugi24/Dataware/p136-24.pdf
ftp://ftp.sas.com/techsup/download/v8papers/odbcdb.pdf
http://support.sas.com/documentation/tools/oledb/index.htm

And this is a paper about TAGSETS.EXCELXP:
http://support.sas.com/rnd/papers/sgf07/sgf2007-excel.pdf

cynthia

[deleted_user]

Cynthia,

You are right. You need to keep the workbook open (Myworkbook.xls), only then will DDE
detect the existence of the workbook.

Anne,
The code you had written works if you keep the workbook open.

Thanks
Shalini

[deleted_user]

Hi shalini,

If we use X and dde bot then no need to open the excel sheet i guess,
but if u use only dde then excel file must be opened .

Thanks,

Sudhir