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)]; SheetNames: SQLtables; // Loads all sheet names in the Excel file. DISCONNECT; 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 TableName: Load * , FileBaseName()as FIle, FileDir() as Dir, FileName() as File_Name, '$(sheetName)' as Sheet_name From $(file)(ooxml, embedded labels, table is [$(sheetName)]); ENDIF NEXT index DROP TABLE SheetNames; NEXT
the code is based on Jagan's answer Load Multiple excel sheets using For loop and Settu's in duplicate entries when reading (3) worksheets in workbook.
hope this could works for you.