Here is what I did to get this all working:
1) Configure an Excel data source (using the Excel Driver) in the ODBC Administrator to point to the desired Excel workbook. I had 3 issues with this. First, I don't have an admin rights to my machine. So, I had to configure this under User DSN, not System DSN. Second, I had to configure all of this in the 64-bit version of odbcad32.exe (in the SysWOW64 folder, not in the System32 folder). When I attempted to configure this in the 32-bit version, it threw the error "The setup routines for the Microsoft Excel Driver ... ODBC driver could not be found. Please reinstall the driver." I don't know if this is expected, but when I configured it under the 64-bit version, I can see the changes persist to the 32-bit version. Third, I didn't know that data source names are so restrictive. I initially used a fairly long name, but I had to shorten it.
2) Create an ODBC connection (User DSN) in Qlik Sense. I encountered an issue with this. Even though my OS is 64-bit, Qlik Sense is 64-bit, and I configured the data source in the 64-bit version of odbcad32.exe, I had to use a 32-bit connection. When I tried to use a 64-bit connection, Qlik threw the error "...the specified DSN contains an architecture mismatch between the Driver and Application."
3) All of the above lets me get the required metadata (really, the total number of sheets) from the Excel workbook. I used the following code in Qlik Sense for this:
LIB CONNECT TO 'Excel Files';
//create table 'sheets' to hold metadata about the Excel file
sheets:
SQLTables
;
//get the total number of sheets
Let vNumberOfSheets = NoOfRows('sheets');
4) Now that I have the total number of sheets, I need to read the data in each of those sheets. I can do this in 2 ways.
In method #1, I am using an absolute path to the file. (This has no relation to the ODBC connection.) Because Qlik Sense only allows library connections to files, I had to modify the Settings.ini file (mine is located in C:\Users\MyName\Documents\Qlik\Sense) to use Legacy mode, not Standard mode. I modified this file by inserting the following line:
StandardReload=0
*Note: You need to be logged out of Qlik Sense in order to modify this file.
Then, I used the following code to read this file:
//load all sheets in the workbook
For i = 0 to $(vNumberOfSheets) - 1
Let vSheetNameTemp = PurgeChar(Peek('TABLE_NAME', i, 'sheets'), chr(39)); //sheet names that contain blanks will be surrounded by single quotes; remove these (and any other) single quotes
Let vSheetName = Left(vSheetNameTemp, Len(vSheetNameTemp) - 1); //the Excel ODBC driver perceives sheets in Excel workbooks as system tables; system tables get a $ sign appended to the end of them
Data:
Load
*
From
C:\Users\MyName\Documents\Qlik\Sense\Apps\multiple_sheets_test.xlsx
(ooxml, embedded labels, table is $(vSheetName))
;
Next i
Drop Table sheets; //drop the metadata tableNote that, in this solution, the ODBC data source does not necessarily need to point to the same location as that used in the Load statement (though, it would make sense to use the same path). And, as others have pointed out, flipping Qlik to run in Legacy mode instead of Standard mode (which is what you're doing with the modifying of the Settings.ini file) means that Qlik administrators cannot control connections that are hard-coded outside of the QMC and in the apps themselves.
In method #2, to avoid the security issue above, I created a folder connection in Qlik to the same path, as follows:
//load all sheets in the workbook
For i = 0 to $(vNumberOfSheets) - 1
Let vSheetNameTemp = PurgeChar(Peek('TABLE_NAME', i, 'sheets'), chr(39)); //sheet names that contain blanks will be surrounded by single quotes; remove these (and any other) single quotes
Let vSheetName = Left(vSheetNameTemp, Len(vSheetNameTemp) - 1); //the Excel ODBC driver perceives sheets in Excel workbooks as system tables; system tables get a $ sign appended to the end of them; however, folder connections won't see the $ sign; so, need to trim it off
Data:
Load
*
From
[lib://test/multiple_sheets_test.xlsx]
(ooxml, embedded labels, table is $(vSheetName))
;
Next i
Drop Table sheets; //drop the metadata tableHowever, with all of this being said, my Qlik admins won't allow either solution method. Issue #1 is the admin overhead that they will incur to configure the data sources to each Excel workbook in the ODBC Admin tool. (It is technically feasible to create multiple Excel data sources in the ODBC Admin tool, but becomes a nightmare to manage.) Issue #2 is the admin overhead that they will incur to set up new connections (ODBC and perhaps a folder connection) in the QMC. Issue #3 is specific to solution method #1. They will not allow file paths that they cannot control via the QMC. So, from all of this, my organization has now created a new rule: Qlik Sense is not allowed to point to Excel workbooks. Those workbooks must first be loaded into a SQL DB.
