Hello SASPhile.
You got it wrong.
ALL the code should be use, not only part of it.
Since you're renaming the files by cycling through a datastep, and the sample of code I have posted is intend to deal with only one file, You should encapsulate the code into a macro and call it from the datastep, say with a call execute.
Something like this:
/* THIS GOES AT THE TOP OF YOUR SCRIPT */
options noxsync noxwait xmin; * assynchronous execution;
* start Excel DDE server;
x 'excel.exe'; /* you may need to add the full path */
filename xlfile dde 'excel|system';
/* THIS GOES AT THE TOP OF YOUR SCRIPT */
%macro ren_sheettab(DIRNAME,FILENAME);
* DIRNAME = directory name of the excel file (no trailing \);
* FILENAME = filename of the excel file without extension;
data _null_;
file xlfile; * start DDE connection and open desired workbook;
put '[OPEN("&DIRNAME\&FILENAME..xls")]';
put '[WORKBOOK.NEXT()]';
put '[WORKBOOK.INSERT(3)]'; * insert new macro worksheet;
run;
* prepare macro for worksheet renaming;
filename xlmacro dde 'excel|Macro1!r1c1:r2c1' notab;
data _null_;
file xlmacro;
* write macro code;
put '=WORKBOOK.NAME("Sheet1","&FILENAME")';
put '=HALT(TRUE)';
put '!dde_flush';
run;
* rename worksheet by running macro;
data _null_;
file xlfile;
put '[RUN("Macro1!r1c1")]';
run;
* delete macro worksheet;
filename xlmacro clear;
data _null_;
file xlfile;
put '[WORKBOOK.ACTIVATE("Macro1")]';
put '[ERROR(FALSE)]';
put '[WORKBOOK.DELETE()]';
put '[ERROR(TRUE)]';
run;
%mend ren_sheettab;
Now, from within you datastep renaming routine you could call execute the above macro, say, like this:
...
call execute ("%ren_sheettab(&my_dir,"||
catx('_',nm,put(year(today()),Z4.),put(month(today()),Z2.))||');');
...
Now, if you are having trouble understanding this piece of code, please say so.
There is no real purpose on helping someone if no knowledge is transmitted.
Cheers from Portugal.
Daniel Santos @
www.cgd.pt.
Message was edited by: Daniel Santos
