SQLTable TABLE_NAME Variable not Matching Sheet Name for Excel Load

[alextomlins]

Hi Guys, What is wrong with the below script? The variable is pulling the table names from an excel sheet using SQLTables Function. The variable seems to be fine as i can see it in the Trace statement - but when i call the variable inserted into the load statement as  ' Table is '$(vTable)')' it seems as if it can't match the Variable and the sheet in Excel. In short there is a discrepancy between SQLTables table names and the excel sheet tables names because when i come to call upon the sheets in the load it cannot identify/find them.

LIB CONNECT TO 'CRTrackerTest (924034-qlik_924034-admin)';

unqualify *;

TABLES:

SQLTables;

DATA:

LOAD 0 as Dummy AutoGenerate 0;

For i = 0 To NoOfRows('TABLES')

   Let vTable = Peek('TABLE_NAME', $(i), 'TABLES');

    Trace This is vTables Value: $(vTable);

    Concatenate(DATA)

    LOAD

    "CR Filename",

    Implemented as Date,

    Description,

    Requestor

    FROM [lib://ExcelSheets (924034-qlik_924034-admin)/CR Tracker.xls]

    (biff, embedded labels, Table is '$(vTable)')

    ;

Next

[alextomlins]

I think this is where it may be going wrong. I couldn't find a driver for xlsx so I converted to an xls and placed onto server from my computer. So on the server it's XLS but i guess when i opened it on my pc it opened using excel 2013 and maybe looks like XLSX?

The problem is i don't know - very new to using excel files with multiple tabs.

I'll make the change you suggested and see if this makes a difference.

Thank you for your help

[marcus_sommer]

In regard to the driver you should ask your IT for help - they might just have blocked the access for the "normal" users.

Beside this if you already used odbc to get the sheetnames you might also use it to load the data - maybe it's easier to adjust the right sheetnames for this approach. Maybe this example is helpful in any way: Re: Xlsb File Format In qlikview.

Another possibility would be to store the sheetnames within an extra table in an extra (hidden ?) sheet within the Excel itself (this might be automated with macros) and then looping through this table to load the various sheets.

- Marcus

[martinpohl]

what is the "from" statement like if you select the sheet manuel?

Regards