topic Re: Dynamic Excel sheet names based on month in App Development

Dynamic Excel sheet names based on month

niranjana — Thu, 19 Feb 2026 16:40:17 GMT

Hi,

I have a scenario where I have an excel file with sheet names named in descending order of a financial year...for example- Mar'26, Feb'26....until April'25. Same type of data is present in all these monthly sheets and other extra sheets are also there (which i load separately).

The sheets were named as Jan'25, Mar'26, but apostrophe was causing issue. So in the source file I changed into hyphen '-' like Jan-25,Feb-26 etc...

I tried for loop method, but though the code runs without error, data is not being pulled. I used the following code:

LET vFilePath = '.lib://.....Feb 2026/Power data base 2025-26.xlsx';

LET vMonth = 'April-25,Mar-25,May-25,June-25,Jul-25,Aug-25,Sep-25,Oct-25,Nov-25,Dec-25,Jan-26';

FOR EACH vMonth IN $(vMonth)

Power_Data:

LOAD

//TableName('$(vMonth)') as Sheetname,
//left(FileBaseName(),3) as Monty,
text('$(vMonth)') as MonthYear,
//month('$(vMonth)') as nummonth,
//'$(vMonth)' & '-' & SubField('$(vMonth)',2) as MonthYear,

field 1,

field 2,

field 3

..and other fields

FROM
['$(vFilePath)'] (ooxml, embedded labels, header is 1 lines,table is '$(vMonth)');

NEXT vMonth;

exit script;

Also, for all these sheets, I dont want to pull rows after "Totals" Row...

Any suggestion/solution is appreciated. Thanks in advance!!

Re: Dynamic Excel sheet names based on month

marcus_sommer — Fri, 20 Feb 2026 13:53:52 GMT

The syntax of vMonth is wrong because each single parameter needs to be wrapped with single-quotes, like:

vMonth = 'Jan', 'Feb', ...

Further I wouldn't use:

FOR EACH vMonth IN $(vMonth)

respectively naming the iteration-variable equally to the list-variable else rather:

FOR EACH vSheet IN $(vMonth)

Re: Dynamic Excel sheet names based on month

niranjana — Sat, 21 Feb 2026 00:38:02 GMT

Hi, Thanks for your reply.

When I enclose all months in single quotes, LET ='Jan','Feb',..... shows in red. I used SET instead. But didnt work. Also, I want the sheet name to be captured as "MonthYear" field in front end.

Would be helpful to know where exactly I have to modify the code.

2) Also, i dont want rows after "total" row......I want to pull rows only above that....

Thanks in advance!

Re: Dynamic Excel sheet names based on month

marcus_sommer — Sat, 21 Feb 2026 12:17:51 GMT

This variable-assignment syntax should be working:

set vMonth = "'Jan', 'Feb', ...";

which means that the entire string with the n single-quotes is wrapped with double-quotes.

The above is just an example - you could of course use your MonthYear as sheet-name as well as an included information within the load. But like hinted I wouldn't use the same name for the list- and the iteration-variable (never tried that and it may be working but IMO it's rather confusing ...).

Re: Dynamic Excel sheet names based on month

Qrishna — Mon, 23 Feb 2026 16:08:01 GMT

//----------------------------------------------------------------------------//
LET vPowerFieldNameCounter = '3'; // Initiate a Power Field counter Outside of the Loop

TRACE Start Loop;
FOR EACH vSheet IN '[Mar-26]','[Feb-26]','[Jan-26]' // Include the New Sheet Name Here, Qlik doesnt allow you to pull Excel Sheet Names Dynamically in this step. you need to create another loop that pulls excel metadata before this loop starts

TRACE Loading sheet: $(vSheet) Data;

Loop:
LOAD RecNo() AS RowOrder,
PurgeChar('$(vSheet)', '[]') AS SheetName,
Text(PurgeChar('$(vSheet)', '[]')) AS MonthYear,
FieldA,
FieldB,
[Power from Source A$(vPowerFieldNameCounter)] AS PowerValue // Your New Requirement
FROM $(vFilePath)
(ooxml, embedded labels, table is $(vSheet));

