The key to achieving this is to use an ODBC connection to connect to the Excel file first, and then use the SQLTables command to return a table listing the sheets (tables) within the file. Once you have this you can loop through this table and load the sheets like this:
ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];
LET vRows = NoOfRows('XlsTables');
FOR i = 0 TO $(vRows)-1
LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
(ooxml, embedded labels, table is '$(sheetName)');
DROP TABLE XlsTables;
This little trick can come in handy and with a few tweaks it can also address a number of other senarios for dealing with Excel spreadsheets such as:
- What if I want to load from an .xls file instead of a .xlsx file? Not a problem, simply change the Connect and Load statements accordingly.
- What if I want all the sheets loaded into a single table? You can create a blank table first, then use the above to concatenate the sheets into it rather than into their own named tables.
- What if the Excel file always has one sheet but it is named differently each time? Tweak the above to load the sheet into a known table in QV.
- What if, rather than loading all sheets from the file, I want to load only those which start with a known string (eg. "My Data Tab 01/01/2012", "My Data Tab 01/02/2012", etc). Simply wrap an IF statement around the LOAD to check if the table name starts with the required string.
Hi Matthew, I really like this trick but have just noticed that it only works when I had the Excel document open. When it is closed, vRows grows to 7 (should be 4 in my document) and it repeats the first 3 tabs. Any thoughts? JohnReplyDelete