topic Re: having problem while implementing for loop in macro in QlikView

having problem while implementing for loop in macro

tripatirao — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

My requirement is when I click a button lets say 'Generate PPT' qlikview engine will select each values in country field one by one and it will send all charts to ppt.

i want to do this by using for loop in macro.

I am able to generate ppt for all country but not able implementing forloop to select country name automatically.

I have added for loop in macro but when running macro. One ppt is opening and qlikview application got hanged.

Please help me regarding this. I have attached macro code which i am using.

Regards

Tripati

Re: having problem while implementing for loop in macro

adamdavi3s — Fri, 14 Oct 2016 10:06:19 GMT

You should find a macro on here which will allow you to wrap your code in a selections loop, maybe just try it with a couple to start with.

I've certainly done things in the past which looped 100+ fields into excel documents so you should be ok!

https://community.qlik.com/docs/DOC-4870

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: having problem while implementing for loop in macro

Frank_Hartmann — Fri, 14 Oct 2016 10:07:20 GMT

Have a look here:

dynamically save various reports as pdf with different filename

hope this helps

Re: having problem while implementing for loop in macro

tripatirao — Sat, 15 Oct 2016 06:33:54 GMT

Dear Frank Hartman,

Sub Exportppt

Set PPApp = CreateObject("Powerpoint.Application")

PPApp.Visible = True ' Create a presentation

Set PPPres = PPApp.Presentations.Add

Set PPSlide = PPPres.Slides.Add(1, 1)

PPSlide.Shapes(1).Delete ' removes the title

PPSlide.Shapes(1).Delete ' removes the text box

ActiveDocument.GetSheetObject("TX6362").CopyBitmapToClipboard

ActiveDocument.GetApplication.WaitForIdle

with PPSlide.Shapes.Paste

.Left = 300

.Top = 25

end with

ActiveDocument.GetSheetObject("CH1708").CopyBitmapToClipboard

ActiveDocument.GetApplication.WaitForIdle

with PPSlide.Shapes.Paste

.Left = 50

.Top = 100

end with

Above is the code which i am using to export chart and textbox.

Now i have a COUNTRY field in application ,My requirement is when I click a button value of country will be automatically selected and save as different name in system.

I have for loop coding with me but it is not working when i try to implement.

Sub ExportCharts()

set field1Values = ActiveDocument.Fields("<your first field here>").GetPossibleValues

chartList = ActiveDocument.ActiveSheet.GetGraphs

for i = 0 to field1Values.Count - 1

for j = 0 to field2Values.Count - 1

for k = 0 to field3Values.Count - 1

ActiveDocument.ClearAll()

ActiveDocument.Fields("<your first field here>").Select field1Values.Item(i).Text

ActiveDocument.Fields("<your second field here>").Select field1Values.Item(j).Text

ActiveDocument.Fields("<your third field here>").Select field1Values.Item(k).Text

' ... export operations here...

end Sub

Please let me know how to implement above looping code into the application.

Regards

Tripati

Re: having problem while implementing for loop in macro

Frank_Hartmann — Sat, 15 Oct 2016 09:07:38 GMT

is this what you want to achieve?

If yes, then try attached sample.

Or do you need to export these Objects into one Powerpointdocument?

Hope this helps

Re: having problem while implementing for loop in macro

Frank_Hartmann — Sat, 15 Oct 2016 09:59:08 GMT

If you need to export all the objects into a single document, then use attached code.

Re: having problem while implementing for loop in macro

tripatirao — Tue, 18 Oct 2016 14:46:48 GMT

Dear Frank,

Thanks for your solution,

I have multiple chart to be exported to PPT by selecting country one after another automatically by qlikview.

I have attached the VB script code where i have mentioned all Chartid and Textbox id which need to be exported to PPT for each country

and stores in a specific location in system like

country1.ppt

country2.ppt

Best Regards

Tripati

Re: having problem while implementing for loop in macro

Frank_Hartmann — Tue, 18 Oct 2016 15:56:53 GMT

Have a look at the attached macro. Just copy an paste into your module.

but there are two things you will have to take care of:

1. create Qlikview Variable (Settings-->Variable Overview-->add)

vFinal=GetFieldSelections(PasteNameYourCountryFieldHere)

2 adjust this path according your local environment (This should be the Directory in which you want to save the reports):

this should loop through your countryfield and creates your report for all countries!

hope this helps

Re: having problem while implementing for loop in macro

Frank_Hartmann — Tue, 18 Oct 2016 16:24:28 GMT

3. here has to be the correct CountryFieldname:

Re: having problem while implementing for loop in macro

tripatirao — Wed, 19 Oct 2016 05:01:13 GMT

Dear Frank,

Thanks for your solution .Its working fine.

Regards

Tripati

Re: having problem while implementing for loop in macro

tripatirao — Wed, 19 Oct 2016 05:46:52 GMT

Dear Frank,

Now I have last requirement ,I have to send these PPT to email.

Can you please assist me how to do this.

Regards

Tripati

Re: having problem while implementing for loop in macro

Frank_Hartmann — Wed, 19 Oct 2016 06:12:17 GMT

to how many receivers do you have to send these reports?

One countryreport to one receiver or all countryreports to all receivers? ....

Please give more detail on your requirement

Re: having problem while implementing for loop in macro

tripatirao — Wed, 19 Oct 2016 06:49:16 GMT

Dear Frank,

Right now I have 12 country report with me. If I click a button all 12 reports will go to only one email id.

Kindly let me know any more information you want from my side.

Regards

Tripati

Re: having problem while implementing for loop in macro

Frank_Hartmann — Wed, 19 Oct 2016 07:06:17 GMT

I have attached the piece of code which you will have to paste/insert above the "End sub" statement of your export macro. You will just have to adapt your email, pass, smtpport, mailserver, mailsubject, mailbody and the paths of the generated reports.

hope this helps

Re: having problem while implementing for loop in macro

tripatirao — Wed, 19 Oct 2016 11:07:41 GMT

Dear Frank,

Thanks for your reply.

I have added the code given by you. But no luck.

After exporting ppt to desired folder it is asking for reciever mail id in qlikview front application.Once I enter the reciever email, it is coming back to vb script editor.

Note:I am doing entire things in local system not in server and the reciever emal id and sender email id domain is gmail.

For your reference I have attached the script.Please check and do the needful.

Regards

Tripati

Re: having problem while implementing for loop in macro

Frank_Hartmann — Wed, 19 Oct 2016 11:50:24 GMT

Fill in the correct gmail smtp settings:

mail Server = "smtp.gmail.com"

SMTPport = 465

Re: having problem while implementing for loop in macro

tripatirao — Thu, 20 Oct 2016 06:52:23 GMT

Dear Frank,

Thanks for help.

Code is working properly.

Just changesd SMTPport =25

Regards

Tripati