Date:         Sun, 9 Mar 1997 01:31:00 EST
Reply-To:     Paul Grant <0005733569@MCIMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Paul Grant <0005733569@MCIMAIL.COM>
Subject:      Re: DDE and opening Excel files

John,
 
I had the same problem.  This code will do the trick:
 
     %macro sas2excl( DSN = Sep96 );
 
       filename cmds dde "Excel|system";
 
       data _null_;
 
         file cmds;
 
           put "%str([open(%"c:\work\&DSN..xls%")])" ;
 
       run;
 
       /*  remaining code  */
 
     %mend sas2excl;
 
Good luck!
 
-Paul
 
--------------------------------------
 Paul Grant - SUGI 21 Conference Chair
 Private Healthcare Systems, Inc.          617-895-7614
 1100 Winter Street                        617-895-7655 / fax
 Waltham, Mass  02154-1227                 0005733569@mcimail.com
 
 
---- in response to ----------------------------------------
>
>Date:     Sat Mar 08, 1997  4:47 pm  EST
>From:     John Bentley
>          EMS: INTERNET / MCI ID: 376-5414
>          MBX: bentleyj@ix.netcom.com
>
>TO:       Multiple recipients of list SAS-L
>          EMS: INTERNET / MCI ID: 376-5414
>          MBX: SAS-L@uga.cc.uga.edu
>BCC:    * Paul Grant / MCI ID: 573-3569
>Subject:  DDE and opening Excel files
>
>I'm working on an application that writes directly to an Excel
>spreadsheet.  I need to define a macro variable that holds the name of
>the spreadsheet--I'll _eventually_ be porting this to AF--but I'm
>having problems getting it to work because of the apparent quotation
>mark requirements.
>
>Excel is already running.  Below is a block of code that works just
>fine--the spreadsheet name is hardcoded into the PUT statement.  Notice
how the quotations marks are set up.  It won't work any other way.  It
>_has_ to be single quotes outside the square brackets, double quotes
>around the file name.  Please feel free to fiddle with it.
>
>The second block of code uses a macro and doesn't work.  I _very_
>slightly modified code from SAS document TS-325 "The SAS System and
>DDE", page 16.  Note the quotes. The SAS document says it works like
>this, but mine doesn't.  I've include output generated by the MPRINT
>option so we can see what the macro is seeing.  I've tried every
>combination of single and double quotation marks that I can think of,
>but as you know SAS macros are very aware of quotation marks.
>
>Ideas, suggestions, and comments are most welcome.  A big part of the
>success of the app will depend on being able to swap in the name of the
>spreadsheet.
>
>The SAS document references a paper from SUGI 17 "Data Exchange Between
>the SAS System and Microsoft Excel", but notes that it has some errors.
> Can anyone provide a copy of the paper?  I already have Mark Bodt's
>excellent paper from  Observations, 2d Quarter '96 "Talking to PC
>Applications Using Dynamic Data Exchange".
>
>Thanks in advance.
>
>John Bentley
>William M. Mercer, Inc.
>
> ----------------------- This works ----------------------
>
>630  options noxwait noxsync mprint;
>632
>633  filename cmds dde 'excel|system';
>634
>635  data _null_;
>636    file cmds;
>638             put
>'[OPEN("o:\common\datamgmt\sas\bsc\payrpt\reports\TEST.xls")]';
>640  run;
>
>NOTE: The file CMDS is:
>      FILENAME=excel|system,
>      RECFM=V,LRECL=256
>
>NOTE: 1 record was written to the file CMDS.
>      The minimum record length was 60.
>      The maximum record length was 60.
>NOTE: The DATA statement used 1.82 seconds.
>
> ----------------------- This doesn't work ----------------------
>
>
>587  options noxwait noxsync mprint;
>589
>590  %macro sas2excl (excelin);        * excelin holds the
>spreadsheet file name ;
>591
>592  filename cmds dde 'excel|system';
>593
>594  data _null_;
>595    file cmds;
>597             put "[OPEN(%bquote(&excelin))]";        * this command
>goes to excel and says;
>599
>run;
>*
>to open the spreadsheet.           ;
>600
>601  %mend sas2excl;
>602
>603      * call the macro and specify the spreadsheet name ;
>604  %sas2excl (o:\common\datamgmt\sas\bsc\payrpt\reports\TEST.xls);
>
>MPRINT(SAS2EXCL):   FILENAME CMDS DDE 'excel|system';
>MPRINT(SAS2EXCL):   DATA _NULL_;
>MPRINT(SAS2EXCL):   FILE CMDS;
>MPRINT(SAS2EXCL):   PUT
>"[OPEN(o:\common\datamgmt\sas\bsc\payrpt\reports\TEST.xls)]";
>MPRINT(SAS2EXCL):   RUN;
>
>NOTE: The file CMDS is:
>      FILENAME=excel|system,
>      RECFM=V,LRECL=256
>
>ERROR: Error detected accessing device excel|system.
>FATAL: Unrecoverable I/O error detected in the execution of the data
>step program.  Aborted during the EXECUTION phase.
>NOTE: 0 records were written to the file CMDS.
>NOTE: The SAS System stopped processing this step because of errors.
>NOTE: The DATA statement used 1.64 seconds.
>
>
>--
>John E. Bentley
>Chicago, USA
>BentleyJ@ix.netcom.com