There is a well known technique that use the Excel ODBC driver supplied from Microsoft to get a list of the sheet names of an XLSX-file. It is covered in different discussions on this forum - so I will not cover that here.
Here is an alternative way of getting the sheet names that does not involve the Excel ODBC-driver. It takes the advantage that an XLSX-file is in fact a ZIP-file in disguise. This ZIP-file (XLSX-file) has many XLM-files and more files in a folder structure. By extracting the right XML-file from this you can retrieve the sheet names easily.Another thing that this routine also illustrates is how you can unzip without being dependent on a command line unzip program. It takes advantage of the built-in libraries in any Windows operating system.
- Not needing to install Excel ODBC drivers
- Not needing to install an UNZIP program
I have coded a small VBscript function that is easily called from a load script that will do the extraction. The rest is only a matter of loading the XML file with the standard way of doing it in QlikView: