Hello.
OK. I couldn't get your renaming piece of code to work properly at my workstation (maybe something missing that was crunched from the post?), so in order to test the complete solution, I ended up redesigning the file renaming piece (same thing, another approach) and adjusting the tab renaming macro.
I have also introduced some artificial delays (1 second each, and adjustable), because I suspect that there is maybe a lag introduced by excel's launch or/and the issued rename command.
This code was tested and is working on my SAS 9.1.3 SP4/MS Excel 2003 (WinXP 32b) workstation configuration.
Just need to adjust the macro variables MY_DIR, SOURCE and TARGET to suit your needs (see code bellow).
PLEASE verify that the path of MS Excel is correct and that your regional settings specify the ; char as the list separator. If not, please do the necessary modifications (see comments) on the code bellow:
options noxsync noxwait xmin; * assynchronous execution;
/************************************************************************/
* macro for excel worksheet renaming;
%macro ren_sheettab(DIRNAME,FILENAME);
* DIRNAME = directory name of the excel file (no tailing \);
* FILENAME = filename of the excel (no .extension);
* alocates connection do DDE server;
filename xlfile dde 'excel|system'; * assumes DDE server is running;
data _null_;
file xlfile; * inits DDE connection;
put "[OPEN(""&DIRNAME\&FILENAME..xls"")]"; * opens desired workbook;
put '[WORKBOOK.NEXT()]'; * activates the next worksheet;
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;
/***** adjust the char list separator if necessary!!! *****/
put "=WORKBOOK.NAME(""Sheet1"";""&FILENAME"")"; * uses name function;
/***** adjust the char list separator if necessary!!! *****/
put '=HALT(TRUE)'; * halts macro;
put '!dde_flush'; * flushes DDE writte buffer;
run;
* run macro for tab renaming;
data _null_;
file xlfile; * inits DDE connection;
put '[RUN("Macro1!r1c1:r2c1")]'; * run macro;
put '[ERROR(FALSE)]';
run;
* delete macro worksheet, save and quit;
filename xlmacro clear;
data _null_;
file xlfile;
put '[WORKBOOK.ACTIVATE("Macro1")]'; * activate macro worksheet;
put '[ERROR(FALSE)]';
put '[WORKBOOK.DELETE()]'; * delete macro worksheet;
put '[ERROR(TRUE)]';
put '[SAVE()]'; * save workbook with renamed worksheet;
put '[FILE.CLOSE(FALSE)]'; * close file;
put '[QUIT()]'; * close DDE server;
run;
%mend ren_sheettab;
/************************************************************************/
* 1. renames FIRST file matching *&SOURCE*.xls to &TARGET._YYYY_MM.xls;
* 2. renames 'Sheet1' of the renamed file to to &TARGET._YYYY_MM;
%let MY_DIR=D:\temp; * directory to scan;
%let SOURCE=TESTE; * source text to search within filename;
%let TARGET=XPTOX; * target text (prefix) to use for filename renaming;
* start Excel DDE server;
data _null_;
/***** adjust to your path if necessary!!! *****/
x '"C:\Program Files\Microsoft Office\OFFICE11\excel.exe"';
/***** adjust to your path if necessary!!! *****/
call sleep(1,1); * custom delay of 1 second;
run;
* scan directory, search for mathcing filename, rename file, rename tab.;
data _null_;
* associate directory;
if not filename('MY_DIR',"&MY_DIR") then do;
D_ID=dopen('MY_DIR'); * open directory;
if not D_ID then stop;
M_IDX=1; * init directory member index;
do until (M_IDX gt dnum(D_ID)); * until there are no members;
NAME=dread(D_ID,M_IDX); * get member name;
F_ID=mopen(D_ID,NAME); * try to open member;
RC=fclose(F_ID); * close member;
* if it is a file and extension is .xls and source matches the name;
if F_ID and
upcase(substrn(strip(reverse(NAME)),1,4)) eq 'SLX.' and
index(upcase(NAME),upcase("&SOURCE")) then do;
* rename file;
TARGET=catx('_',"&TARGET",put(year(today()),z4.),put(month(today()),z2.));
call system("ren &MY_DIR\"!!strip(NAME)!!' '!!strip(TARGET)!!'.xls');
* rename tab;
call sleep(1,1); * custom delay of 1 second;
call execute('%ren_sheettab('!!strip("&MY_DIR")!!','!!strip(TARGET)!!');');
RC=dclose(D_ID); * close directory;
stop; * stop dataset execution;
end;
M_IDX+1; * update index to next directory member;
end;
RC=dclose(D_ID); * close directory;
end;
run;
It may look very complex, but actually it is not. Almost every line is commented to help understanding.
I'll post here some documentation after you get this to work.
Cheers from Portugal.
Daniel Santos @
www.cgd.pt.
