Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Could you please tell me how to take the names of excel sheets - is there any function .... ?
Thanks in advance!
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
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