1 Reply Latest reply: May 12, 2015 2:31 PM by Marcus Sommer RSS

    Loop over to catch the image of every sheet, but always failed to catch the first one on the list when change to another sheet

    Baiyang Xiang

      Hey Fellows


      So I got a list and four sheets in the document. I loop over the my work-group list on each sheet (every sheet produce metrics chart for the work group) then export the sheet image for every item in the list.

      Everything was good. But I just cannot catch the first one on the work-group list when I change to another sheet.


      this is a sample code for one sheet:

      Sub VMNH4()

      ' clean all current choices





      ' get a list of values in the center listbox

      set field1Values = ActiveDocument.Fields("Center").GetPossibleValues



      ' set the folder we want to export

      vFolder = ActiveDocument.GetVariable("vExportFolder").GetContent().String



      ' loop over the list

      for i = 0 to field1Values.Count - 1

        ActiveDocument.Fields("Center").Select field1Values.Item(i).Text


                 ActiveDocument.GetSheetByID("VMNH3").Activate 'go to the sheet VMNH3

                  set obj = ActiveDocument.GetSheetByID("VMNH3")' get the sheet as an object


               ' building the file name

               vCenterName = ActiveDocument.GetVariable("vCenter").GetContent().String

                  fileName =  vCenterName & " VMNH" & ".png"


               ' export the sheet object as pic format

               obj.ExportBitmapToFile vFolder & fileName



      end Sub


      in my button setting, i just run the macro for each sheet one by one. i think i should add something when i start loop over in another sheet? Please contact me if i have not made myself understood.