topic Re: How to take Excel sheets names ? in QlikView

How to take Excel sheets names ?

ivan_will — Fri, 27 May 2011 08:32:02 GMT

Hi all,

Could you please tell me how to take the names of excel sheets - is there any function .... ?

Thanks in advance!

How to take Excel sheets names ?

Fri, 27 May 2011 08:54:17 GMT

Have a look at the following - it loops through all .xls in a folder and then sets a variable to the current sheetname, which should give you some ideas:

SET vPath = 'C:\pathtodata\';

FOR each vFile in filelist ('$(vPath)'&'*.xls')

CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$(vFile)';Extended Properties="Excel 8.0;"];

[Excel Sheets]:
// Get description of the tables in the ODBC datasource - with Excel each sheet is a table
SQLtables;

DISCONNECT;

// TABLE_NAME contains the sheet name - loop through this set
FOR i = 0 to NoOfRows('Excel Sheets')-1

// TABLE_NAMEs that contain spaces will be enclosed in single quotes. The purgeChar function will remove any quotes.
LET vSheet = purgeChar(peek('TABLE_NAME', i, 'Excel Sheets'), chr(39));

NEXT ; // (sheet)

DROP TABLE [Excel Sheets];

NEXT vFile

Regards,

Gordon

Re: How to take Excel sheets names ?

Tue, 14 Oct 2014 14:07:48 GMT

When I use this script I get a Class Not Registered error message when Qlikview tries to connect to the Excel file. Any suggestions as to what might be causing that? I have run a scan to see if there are any corrupt programmes / files and it comes back saying no so it is not that.

Thanks