Sorry Rob, my mistake, where I said oldest file I should have said newest file. I am interested in loading only the newest file in the folder. By the way on your proposed script, last line where you add EXIT FOR ...would I need to add "1" at the end ? ( so the loop stops after the first file has been loaded )...or it is just enough to leave it like that ( EXIT FOR ).
Thank you very much guys for your help. I managed to follow your suggestions ( and the quickest way provided by Rob ). I added some calculated fields , derived from the file name to have my datestamp in the resulting table as well :
FOR Each file in FileList( '*_Activ_EU.xlsx' )
left(Filename(),8) as DateStamp_string,
Date(date#(left(Filename(),8),'YYYYMMDD'), 'DD-MMM-YY') as DS_Date,
Year(date#(left(Filename(),8),'YYYYMMDD')) as DS_Year,
Month(date#(left(Filename(),8),'YYYYMMDD')) as DS_Month,
dual('Q0'&ceil(month(date#(left(Filename(),8),'YYYYMMDD'))/3),ceil(month(date#(left(Filename(),8),'YYYYMMDD'))/3)) as DS_QTR
(ooxml, embedded labels, table is Sheet1);
Do you know any shortcut for above scenario where you need to add several calculated fields basically using the same original field, in this case being left(Filename(),8) ? I thought of maybe just loading this calculated field first ( alone ) ...then build the other fields from this one ( already aliased as DateStamp_string ) using a resident load ? Any other ( better, faster ) ideas ?