Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, everybody!
I've got a macro (VBScript) which programmatically changes certain expressions in charts and tables. Something like this (simplified):
Objects = ActiveDocument.ActiveSheet.GetSheetObjects
For j = lBound(Objects) To uBound(Objects)
Set obj = Objects(j)
If obj.GetObjectType = 11 or obj.GetObjectType = 10 then
Set o = ActiveDocument.GetSheetObject(obj.GetObjectid)
Set cp = o.GetProperties
For k = 0 To cp.Expressions.Count - 1
OldExpr=cp.Expressions.Item(k).Item(0).Data.ExpressionData.Definition.v
NewExpr=replace(OldExpr,"+","-")
cp.Expressions.Item(k).Item(0).Data.ExpressionData.Definition.v=NewExpr
Next
o.SetProperties cp
End if
Next
It works fine for stand-alone charts and tables, but not for those put inside containers. I've tried to loop through objects inside containers - like this:
If obj.GetObjectType = 36 then
Set ContainerObj = ActiveDocument.GetSheetObject(obj.GetObjectId)
Set ContProp=ContainerObj.GetProperties
For l = 0 to ContProp.ContainedObjects.Count - 1
Set o = ContProp.ContainedObjects.Item(l).Def
If o.GetObjectType = 11 or o.GetObjectType = 10 then
Set cp = o.GetProperties
For k = 0 To cp.Expressions.Count - 1
OldExpr=cp.Expressions.Item(k).Item(0).Data.ExpressionData.Definition.v
NewExpr=replace(OldExpr,"+","-")
cp.Expressions.Item(k).Item(0).Data.ExpressionData.Definition.v=NewExpr
Next
o.SetProperties cp
End if
Next
End if
but it throws an error "Object doesn't support this property or method: 'o.GetObjectType'". o.GetProperties doesn't seem to work for ContainedObjects, too. Tried it with or without Def - no success.
How can I programmatically change expressions for all charts and tables - including those placed inside containers?
Many thanks in advance!
OK, maybe the question was too verbose, so I'll try to put it as simple as possible.
How do we reach the expressions of charts and tables, placed inside containers? Enumeration of all the objects on the sheet (For j = lBound(Objects) To uBound(Objects)) doesn't reveal them - only stand-alone charts and tables.
For simplicity's sake - let's suppose we just want to see the expressions (from VBScript). For stand-alone charts and tables that's simple - but this simple approach doesn't work for those charts an tables inside containers.
I'm sure it ought to be possible - but I couldn't find any examples and wasn't able to make it up on my own.
Any help would be greatly appreciated!