When I saw you're first post, I heard a little voice in my head saying "Don't reply! Don't reply!". 😂
Okay, here's an attempt to loop through multiple workbooks, and then to loop through multiple spreadsheets within the workbooks. Give it a spin, and hopefully it'll give you some thoughts that will put you on the right path.
Good luck!
Tom
data all_files;
length file_name file_path $500;
file_path = "C:\workingfolder";
input file_name;
cards;
TestXL1.xlsx
TestXL2.xlsx
TestXL3.xlsx
run;
%macro GetFile(folder_path, file_name, i);
%macro GetSheet(folder_path, file_name, sheet_name, i, j);
data file_src_&i._&j.;
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count1 = &i;
file_count2 = &j;
run;
%mend;
/*create library for xlsx file*/
libname indata xlsx "&folder_path.\&file_name.";
/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname from dictionary.members where libname="INDATA";
quit;
data SheetsToSubmit;
length ExecuteLine $500;
set contents (keep=memname);
ExecuteLine = '%GetSheet(' || strip("&folder_path.") || ',' || strip("&file_name.") || ',' || strip(memname) || ',' || strip("&i.") || ',' || strip(put(_n_, best15.)) || ');';
call execute(ExecuteLine);
run;
%mend;
data BooksToSubmit;
length ExecuteLine $500;
set all_files;
ExecuteLine = '%GetFile(' || strip(file_path) || ',' || strip(file_name) || ',' || strip(put(_n_, best15.)) || ');';
call execute(ExecuteLine);
run;
... View more
