5 Replies Latest reply: Jun 18, 2012 5:06 AM by Krishnaroopa Senthilkumar RSS

    macro to list all charts on sheet

    shanikia

      hi all,

      I'm trying to dynamically build buttons to access charts on another sheet. I am having trouble writing a macro to get a list of all of the charts on a particular sheet. I can get all of the objects, but how would I get a list of the charts and be able to access properties such as chart name, id and conditional expression for the visible property.

      thanks so much,
      Shanikia

        • macro to list all charts on sheet
          Neil Miller

          You can get the info you want by utilizing GetSheetObjects and GetObjectType (see the API Guide for more detail).

          Here is an example that will display each chart Id in a message box:

          Sub GetCharts
          Objects = ActiveDocument.ActiveSheet.GetSheetObjects
          For i = lBound(Objects) To uBound(Objects)
          If obj.GetObjectType = 11 'Charts Then
          msgbox("Chart: " & obj.GetObjectId)
          End If
          next
          End Sub


          Once you have the object Id of the chart, you will use GetProperties. Then you can manipulate the properties and use SetProperties.

            • macro to list all charts on sheet
              shanikia

              Thanks NMiller...

              I thought I had the same syntax, but yours works and mine didn't so...just had to add an assignment:

               

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

              set obj = Objects(i)

               

              If



               

               





               

              obj.GetObjectType >= 10 Then ' All Chart types("Chart: " & obj.GetCaption.Name.v & " ID: "&obj.GetObjectId)

              msgbox

               

               



               

               

               

               



               

               

              End

               

               

              If

               

               

               

               

              next

               



               

              • macro to list all charts on sheet
                Marcel Olmo

                Hey NMiller, thanks for your contribution.

                I'm just wondering what if I would like to get all the objects of the document. Not only for the activated sheet.

                How can I do that?

                Many thanks in advance!!!

                  • macro to list all charts on sheet
                    Neil Miller

                    In order to get all objects, I think you would need to loop through all sheets and for each sheet use something similar to the routine above. GetSheetObjects is a method of a sheet, so you can't use ActiveDocument.GetSheetObjects.

                    Here is an example:

                    Sub GetCharts
                    For j = 0 to numberOfSheets //I'm not sure how to determine this
                    Objects = ActiveDocument.GetSheet(j).GetSheetObjects
                    For i = lBound(Objects) To uBound(Objects)
                    If obj.GetObjectType = 11 'Charts Then
                    msgbox("Chart: " & obj.GetObjectId)
                    End If
                    next
                    Next
                    End Sub


                    Here, you would loop through all sheets. I didn't find a property which defined how many sheets there were in the document, but there is probably a way to figure it out.

                  • Re: macro to list all charts on sheet
                    Krishnaroopa Senthilkumar

                    set Objects = ActiveDocument.getSheet(0).GetSheetObjects

                     

                    or

                     

                    ActiveDocument.ActiveSheet.GetSheetObjects

                     

                    Both is not working.. I am getting an error - "Object Required"

                     

                    Please help

                     

                    regards

                    KRS