7 Replies Latest reply: Mar 16, 2014 9:50 PM by John M RSS

    Loop Field Macro not working

    Tan Soo Chin

      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
          Curt Daughtry

          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
              Tan Soo Chin

              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

                • Re: Re: Loop Field Macro not working
                  Curt Daughtry

                  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
                      Tan Soo Chin

                      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)




                      Set PPSlide = Nothing

                      Set PPPres = Nothing

                      Set PPApp = Nothing


                      End Sub

                        • Re: Loop Field Macro not working
                          Curt Daughtry

                          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.



                          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



                          END SUB


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

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