topic Re: Macro Loop through Sales Codes in QlikView

Macro Loop through Sales Codes

johngouws — Fri, 29 Jul 2022 07:33:01 GMT

Good morning.

I have an existing working macro that distributes monthly reports to around 95 Clients. This month is the first time we exceed 100 Client Reports, 112 Clients. Unfortunately the generation of the pdf's end at 100 pdf's. At first I thought it was to do with our ISP, but even after commenting out the emailing portion, it only generates 100 pdf's.

This portion of the code is what I have been using for a while:

Sub DistributeReports()
ActiveDocument.ClearAll True

Set Custs = ActiveDocument.Fields("SCode").GetPossibleValues

For i = 0 to Custs.count -1
ActiveDocument.Fields("SCode").Select Custs.item(i).Text

Reports()
Next

ActiveDocument.ClearAll True

End Sub

sub Reports

.....

End Sub

As an alternative, I have also tried this code:

Sub DistributeReports()
ActiveDocument.ClearAll True

Set Custs = ActiveDocument.Fields("SCode").GetPossibleValues

Do while i < 120
i = i + 1
ActiveDocument.Fields("SCode").Select Custs.item(i).Text

Reports()

Loop

End Sub

sub Reports

.....

End Sub

Unfortunately the results are the same. I know it's a bit of a weird one, but if anyone has any thought on how I can overcome this problem, I would appreciate it very much.

Thank you.

Re: Macro Loop through Sales Codes

cwolf — Fri, 29 Jul 2022 08:34:53 GMT

Per default GetPossibleValues returns 100 values. You have use it with a value of a maximum number you want to get. For example:

Set Custs = ActiveDocument.Fields("SCode").GetPossibleValues(1000)

Re: Macro Loop through Sales Codes

johngouws — Fri, 29 Jul 2022 09:40:07 GMT

Hi @cwolf .

I did not know that, but now I do - I just ran it and it works like a charm.

Looking at the two code options, is the one 'better' or more technically correct to use than the other?

Thank you for the solution.

Re: Macro Loop through Sales Codes

cwolf — Fri, 29 Jul 2022 10:51:29 GMT

The 1st variant is correct and also better.
The 2nd variant is incorrect anyway.

The second variant should look like this:

Sub DistributeReports ActiveDocument.ClearAll True Set Custs = ActiveDocument.Fields("SCode").GetPossibleValues i=0 Do while i < Custs.Count ActiveDocument.Fields("SCode").Select Custs.item(i).Text Reports() i = i + 1 Loop End Sub

Re: Macro Loop through Sales Codes

johngouws — Fri, 29 Jul 2022 11:01:54 GMT

Thanks a lot, I will stick to the first.