turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-20-2009 04:13 AM

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

/* 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

'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

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

or something similar?

For some reason it doesn't work. I get the error message:

But the file exists in that directory.

And I have started Excel with

x 'excel' /* YES, WITH THE CORRECT PATHNAME*/

data _null_;

x=sleep(10);

run;

What can be wrong?

Anne

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-20-2009 11:04 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-22-2009 12:48 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-22-2009 03:05 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2009 05:29 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-18-2009 05:20 AM

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

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