You can loop through the files, assigning the filenames to a variable, which you would then need to use in the load statement and can also strip apart to use for making fields. Something like this:
SET Dir = 'C:\ExcelFilesDir';
FOR EACH File IN FileList($(Dir)&'*.xlsx')
SET vExcelFileMonth = Left($(File),2);
SET vExcelFileYear = Mid($(File),4,2);
MakeDate($(vExcelFileYear),$(vExcelFileMonth)) AS ExcelFileDate
FROM [$(File)] (biff, embedded labels, table is [Sheet1$]);
Im loading the files using *.
I just clicked into the table files and brought in the source file, and the changed the 01-13 to *,
so it looks like this;
[MUV analysis\ *.xlsx]
Where when I load one source file at a time it looks like this
I need to somehow extract the 01-13 and make it into a MonthYear field in the app, but im not sure can that be done when im using * to load in all the files?
Note: Each source file has the exact same layout and columns so that * can work.