Hide Totals Row through VBS?

[Michael_Reese]

I am creating a straight table dynamically through the vb script, but can't seem to get rid of the totals row.  Any thoughts?

Thanks

[Michael_Reese]

Basically, I need the ability to set the totals mode to no totals on each expression, but I don't see an expression  property for that.

[Michael_Reese]

Just checking to see if anyone had any thoughts on this?

[Anonymous]

As far as I can tell you can't control that. You can switch between Expression total and the type of total that you want. BrutalSum decides between Expression Total and the type of total you want, while AggregationMode decides the type of Total:

set chart = ActiveDocument.Sheets("Main").CreateStraightTable

chart.AddDimension "ProductType"

chart.AddExpression "sum(Amount)"

chart.AddExpression "count(Customer)"

set p = chart.GetProperties

p.Expressions.Item(1).Item(0).Data.ExpressionData.BrutalSum = true

p.Expressions.Item(1).Item(0).Data.ExpressionData.AggregationMode = 5   'sum

chart.SetProperties p

Specific aggregation function to be used for totals when BrutalSum=true

0   =   Numeric count

1   =   Null count

2   =   Text Count

3   =   Total count

4   =   Missing count

5   =   Sum

6   =   Average

7   =   Std dev

8   =   Skewness

9   =   Kurtosis

10  =   Min

11  =   Max

12  =   Only value

13  =   Mode value

14  =   First string

15  =   Last string

[Michael_Reese]

Yes, I was looking at this.  Unfortunately, it doesn't have an option to show 'no totals'.

[kaushiknsolanki · Accepted solution]

Hi,

    Try below code.

     Sub Check

     set chart = ActiveDocument.ActiveSheet.CreateStraightTable

     chart.AddDimension "Year1"

     chart.AddExpression "1"

     set cp = chart.GetProperties

     set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData

     expr.UsePartialSum = false  //To disable the total sum.

     chart.SetProperties cp

     End Sub

Regards,

Kaushik Solanki

[Michael_Reese]

Thank you!

[rynedh_blueb]

The values for AggregationMode may have changed, in my system "Average" has a value of 3.

[rynedh_blueb]

The resource that I have found most useful is:

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel?view=excel-pia