Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I am using Autopilot's macro in my application to loop field.
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.
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.
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.
I think i set the 1st 2 item correctly.
"Path" is exist in the application.
I'm waiting for your reply
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.
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
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.
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.
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!
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