I am trying to write a SAS program to do a find and replace in an excel file via DDE. Specifically, I am trying to search the header row for spaces between the strings (i.e., " ") and replace them with no spaces (i.e., "").
For example, if I have a cell that contains "Test Name" I want to do a find and replace to make it "TestName".
This is what I 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 and 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 and replace function is not working. I get the following in my log after it reads that statement:
NOTE: The file CMDS is:
DDE Session,
SESSION=excel|system,RECFM=V,LRECL=256
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: 2 records were written to the file CMDS.
The minimum record length was 21.
The maximum record length was 70.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.63 seconds
cpu time 0.00 seconds
Any suggestions?
Also, does anyone know what the parameters in the formula.replace statement are? I just know the first and second are what you want to find and what you want to replace it with. I am struggling to find any documentation.