The name of the Excelsheet, which you are loading is in the script. You may also write workbook- and sheet-name into variables and refer to them in the script.
If you access Excel with an ODBC-connection (not the default loader) you may read all sheetnames with SQLTABLES-command and store them into a table. Then you may inspect the possible sheetnames and select one or more, which are to be loaded.
Below you may find a copy from an application:
// ======================================================================================== Load Excel-Files from one directory
/* All Excel-files fitting into a certain pattern will be loaded
individual sheets will be loaded if also fitting into certain pattern
file-headers are *not* used, as have changed in the past
each file will be documented with file- and sheetname as well as with creation-date of the file
*/
// ==============================================================Scan through all Files in a Directoy matching a certain pattern
FOR each varFile in FileList (strFileList) // ===============strFileList has been defined under "Variables"
ODBC CONNECT TO [Excel Files; DBQ=$(varFile)]; // ========================================DBQ= Name of Workbook
tmp: // =====================================Read sheet-informations
sqltables;
SheetNames:
LOAD
*,
recno() AS record;
LOAD
TABLE_NAME AS SheetName
RESIDENT
tmp
WHERE
WildMatch(TABLE_NAME, '*BD*')
AND NOT WildMatch(TABLE_NAME, '*_Filter*', '*Print*', '*FilterDatabase*');
DROP TABLE tmp;
LET maxRec = PEEK('record', -1, 'SheetNames'); // ==============================Determine Last Table (for Loop)
FOR i = 0 TO maxRec -1 // ========================Loop through all sheets and load data
LET strSheet = Trim(PEEK('SheetName', i, 'SheetNames'));
LET strConc = chr(39) & Upper(Mid(strSheet, 5, 3)) & chr(39); // ======= Logistic Concept (SUR/DEF) from sheetname
LET strSiTy = chr(39) & Upper(Left(SubField(strSheet, ' ', 3), 4)) & chr(39); // ============ Read SiTy from sheetname
LET strSheet = PurgeChar(strSheet, chr(39));
LET strSheet = PurgeChar(strSheet, '_'); // ====================== used for designating sheet for reading
Let strSheetName = chr(39) & strSheet & chr(39); // ================== used for recording filename for each entry
If WildMatch(strSheet, '*DV20*', '*DV40*', '*HC40*', '*RE40*') THEN
Data:
LOAD
$(strConc) AS Concept,
$(strSiTy) AS SiTy,
// @1 AS Region,
@2 AS Port,
...
FROM
[$(varFile)]
(biff, header is 5 lines, no labels, table is [$(strSheet)])
WHERE
Len(Trim(@2)) = 5;
END If
NEXT i
DROP TABLE SheetNames;
NEXT varFile
HTH
Peter
