How to set always show for Pivot Table (Macro)

[Anonymous]

Hi experts,

I am working on a report cloning button to clone the existing report and paste the clone(collaboration object) into another sheet. Currently, I have pivot tables initially set with conditional show, I have created a macro function to clone an object with the conditional show but I need to remove the conditional show in the new object and change it to always show so it will be displayed in the destination sheet because the new object has exactly 100% same properties as the original object has. I have tried a few ways to do it but did not have any luck XD, this is the code that Im using currently to change the conditional show to always show. "prop.Layout.Frame.Show.Always=true"

Below is the script to give you guys better understanding about my problem, hope it helps.

Thanks.

Sub cloneReport
          Dim currentID 'as String
          currentID= ActiveDocument.Variables("varRptID").GetContent().String
          if((currentID="-"))then
          msgbox("Please select a report")
          exit sub
          else
          ' direct to customizable report tab
          ActiveDocument.ActivateSheetByID "SH11"
          'set x = clone(currentID)
          clone(currentID)
          end if
End sub
Function clone (objectID)
          tempfile = "qvtemp-" & ActiveDocument.Evaluate("purgeChar(osUser(),'\')") & ".xml"
          ' Write out the model object and read it back. This does *not* require macro system access.
          ActiveDocument.GetSheetObject(objectId).WriteXmlPropertiesFile tempfile
          ActiveDocument.Sheets("Saved Report").CreateObjectFromXmlPropertiesFile tempfile
          ' Trick is getting a handle to the newly created object. 
          ' From testing, it appears the new object is always first in the collection.
          ' Will this always be true?
          sObjects = ActiveDocument.ActiveSheet.GetSheetObjects  'Objects this sheet
          set cloneObj = sObjects(lBound(sObjects))                    ' Get first obj in collection
          ' Now we want to turn Show on for the new object so it's visible.
          set prop = cloneObj.GetProperties 
          prop.Layout.Frame.Show.Always=true
          cloneObj.SetProperties prop
End Function

[kaushiknsolanki]

HI,

   try below one.

set chart=ActiveDocument.GetSheetObject("CH01")

set cp=chart.GetProperties

setdims=cp.Dimensions

setexpr=cp.Expressions.Item(0).Item(0).Data.ExpressionData

dims(0).Show.Type=1 '0=false, 1=true, 2=cond

expr.Show.Type=2

expr.Show.Expression="sum(a)<10000"

chart.SetProperties cp

Regards,

Kaushik Solanki

[Anonymous · Accepted solution]

Hi Kaushik.solanki,

Thanks for helping but unfortunately it didn't solve my problem, maybe you could not understand my question because of my poor english, thanks anyway my problem is solved.:D

Found the problem, the problem was because of the statement below did not really get the new created object

sObjects = ActiveDocument.ActiveSheet.GetSheetObjects  'Objects this sheet
set cloneObj = sObjects(lBound(sObjects))

so I added in a For loop to get the object id of the last pivot table object type which is the new created  object and set the object to always show. The script is attached below.

Sub cloneReport
          Dim currentID 'as String 
          currentID= ActiveDocument.Variables("varRptID").GetContent().String
          ' Make a clone of report. The clone will automatically get a new id.
          if((currentID="-"))then
          msgbox("Please select a report")
          exit sub
          else
          'set x = clone(currentID)
          clone(currentID)
          end if
End sub
Function clone (objectID)
          ' Need some work on the filename. Make sure two users don't collide and that the
          ' file is created in a temp area.
          ' This will create a filename like "qvtemp-UPIwts03.xml"
          tempfile = "qvtemp-" & ActiveDocument.Evaluate("purgeChar(osUser(),'\')") & ".xml"
          ' Write out the model object and read it back. This does *not* require macro system access.
          ActiveDocument.GetSheetObject(objectId).WriteXmlPropertiesFile tempfile
          ActiveDocument.Sheets("Saved Report").CreateObjectFromXmlPropertiesFile tempfile
          ' direct to customizable report tab
          ActiveDocument.ActivateSheetByID "SH11"
          sObjects = ActiveDocument.ActiveSheet.GetSheetObjects  'Objects this sheet
          'get the object id of the last pivot table object
          For i = lBound(sObjects) To uBound(sObjects)
      set obj = sObjects(i)
      if obj.GetObjectType = 10 then          'objectType=10=pivot tables
                          id = obj.getObjectId
      end if
          next
          set finalObj = ActiveDocument.getSheetObject(id)
          set prop = finalObj.GetProperties
          prop.GraphLayout.Frame.Show.Always=True
          finalObj.SetProperties prop
          finalObj.Restore 'the unminimized the object
End Function