Date: Fri, 14 Apr 2006 20:29:48 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: What's wrong with this SAS2EXCEL code
I think that Excel mistakes part of Hamani's malformed DDE triplet for a
named range. More below.
On Wed, 5 Apr 2006 15:51:38 -0400, Elmaache, Hamani <Hamani.Elmaache1@CRA-
ARC.GC.CA> wrote:
>I ran the following code to tranfert a data set a sheet salle1 in the
worksheet salles, there is no errors displayed in LOG, but I hade no result
in my worksheet in sheet salle1. Nothing happened !
>Can anynone helps me?
>Thanks
>
>
>
>FILENAME ddedata DDE "Excel|E:\salles.xls!Salle1! R1:R100" ;
>
> DATA _NULL_;
> FILE ddedata;
> set Salle1;
> put _ALL_ ;
> run;
> quit;
>
>Here, is the log:
>
>1039 FILENAME ddedata DDE "Excel|E:\salles.xls!Salle1! R1:R100" ;
>1040
>1041 DATA _NULL_;
>1042 FILE ddedata;
>1043 set Salle1;
>1044 put _ALL_ ;
>1045 run;
>
>NOTE: The file DDEDATA is:
> DDE Session,
> SESSION=Excel|E:\garderie\salles.xls!Salle1! R1:R100,
> RECFM=V,LRECL=256
>
>NOTE: 22 records were written to the file DDEDATA.
> The minimum record length was 90.
> The maximum record length was 109.
>NOTE: There were 22 observations read from the data set WORK.SALLE1.
>NOTE: DATA statement used:
> real time 0.03 seconds
> cpu time 0.01 seconds
So the mystery is: Why do the notes in the log indicate success in writing
to Excel via DDE if in fact nothing was written.
Let's first streamline the example.
To prepare the environment, launch Excel. If the workbook "Book1" and the
worksheet "Sheet1" exist, fine. Otherwise create as needed. Do *not* save
to disk.
Run the following:
filename ddedata dde "excel|Book1!Sheet1!r1";
data _null_; file ddedata; put 'Hello World'; run;
You should see
NOTE: The file DDEDATA is:
DDE Session,
SESSION=excel|Book1!Sheet1!r1,RECFM=V,
LRECL=256
NOTE: 1 record was written to the file DDEDATA.
But nothing shows up on the Excel side.
Now run the following to "round trip" the data back to SAS:
data _null_; infile ddedata; input; put _infile_; run;
You should see
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data step
program.
Aborted during the EXECUTION phase.
NOTE: 0 records were read from the infile DDEDATA.
Bill Viergever's suggestion was essentially to modify the FILENAME
statement as
filename ddedata dde "excel|[Book1]Sheet1!r1";
Then the two DATA steps work perfectly and the log reads:
Hello World
NOTE: 1 record was read from the infile DDEDATA.
Another remedy is to leave out the worksheet name and let it default:
filename ddedata dde "excel|Book1!r1";
In this case, that works as well as Bill's way.
The next experiment is to create a named range in Excel
(Insert>Name>Define), 1x2 or larger, and place the range name in the
triplet:
filename ddedata dde "excel|Book1!myrange";
Again, the DATA steps work fine.
But what if the range does not exist? I changed the FILENAME to
filename ddedata dde "excel|Book1!nosuchrange";
but I did *not* create a named range to correspond. The result from the
first step is then:
NOTE: The file DDEDATA is:
DDE Session,
SESSION=excel|Book1!nosuchrange,RECFM=V,
LRECL=256
NOTE: 1 record was written to the file DDEDATA.
The second step yields:
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data step
program.
Aborted during the EXECUTION phase.
NOTE: 0 records were read from the infile DDEDATA.
This is exactly what happened when the triplet had 2 exclamation points.
I tried to create an Excel range named "Sheet1!r1", but the name was
rejected as being invalid.
So, it seems that
(1) When the triplet is parsed, everything after the first exclamation
point is considered to be the cell range, and if it does not begin
with "RC" notation, it is assumed to be a named range.
(2) Range names are not validated in any way.
(3) When DDE points to a non-existent named range for writing, Excel goes
through the motions and does not raise an error.
