2 Replies Latest reply: Mar 20, 2015 9:19 AM by Marcus Sommer RSS

    Vbscript get all sheet objects and find by part of ID

    Igor Zhilin

      Dear Community:

       

      In each sheet, I need to find object based on part of ID. E.g. in each sheet, I will have a text object with ID like "HEADER_XX". I intend to assign a trigger on every sheet activation, which will change the text of the object to the name of the sheet.

       

      What I have in mind (mock-up code):

      set s = ActiveDocument.ActiveSheet
      title = s.GetProperties.Name

      for i = 1 to ActiveDocument.ActiveSheet.SheetObjects.Count '<< NOT WORKING OF COURSE

           if find(ActiveDocument.GetSheetObject(i).Name, "HEADER") then

                set mytext = s.SheetObjects("TX30")

                  mytext.SetText(title)

           end if

      next i

       

       

       

      What I have now is:

      Sub ChangeTitle()

      set s = ActiveDocument.ActiveSheet
      title = s.GetProperties.Name

      ' text object in the sheet is hard-coded
      set mytext = s.SheetObjects("TX30")      ' this to be defined dynamically
                                                                            ' e.g. find(ObjectID, "HEADER") <> 0
      mytext.SetText(title)

      End Sub

      Sub ChangeTitle()

      set s = ActiveDocument.ActiveSheet
      title = s.GetProperties.Name

      ' text object in the sheet is hard-coded
      set mytext = s.SheetObjects("TX30")      ' this to be defined dynamically
                                              ' e.g. find(ObjectID, "HEADER") <> 0
      mytext.SetText(title)

      End Sub

        • Re: Vbscript get all sheet objects and find by part of ID
          Igor Zhilin

          UPD: This is NoOfSheetObjects

          Took some effort to find it in the QV API reference:

           

          move all objects on active sheet 60 docunits down and 45 right

          set sh = ActiveDocument.ActiveSheet

          for i = 0 to sh.NoOfSheetObjects-1

              set obj = sh.SheetObjects(i)

              set fr = obj.GetFrameDef

              pos = fr.Rect

              pos.Top = pos.Top + 60

              pos.Left = pos.Left + 45

              obj.SetFrameDef fr

          next

          • Re: Vbscript get all sheet objects and find by part of ID
            Marcus Sommer

            I think this code-snippet will be helpful for you to loop through all sheets and all sheet-objects:

             

            sub x

             

            for i = 0 to ActiveDocument.NoOfSheets - 1

                set s = ActiveDocument.GetSheet(i).GetProperties

                set t = ActiveDocument.GetSheet(i)

                'msgbox(s.name)

                t.Activate

               

                Objects = ActiveDocument.ActiveSheet.GetSheetObjects

                For j = lBound(Objects) To uBound(Objects)

                    set obj = Objects(j)

                      if obj.GetObjectType = 6 then          'text boxes

                          str = str & obj.GetObjectid & chr(10)

                      end if

                next

            next

             

            'msgbox str

             

            end sub

             

            But I think you didn't need it for your goal to add the sheet to the caption from a textbox. For this you could use:

             

            = 'Header ' & getactivesheetid()

             

            - Marcus