SAS - Execute Find and Replace in an Excel File via DDE -

i trying write sas program find , replace in excel file via dde. specifically, trying search header row spaces between strings (i.e., " ") , replace them no spaces (i.e., "").

for example, if have cell contains "test name" want find , replace make "testname".

this have:

options noxwait noxsync;  /* open excel */ x '"c:\program files (x86)\microsoft office\office14\excel.exe"'; filename cmds dde 'excel|system'; data _null_;     x=sleep(5); run;  /* open file , manipulate*/ data _null_;      file cmds;      put '[open("c:\filename.xls")]';      put '[select("r1")]';     put '[formula.replace(" ","",1,,false,false)]';  run;  /*close file*/ data _null_;     file cmds;     put '[file-close("c:\filename.xls")]';     put '[quit()]'; run; 

the find , replace function not working. following in log after reads statement:

note: file cmds is:     dde session,     session=excel|system,recfm=v,lrecl=256  error: dde session not ready. fatal: unrecoverable i/o error detected in execution of data step program.       aborted during execution phase. note: 2 records written file cmds.     minimum record length 21.     maximum record length 70. note: sas system stopped processing step because of errors. note: data statement used (total process time):     real time           0.63 seconds     cpu time            0.00 seconds 

any suggestions?

also, know parameters in formula.replace statement are? know first , second want find , want replace with. struggling find documentation.

my suggestion write excel (vba) macro this, call macro; gives not far superior ide write code in, contextual clues , such, gives lot more control. can call macros separate workbooks, if have repeatedly (i assume so), can put in 'macro' template workbook open separately automated file.