Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

surynnchin
New Contributor III

Loop Field Macro not working

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 Valu...

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.

1 Solution

Accepted Solutions
autopilot
Contributor III

Re: Re: Loop Field Macro not working

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.

8 Replies
autopilot
Contributor III

Re: Loop Field Macro not working

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.

surynnchin
New Contributor III

Re: Loop Field Macro not working

I think i set the 1st 2 item correctly.

"Path" is exist in the application.

2014-02-21 15_34_22-List Box Properties [Sales Reps] - 7 linked objects.png

I'm waiting for your reply

autopilot
Contributor III

Re: Re: Loop Field Macro not working

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.

surynnchin
New Contributor III

Re: Loop Field Macro not working

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

autopilot
Contributor III

Re: Loop Field Macro not working

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.

surynnchin
New Contributor III

Re: Loop Field Macro not working

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

autopilot
Contributor III

Re: Loop Field Macro not working

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!

Not applicable

Re: Re: Re: Loop Field Macro not working

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

Community Browser