How to concatenate all xlsx from a folder

[Fred12]

Hello, I got a folder with a lot of xlsx ( maybe 200) but they have all same column

My aim is to concatenate all xlsx file 

So, I did that : 

For Each file in FileList('lib://Sharepoint/Shared Doc/Off/01- ds/01- coll/1.01 preno\*.xlsx')
final_table:
LOAD
*
FROM [$(file)] (ooxml, embedded labels, table is [RENDU]);
Next file;

It returns me no error but no data too.

Someone could explain me if I'm wrong and how I need to do 

Thanks for reading me 

[Kushal_Chawda]

@Fred12  try below. This approach will work only when you have single sheet in all excel files.

Data:
Load 0 as Temp
AutoGenerate 0;

for Each vFile in FileList('lib://DataFiles/*.xlsx')

Concatenate(Data)
LOAD *
FROM [$(vFile)]
(ooxml, embedded labels, table is []);

Next

Drop Field Temp;

if your excel files multiple sheets with different name then use below approach

https://community.qlik.com/t5/QlikView-App-Dev/Dynamic-File-Load-and-store-it-as-qvd/td-p/1740736

[Fred12]

I got multiple sheet, but I just want 1 of them,

I replace 

(ooxml, embedded labels, table is []);

by the name of my sheet like that : 

(ooxml, embedded labels, table is [RENDU)

but atleast I got again nothing,

no error but no line

[Kushal_Chawda]

@Fred12  not sure but below code works fine for me. May be you can debug for  any issue at your end. Check your path is correct in filelist function. I have updated my code which will work if any excel file is not having sheet name you want to load it will not throw error and will load other files

Data:
Load 0 as Temp
AutoGenerate 0;

for Each vFile in FileList('lib://DataFiles/test_*.xlsx')

set ErrorMode=0;

Concatenate(Data)
LOAD
    *,
    FileBaseName() as File
FROM [$(vFile)]
(ooxml, embedded labels, table is [TEST]);

if ScriptErrorCount>0 then

Trace "Error occurred while loading $(vFile)";

EndIf

set ErrorMode=1;

Next

Drop Field Temp;

[Fred12]

Hello, 

I unterstood why it didn't work, it's because you can't use sharepoint for that.

I changed my connection, and I wanted to know how I can to load multiple sheet excel without use your solution on other post because it doesn't work.

I can't use connector like you did 

thanks