Hi there - you will need to put a nested loop construct in place, firstly for each workbook in the folder and then for each sheet in the workbook.
For each workbook you will require code similar to this:
for each vFileName in FileList('.\*.xls')
And then within that loop, you will need to loop for each sheet. It is possible to get a list of all sheets in an Excel spreadsheet by connecting to it with an ODBC connection and calling the SQLTables function. But as you know the sheets are sequential from 1 that seems overboard. I would probably put an extra sheet 0 on each workbook that contained a cell with the number of sheets. The code would then be a bit like this:
Temp_Sheets:
LOAD
Sheets
FROM $(vFileName)
(biff, embedded labels, header is 1 lines, table is [0$]);
let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');
DROP TABLE Temp_Sheets;
for vSheetNo = 1 to $(vMaxSheet)
Data:
LOAD
[... Field List ...]
FROM $(vFileName)
(biff, embedded labels, header is 1 lines, table is [$(vSheetNo)$])
next
Obviously this does rely on the sheets being sequential from 1, otherwise the load will fall over. In this case explore the SQLTables route.
