3 Replies Latest reply: Sep 18, 2011 4:41 AM by Jonathan Dienst RSS

    code smaple from APIguide fails. - (getsheetobjects method code)

    DANIEL CHOTZEN

      hi,

      I need to access the background color and text color of a Pivot/Straight table expression cells

      but the code sample for these properties (and few others) is not working

      it usues a member that doesnt exist (ExpressionVisuals) and has other errors

      can anyone figure out the correct code?

      thanks in advance

      Mansyno

        • code smaple from APIguide fails. - (getsheetobjects method code)
          Jonathan Dienst

          Hi Mansyno

           

          I suggest that you post the code that is not working so that someone can help you.

           

          Regards

          Jonathan

            • code smaple from APIguide fails. - (getsheetobjects method code)
              DANIEL CHOTZEN

              hmm well it is in the API guide .qvw file but anyway here it is

              btw i have ver 10. sp3

               

              function Rand (Lo, Hi)

                  Rand = Lo + Rnd() * (Hi - Lo + 1) - 0.5

              end function

               

              sub randpiv

              rem  ** set random colors in all expression        **

              rem  ** cells in all pivot tables on active sheet. **

              Objects = ActiveDocument.ActiveSheet.GetSheetObjects

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

                 If Objects(i).GetObjectType = 10 Then   'pivot tables

                    set Pivot = Objects(i)

                    set y = Pivot.GetProperties

                    set e = y.ExpressionVisuals

                    for k = 0 to e.Count-1

                       set f = e.Item (k)

                    set v = f.CellVariations (0)

                       v.FgColor.Col = RGB(Rand(0, 127), Rand (0, 127), Rand (0, 127))

                       v.BgColor.Col = RGB(Rand(128,255), Rand (128,255), Rand(128,255))

                       v.TotalFgColor.Col = RGB(Rand(0, 127), Rand(0,127), Rand(0,127))

                       v.TotalBgColor.Col = RGB(Rand(128,255), Rand(128,255), Rand(128,255))

                    next

                    y.TableProperties.StyleNumber = -1

                    Pivot.SetProperties (y)

                  end if

              next

              end sub

                • code smaple from APIguide fails. - (getsheetobjects method code)
                  Jonathan Dienst

                  Hi

                   

                  I discovered that the posted code is in the API guide for the CellVariations method. It may be in the API guide, but its broken in several ways.

                   

                  First off - the member name is ExpressionVisual (no 's').

                  Second - ExpressionVisual is not a member of the properties object returned by GetProperties. The API guide does not document which objects have ExpressionVisual as a member, but the code sample has (adapted to your code):

                   

                  set y = Pivot.GetProperties

                  set e = y.Expressions.Item(0).Item(0).Data.ExpressionVisual

                   

                  Third - ExpressionVisual does not have a Count member. It does have an AttributeExpressions memer which is an array of attribute expressions.

                   

                  CellVariations and ExpressionVisual are not fully documented in the API guide, so getting them to work will take some experimenting.

                   

                  Jonathan