Load more Tables froman Excel File

[francisvandergr]

I have a problem with a loop in an excel file. I want to load all tabs.I have a script error when loading the file. (error attached)

Can someone helpme ?

[brenner_martina · Accepted solution]

Hi, Francis,

do you know the sheetnames and are all tables same in structure? Than you can use a loop with a variable like:

For each Sheetname in 'Table1', 'Table2', ... 'TableN'

Load * From C:\1-jan.xls (biff, ..., table is $(Sheetname)$);

Next Sheetname

[stevedark]

Hi there,

Can you please provide an English translation of the error message?

As a rule it is better to open Excel files with the native QlikView drivers, rather than ODBC. This doesn't allow you to load a list of tabs in the way that ODBC does though (which I presume is what you are attempting?).

What I have done in the past is created an 'Index' tab as the first tab of the spreadsheet and then recorded the names of all other tabs to load on here. This can be loaded into QlikView to do the loop. This means that further information can be stored about each tab - perhaps column names to be expected on each sheet, or header offsets etc.

To implement the Index sheet way of doing things you will need to look into the For, NoOfRows and Peek statements - and usage of variables in load scripts.

Hope that helps.

Regards,
Steve