Loop For Multiple Excel Worksheets

[#1]

Hi,

I have an excel workbook with 20 worksheets.  Each worksheet has a different name (i.e. January, February, etc.)  All the worksheets are in the same format and contain the same fields.  How do I create a loop to load all worksheets in the excel workbook in Qlikview?

Thanks

Brian

[tresesco]

LET

vWorkbook ='C:\MultiSheetsLoading.xlsx';



ODBC CONNECT TO [excel;DBQ=C:\MultiSheetsLoading.xlsx];









//------ Create a list of sheets from Excelworkbook

XLtable:        // Name the table that will be created by SQLTables stmt

SQLtables;

DISCONNECT; //Discounnect from ODBC - tables are loaded into document





TRACE Loop through tables;

//A loop is made through all sheets in the documents

FOR i = 0 to NoOfRows('XLtable')-1 //Loop and set number of tables



//Get name of each sheet

//Remove all $-signs and quotes '

LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'XLtable'), chr(39)),'$');

TRACE Loading data from $(sheetName);

ExcelLoad:    //Tablename

LOAD *

FROM MultiSheetsLoading.xlsx (ooxml, embedded labels, table is $(sheetName)); //ooxml for XL2007

NEXT



DROP TABLE XLtable;   //Drop list of possible sheets in XL

This is a sample code. Hope this helps.

[#3]

see link

http://community.qlik.com/thread/54342