Qlik Sense load excel file set the variable of the worksheet name

[anson_kong]

hi all,

the following code is looping excel files in a folder and sub folder,

however, it is difficult to set the variable of the worksheet name.

(for each vSheet in 'a','b,'c*','d','e','f'.....) because the worksheet name is dynamic

other than legacy mode, and odbc connect (not work in the qliksense) <<< I try several days

any idea of this, thank you.

code:

-------------------------------------------------------------------------

sub DoDir (Root)

For Each Ext in 'xls'

For Each File in filelist (Root&'\*.' &Ext)

for each vSheet in 'a','b,'c*','d','e','f'.....

Table:

LOAD

*,

FROM $(File)(biff, embedded labels, header is 3 lines,table is $([vSheet]$)));

next vSheet

Next File

Next Ext

For Each Dir in dirlist (xxxx)

Call DoDir (Dir)

Next Dir

End Sub

Call DoDir (xxxxx)

[agigliotti]

I suggest you to take a look at Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[sasiparupudi1]

Try Something like below

Create a procedure

Sub IsUrlAvailable(vTempUrl,vTabNo)

LET vTempErrorMode=ErrorMode;

SET ErrorMode=0;

SET vTableNotFound='Table Not Found';

SET vIsFileNotFound=0;

SET vTempTableName='TEMP_T1';

$(vTempTableName):

    First 3

NOCONCATENATE LOAD

*

FROM

[$(vTempUrl)]

(biff, embedded labels, table is '@$(vTabNo)');

LET vIsFileNotFound=(ScriptError='$(vTableNotFound)');

DROP Table '$(vTempTableName)';

SET ErrorMode=$(vTempErrorMode);

END SUB

SET vFile='YourFile.xls';

For i=1 to 100

call IsUrlAvailable('$(vFile)',$(i))

IF Not $(vIsFileNotFound) Then

LOAD

     *

FROM '$(vFile)'

(biff, embedded labels, table is '@$(i)');

END IF

Next i;

hth