Thanks for the reply, but as soon as I place any of the elements past JAN in quotes I get a 'Field not found' error because the sheet cannot be loaded. If I just put JAN in quotes the first 12th of all the row numbers have 'JAN' populated in them.
Can you please try like below?
set errormode=0; // setting the error mode to 0
// create a table keeping all the possible sheet names
load * Inline [
// load the total count of sheet names
LET vSheetCount = NoOfRows('tmpinline');
// load each files in a loop assume that we have book1 and book2.xlsx
for each vFiles in filelist('Book*.xlsx')
// load each sheet until total sheet counts
for i = 0 to $(vSheetCount)-1
// store the sheet name
let vSheetName = peek('sheetname',$(i),'tmpinline');
'$(vSheetName)' as sheetname,FileName() as filename,*
FROM [$(vFiles)] (ooxml, embedded labels, Table is '$(vSheetName)');
drop table tmpinline;