Hi I want to load data from multiple excelfiles that can containe multiple worksheet. I have made a Excel file that contains the name of the files, the worksheet and the path where it is placed.
I made a script, you can see below. Until the row with the Call everything seems to go well.
Can anybody tell me how to call the subroutine with variables and then load the data from the specific worksheet that is written in the variable. I would be very greatfull!!
This is the script that I have written! I also have posted the Excel files that I use in this script. It will expand if this script works.
Let vTeller=1;
IMPORTEER:
LOAD
RowNo() as rijnr,
padnaam,
excelbestand,
werkblad
FROM [lib://Qlik/instellingen.xlsx]
(ooxml, embedded labels, table is Blad1);
MAXAANTAL:
Load max(rijnr) as MaxAantal
resident IMPORTEER;
LET vMaxAantal = peek('MaxAantal');
For vTeller =1 to $(vMaxAantal)
tmp:
load
rijnr as intRijnr,
padnaam as strPadnaam,
excelbestand as strExcelBestand,
werkblad as strWerkblad
Resident IMPORTEER
WHERE rijnr=$(vTeller);
LET vRijnr = peek('intRijnr');
LET vPath ='lib://Document/';
LET vExcel = peek('strExcelBestand');
LET vWerkblad = peek('strWerkblad');
LET vFullPath = '$(vPath)$(vExcel)';
Call LaadData('$(vFullPath)','$(vPath)','$(vExcel)','$(vWerkblad)');
drop table tmp;
next
sub LaadData (vFullPath,vPath, vExcel,vWerkblad)
trace $(vPath);
trace $(vExcel);
trace $(vWerkblad);
trace $(vFullPath);
LAADGEGEVENS:
LOAD
bron,
naam,
aantal,
nr,
bestandsnaam
FROM $(vFullPath)
(ooxml, embedded labels, table is $(vWerkblad));
end Sub
