Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ivan_will
Contributor II

How to take Excel sheets names ?

Hi all,

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

Thanks in advance!

2 Replies
Not applicable

How to take Excel sheets names ?

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

Not applicable

Re: How to take Excel sheets names ?

Hi

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

Community Browser