topic Re: Loop Field Macro not working in QlikView

Loop Field Macro not working

Anonymous — Thu, 20 Feb 2014 12:53:48 GMT

Hi Community,

I am using Autopilot's macro in my application to loop field.

VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Value

I have followed all neccesary steps but it's not working. Attached sample application with the macro. Here i want to loop the field "Path (Sales Reps)" so i set the field Path (Sales Reps) as my vfname and also the chart id= CH36.

Re: Loop Field Macro not working

Anonymous — Thu, 20 Feb 2014 14:40:22 GMT

Ok, here are a few things that I noticed.

1. The variable vfname must be set to a field name that exists in the application. You had it set to path which was not a field in the qv app. Try something like Year or Month

2. The module security settings must be set to "Sytem Access" and "Allow System Access" in the dropdowns above the OK button in the edit module dialog. You had it set to the default which is Safe Mode.

3. Even with the above being set correctly, I ran into an unexplained error in the macro if I only selected only value in the field set as vfname. For example, if I set vfname to Month and selected one value in that field, say 'Jan', and ran the macro it has an error when attempting to delete the extra (blank) worksheets in the excel file.

The only immediate resolution is ensure that at least 2 values are selected in whatever field that vfname is set to. In that case, it does perform as expected without errors. When I designed it, I intended it to be used for successive (looping) exports to excel. That said, I don't think that it shouldn't be capable as well of performing a single value export.

I'll continue to test the script to find a working solution. But for now, you can either comment out the script that deletes the extra sheets or select multiple values for export.

I'll let you know when I find the resolution.

Re: Loop Field Macro not working

Anonymous — Fri, 21 Feb 2014 11:32:54 GMT

I think i set the 1st 2 item correctly.

"Path" is exist in the application.

I'm waiting for your reply

Re: Re: Loop Field Macro not working

Anonymous — Fri, 21 Feb 2014 18:23:49 GMT

Thanks - overlooked the "Path" field but the security was not set correct when I opened the demo app.

Okay - I've got a solution!

The issue was the portion of script trying to cleanup the extra worksheets. If you tried to run it with one value selected, then it would lose track of how many worksheets existed and then reference ones that did not exist anymore.

Attached is the revised script. I'll post the revision in the main document as well.

Re: Loop Field Macro not working

Anonymous — Sat, 22 Feb 2014 15:35:38 GMT

Thank you so much. Your script works like a charm.

By the way, although this seem like out of the topic but do you have any macro script which would allow me to export all the tabs in the application to ppt in different slides?

I got this from community but it will only export the active sheet and also the image got truncated.

sub exportppt

Set objPPT = CreateObject("PowerPoint.Application")

objPPT.Visible = True

Set objPresentation = objPPT.Presentations.Add

Set PPSlide = objPresentation.Slides.Add(1,11)

ActiveDocument.ActiveSheet.CopyBitmapToClipboard

PPSlide.Shapes.Paste

Set PPSlide = Nothing

Set PPPres = Nothing

Set PPApp = Nothing

End Sub

Re: Loop Field Macro not working

Anonymous — Mon, 24 Feb 2014 16:05:42 GMT

Great - glad to help!

In fact, yes. Here is the link to the post with the PPT extract utility. Haven't reviewed it in a while so it may need some tailoring to meet your specific needs.

http://community.qlik.com/docs/DOC-3774

I know that you'll need to add a step that allows you to walk through the sheets at a minimum as originally I set it up to work all the objects from one sheet.

Below is a little script that I used to set all the sheets' tab color and background in production apps to maintain consistency. It illustrates how to move through the sheets in the app using a for-next loop.

SUB dynTabColor

FOR i = 0 to activedocument.noofsheets - 1

sheetid = activedocument.getlayout.activesheetid

SET mysheet=activedocument.activesheet

SET sp=mysheet.getproperties

sp.tabattr.bgcolor.primarycol.iscalculated = TRUE

sp.tabattr.bgcolor.primarycol.colorexpr.v = "if(GetActiveSheetId() like '*"&sheetid&"' ,white(),rgb(57,103,139))"

sp.tabattr.fgcolor.primarycol.iscalculated = TRUE

sp.tabattr.fgcolor.primarycol.colorexpr.v = "if(GetActiveSheetId() like '*"&sheetid&"' ,rgb(0,71,125),white())"

sp.tabattr.mode = 2

mysheet.setproperties sp

activedocument.nexttab

NEXT

END SUB

Let me know if you post another discussion regarding the ppt tool and I'll see if I can help.

Re: Loop Field Macro not working

Anonymous — Fri, 28 Feb 2014 03:11:24 GMT

Honestly i got no idea how to modify your script to fulfill my needs, it's even too me quite a while to understand QlikView scripting as well.

I post another discussion here, please have a look, hope you can help.

Export all sheets to ppt

Re: Loop Field Macro not working

Anonymous — Mon, 03 Mar 2014 14:16:10 GMT

I replied to your post...the macro should allow you to copy an image to a ppt slide for each sheet in the qv app.

Hope that helps!

Re: Re: Re: Loop Field Macro not working

Mon, 17 Mar 2014 01:50:26 GMT

Hi Autopilot

Your macro is working for me to loop field and export. But i also want to have a 'TOTAL' sheet.

Let say i want loop my country field: Country A, Country B and Country C, so the TOTAL is sum of Country A, Country B and Country C. Please see my attachment. Hope you can help.

John