topic Re: how do I get this code to work in QlikView

how do I get this code to work

Wed, 25 Nov 2020 16:16:04 GMT

For Each vFile in FileList('[Recording Sheet]')

ODBC CONNECT32 TO '[Excel Files;DBQ=$(vFile]';

Sheets:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('Sheets')-1

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

LOAD '$(vSheet)' as [Tab Name], *

From [$(vFile)]

(ooxml, embeddd labels, table is $(vSheet));

Next;

Re: how do I get this code to work

Gysbert_Wassenaar — Tue, 19 Apr 2016 14:54:42 GMT

A missing parenthesis:

ODBC CONNECT32 TO '[Excel Files;DBQ=$(vFile)]';

Dollar expanding the variable i might help:

Let vSheet = left(Peek('TABLE_NAME', $(i), 'Sheets'), len(Peek('TABLE_NAME', $(i), 'Sheets'))-1);

To be honest I don't understand why you're removing the last character of the sheet name. That could prevent the load statement from finding the right excel table.

Re: how do I get this code to work

swuehl — Tue, 19 Apr 2016 14:56:03 GMT

For Each vFile in FileList('[Recording Sheet]')

ODBC CONNECT32 TO '[Excel Files;DBQ=$(vFile]';

Shouldn't you give a path to an excel file here? I also think the single quotes are not correct and you are missing a closing ];

ODBC connection to Excel file

Re: how do I get this code to work

Tue, 19 Apr 2016 15:09:16 GMT

Hi,

the following is what I am trying to follow from Maxgro: Re: How to load EXCEL worksheets and retrieve tab names

Re: how do I get this code to work

Tue, 19 Apr 2016 15:10:54 GMT

I don't want to remove the last character; I am just following this:

Re: How to load EXCEL worksheets and retrieve tab names

Re: how do I get this code to work

Tue, 19 Apr 2016 16:16:53 GMT

this is what i have and it is still not loading the data