Excel with multiple sheets

rhinobundy — Sat, 16 Nov 2024 21:41:18 GMT

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

Re: Excel with multiple sheets

Gysbert_Wassenaar — Thu, 27 Dec 2018 11:35:23 GMT

The subroutine needs to be defined before you can call it. If the script you posted is as you created it then you need to move the code for the subroutine up to at lease above the for loop.

Re: Excel with multiple sheets

rhinobundy — Fri, 28 Dec 2018 13:59:23 GMT

Thanks a lot. My script is working now!!

topic Re: Excel with multiple sheets in App Development

Excel with multiple sheets

Re: Excel with multiple sheets

Re: Excel with multiple sheets