Load Excel (XLSM) files in QLIK CLOUD app

[amaaiia]

Hi!

I have some excel files in my Qlik Cloud repository (lib), each file has 2 to N sheets. All sheets in an excel file have the same structure (well, the truth is that N-1 files do, 1 sheet has to be discarded), so I want to read all sheets from all files and concatenate them in the same table.

I'm doing the following:

set sheetnamesList='s1', 's2', 's3';
set ErrorMode=0;
for each excel in FileList('lib://DataFiles/*.xlsm')
	trace('Reading file: ', $(excel));
    for each sheet in $(sheetnamesList)
        trace('Reading sheet: ', $(sheet));
        my_table:
        LOAD
            A,
            B,
            C,
            D,
            '$(sheet)' AS SHEET
        FROM [$(excel)]
        (ooxml, no labels, header is 12 lines, table is $(sheet));

        if ScriptError=8 then //error file not found
        	trace('error in ', $(sheet));
        	ScriptError=0
        end if
    NEXT sheet
NEXT excel

This works with a few amount of excel files where I know the names of the sheets. But I would like to generalize the script to get the list of sheet names of the current excel file inside the loop.

Is there any way to achieve this?

Thanks!

[Ralf-Narfeldt]

I don't think there is a way to do that in Qlik Cloud currently. You can do it in Qlik Sense on Windows and QlikView by connecting through ODBC and using the SQLTables command, as described here:
https://community.qlik.com/t5/Official-Support-Articles/How-to-load-Excel-sheet-names-in-QlikView-an...

You can call the sheets by number instead of name:
table is @1, table is @2 ...
If you do that in a loop, then maybe you can catch the ScriptError=10 (Table not found)?
I have not tried it though...