Hi Paudahuan:
I did just test this code and it works perfectly and it will return a list of all files in a given directory, to your log. here are your steps:
1) submit the macro definition (every line from %macro to %mend
2) When you call the macro definition, in the parenthesis, write the name of the folder containing the .xls and .xlsx files, something like this: %find_excel(S:\Workshop)
3) Go to your log and observe the list of Excel workbooks; it will look something like this:
S:\Workshop\custfm.xls S:\Workshop\custfm.xlsx S:\Workshop\daily_sales.xls S:\Workshop\junk.xlsx S:\Workshop\junk1.xlsx S:\Workshop\sales.xlsx
4) Depending on how many you have, you can manually import them into SAS using PROC IMPORT.
5) Alternatively, you could write a libname statement, and use the Data step to read, combine, etc. selected workbooks, something like this:
libname exfile xlsx ("s:\workshop\custfm.xlsx","s:\workshop\junk.xlsx");
I realize this code may be new to you, but at least give it a try, see your results.
... View more
