Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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.

View solution in original post

8 Replies
Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Author

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