I'm new to Qlik view and Sence, but have same requirement as you, after searching i got it worked like below, hope it could give any useful information.
First I just found CONNECT32 not works for me but CONNECT64 works, this may cased by your odbc driver version, so i just user CONNECT not specify 32 or 64.
As second, make sure you create ODBC in SYSTEM DSN and named 'Excel Files', or any other name but just need change [Excel Files;DBQ=$(File)] to [your name;DBQ=$(File)]; for me, i create Excel in system dsn and use [Excel;DBQ=$(file)]
so those two steps make me success to load multi tab for once excel; the scrip is
LET vFilePath = 'C:\';
FOR EACH file in FileList('$(vFilePath)\*.xlsx'); // Loops each excel file in the given Folder
//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT TO [Excel;DBQ=$(file)];
SQLtables; // Loads all sheet names in the Excel file.
FOR index = 0 to NoOfRows('SheetNames')-1 // Loops for each sheet in the Excel file.
LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));
if not WildMatch(sheetName,'*xlnm*') then //In case your sheet contains any filter or other
Load * ,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);