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