TotalsRow:
LOAD RowOrder
Resident Loop
Where FieldA = 'Totals';

LET vTotalsRow = Peek('RowOrder', 0, 'TotalsRow'); // Store the Rowno of the row where Where FieldA = 'Totals'

Drop Table TotalsRow;

//NoConcatenate // This is causingissue with concatenation of the succeeding tables after the the loop finishes one round
FinalTable:
Load *,
0 as DummyField // This Dummy field is to avoid Auto Concat of 'FinalTable' to 'Loop' Table
Resident Loop
Where RowOrder < '$(vTotalsRow)';
Drop table Loop;
//Drop Field DummyField;

LET vPowerFieldNameCounter = $(vPowerFieldNameCounter) - 1; // Increment/Decrement the Power Field counter Inside of the Loop

NEXT
TRACE End Loop;
//----------------------------------------------------------------------------//

Re: Dynamic Excel sheet names based on month

niranjana — Sun, 22 Feb 2026 14:27:03 GMT

Hi Qrishna!

1) Thanks for your reply. Your solution is working for my scenario now!. All the months are getting pulled. But the rows after "Total" are still getting pulled. That alone is not working though the code doesn't throw any error......

2) But I have one more issue. In all these monthly sheets, only one field is different after certain character...for example.... "Power from Source A1" in Jan-26, "Power from Source A2" in Feb-26 and so on....So the field is not getting pulled while completing for loop.....How to pull this field from all sheets in for loop?

3) Also, if in future more monthly sheets are to be added, how to write the code to dynamically pull all sheets (like using wildmatch ? )

Thanks in advance!!

Re: Dynamic Excel sheet names based on month

Qrishna — Sun, 22 Feb 2026 09:59:34 GMT

1. Field with Names '"Power from Source A1" in Jan-26, "Power from Source A2" in Feb-26 and so on' new requirement - Updated the code above to include your new dynamic fields.

2. Attach the Excel file with all Sheet Names and some future added dummy sheets - i need to see how the sheet names are changing

Re: Dynamic Excel sheet names based on month

niranjana — Tue, 24 Feb 2026 17:45:31 GMT

Hi Qrishna,

Thanks for your reply...

The issue of "Total" is still there......The rows after "Total" are still getting pulled. That alone is not working, though the code doesn't throw any error during load....

Thanks for any solution/help in advance!!

Niranjana

Re: Dynamic Excel sheet names based on month

marcus_sommer — Wed, 25 Feb 2026 06:14:47 GMT

Not wanted records could be filtered with a where-clause, for example:

... where Field <> 'total';
... where isnum(Field);
... where len(trim(Field));
... where rowno() < X;

or similar queries - maybe also in combination against n fields.

Re: Dynamic Excel sheet names based on month

niranjana — Wed, 25 Feb 2026 11:21:55 GMT

Hi Marcus,

Thanks for your reply... But I have 5 rows after "Total" under "Plants" column which I don't want to pull.

That is all the rows from "Total" and after that should be ignored. I only want to include rows : A,B,C

Plant

Total

Abstract

Source Power

..... etc....

Thanks for any solution in advance!!

Niranjana

Re: Dynamic Excel sheet names based on month

Qrishna — Wed, 25 Feb 2026 11:58:44 GMT

Hi - you have to incorporate the logic that i provided and not directly use the code, as my dummy data is not same as your data. if you need the exact solution, provide the original data or atleast a data set that matches your original data.

Re: Dynamic Excel sheet names based on month

marcus_sommer — Wed, 25 Feb 2026 14:01:34 GMT

The above mentioned filtering goes against the entire record and not only a single field. This means each field could be used to filter against isnum(), len(trim()), ... Beside this also direct value-filters are applicable, maybe with something like:

... where match(Plant, 'Total, 'Abstract', 'Source Power') = 0;

which means an excluding of values. If there are only a few wanted known values within the field these values might be queried as an include-filter.