DDE error unless Excel running
in [SAS]
|
Prev: DOW Loop with multiple sum statements within by-group
Next: Query regarding ordering of alphabets using iml
From: BruceBrad on 25 May 2010 03:07 I'm using the following code to convert ODS html files into proper Excel files. It works fine if Excel (2007) is already running when I run the code. However, if Excel is not running I get an error message. I haven't used DDE much. Is this behaviour normal? Is there a better way to get Excel up and running? (I'm using SAS 9.2 TS2M2) %macro ToExcelBinary(in=,out=); /* This creates a binary excel file from any file that can be opened by Excel. Parameters: in File name (in quotes) that can be opened by Excel (eg csv, html, xml) out File name (in quotes) of Excel binary file to be created BB March2010, based on SAS usage note 37040 Example usage: ods msoffice2k file="%sysget(temp)\temp.htm"; proc print data=sashelp.class;run; ods msoffice2k close; %ToExcelBinary(in="%sysget(temp)\temp.htm",out="c:\temp \temp3.xls"); ----------------------------------------------------------------------- */ options noxwait noxsync; filename cmds dde 'excel|system'; data _null_; X=SLEEP(10); file cmds; put "[open("&in")]"; put '[ERROR("FALSE")]'; put "[SAVE.AS("&out",1)]"; X=SLEEP(2); put '[close("false")]'; run; %mend ToExcelBinary;
From: billyk on 25 May 2010 13:59 Try the technique explained here: http://www2.sas.com/proceedings/sugi25/25/cc/25p097.pdf Worked great for me. On May 25, 3:07 am, BruceBrad <b.bradb...(a)unsw.edu.au> wrote: > I'm using the following code to convert ODS html files into proper > Excel files. It works fine if Excel (2007) is already running when I > run the code. However, if Excel is not running I get an error message. > I haven't used DDE much. Is this behaviour normal? Is there a better > way to get Excel up and running? (I'm using SAS 9.2 TS2M2) > > %macro ToExcelBinary(in=,out=); > /* This creates a binary excel file from any file that can be opened > by > Excel. > Parameters: > in File name (in quotes) that can be opened by Excel (eg csv, > html, xml) > out File name (in quotes) of Excel binary file to be created > BB March2010, based on SAS usage note 37040 > Example usage: > ods msoffice2k file="%sysget(temp)\temp.htm"; > proc print data=sashelp.class;run; > ods msoffice2k close; > %ToExcelBinary(in="%sysget(temp)\temp.htm",out="c:\temp > \temp3.xls"); > > ----------------------------------------------------------------------- > */ > options noxwait noxsync; > filename cmds dde 'excel|system'; > data _null_; > X=SLEEP(10); > file cmds; > put "[open("&in")]"; > put '[ERROR("FALSE")]'; > put "[SAVE.AS("&out",1)]"; > X=SLEEP(2); > put '[close("false")]'; > run; > %mend ToExcelBinary;
From: pchoate on 27 May 2010 23:05
Bruce - DDE needs Excel to be open. What it is doing is running macros inside of Excel, using Excel's old v4 macro processor. I usually fire it open with a %sysexec or x command. just Excel options noxsync noxwait; %sysexec "C:\Program Files\Microsoft Office\office11\excel.exe"; a workbook: %let path=..\My Documents\Paul\Adhoc\; %let file=BG Goals 2009.07.26.xls; %sysexec "&path.&file"; You can do all your system utility stuff: %sysexec attrib +R "G:\DataX\Production\Data\Estimates Reports\POS Claims &Mo .xls"; Google "Macrofun.hlp" HTH - PC On May 25, 12:07 am, BruceBrad <b.bradb...(a)unsw.edu.au> wrote: > I'm using the following code to convert ODS html files into proper > Excel files. It works fine if Excel (2007) is already running when I > run the code. However, if Excel is not running I get an error message. > I haven't used DDE much. Is this behaviour normal? Is there a better > way to get Excel up and running? (I'm using SAS 9.2 TS2M2) > > %macro ToExcelBinary(in=,out=); > /* This creates a binary excel file from any file that can be opened > by > Excel. > Parameters: > in File name (in quotes) that can be opened by Excel (eg csv, > html, xml) > out File name (in quotes) of Excel binary file to be created > BB March2010, based on SAS usage note 37040 > Example usage: > ods msoffice2k file="%sysget(temp)\temp.htm"; > proc print data=sashelp.class;run; > ods msoffice2k close; > %ToExcelBinary(in="%sysget(temp)\temp.htm",out="c:\temp > \temp3.xls"); > > ----------------------------------------------------------------------- > */ > options noxwait noxsync; > filename cmds dde 'excel|system'; > data _null_; > X=SLEEP(10); > file cmds; > put "[open("&in")]"; > put '[ERROR("FALSE")]'; > put "[SAVE.AS("&out",1)]"; > X=SLEEP(2); > put '[close("false")]'; > run; > %mend ToExcelBinary; |