For each variable loop through code

[gerhardl]

Hi,

In my load script I set a certain number of variables which I load from an excel file, like_this.

Currently there are always 10 variables.

I then have a macro which creates an excel file for each of the stores (the variables). But I currently use the same code 10 times to create the 10 different files. I need to cater for the fact that there can be any number of variables in the excel file, so I need to loop through this same code as many times as there are variables, and each time increase the number shown in red below:

ActiveDocument.Fields("Store Number").Select vStore1.GetContent.String

'Select 1st Store

File1 = Location & "Referred Apps Store "& vStore1.GetContent.String & " - " & strYear & strMonth & strDay

obj.ExportBiff File1 & ".xls"

ActiveDocument.Fields("Store Number").Select vStore2.GetContent.String

'Select 2nd Store

File2 = Location & "Referred Apps Store "& vStore2.GetContent.String & " - " & strYear & strMonth & strDay

obj.ExportBiff File2 & ".xls"

I've attached the full macro for reference. If someone could perhaps edit the code in the txt file it will really help a lot.

Thanks,

Gerhard

[whiteline]

Hi.

And so, what is the problem ? It seems that writing a loop on VB is not a QlikView question.

Use loop, one set of variables and string functions to dynamically get the variable names 'variableN'.

[gerhardl]

Hi,

I would not have asked if I knew how to do that. And there are literally hundreds of VB questions on here, because it is VB on QlikView.

Your answer is a little vague for me but I appreciate the response. If someone could help me with the code I would appreciate that even more.

[whiteline]

Hi.

Yes, but your question is really refer to VB syntax.

Post your tries with the loop.

[MarcoWedel]

Hallo Gerhard,

what are you doing with those variables?

Maybe there is a solution without creating multiple variables?

regards

Marco

[gerhardl]

Hi Marco,

The variables are store numbers, and for each of the stores a report listing all outstanding applications are created every morning and then emailed to each store individually.

We have hundreds of stores, but this report goes only to 10 or so stores (those with the most applications), so I want business users to be able to choose which stores get the reports - they can do this simply by updating the store number and email address in the excel file.

It works perfectly, except that it HAS to be 10 stores at the moment - I want the user to be able to add or remove stores, so the QlikView macro needs to loop X number of times, based on how many variables were loaded.

Regards,

Gerhard

[mattydolan]

In case anyone else finds this via google. 

Use: Document.GetVariableDescriptions

APIGuide has an example (attached)