Turning off Excel Pivot Table Sub Totals VB.NET

I am creating pivot tables in VB.NET and have run into a problem I did not think would be as difficult as it is turning out. When I create a pivot table it adds in subtotals for each row and I do not want that. In excel you just drag down the subtotals option and tell it to not display subtotals. I looked into the VBA for it and it is several lines long of this format:

     ActiveSheet.PivotTables("Main Highway Pivot").PivotFields("Division"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)

This is a surprising amount of code, but that is fine, so I have been trying something similar with my pivot table in VB.NET

With ptTable
.ManualUpdate = True
.PivotFields("Division").orientation = Excel.XlPivotFieldOrientation.xlRowField
.PivotFields("Net Sales Amt").orientation = Excel.XlPivotFieldOrientation.xlDataField
.PivotFields("Division").Subtotals = false
.ColumnGrand = False
.RowGrand = True
.ManualUpdate = False
End With

I have tried several different variations of the

 .PivotFields("Division").Subtotals = false

line, but have had little luck. Is this the right code and I am specifying the false part wrong? Why is there no

.subtotal = false

to make this easier?

In any case, it seemed like something simple to me, but after a good bit of digging I am not sure how to fix it, so any help would be appreciated.

Thank you as always SO

Answers


Here's a snippet of VBA (from http://www.pcreview.co.uk/forums/hide-subtotals-rowfields-pivot-table-t974940.html) which might help:

'=========================
Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub
'========================

Need Your Help

Find out whether Facebook Tab App was invoked by a bot or by a user

facebook google-app-engine http-headers facebook-apps bots

Facebook Canvas or Tab App is meant for Facebook users to invoke manually. I see a lot of calls to the app in quick bursts in a short period of time (about 20 to 40 in less than 2 or 3 seconds) lea...

How to package shared library in deb packages

linux package debian shared-libraries

My application uses some private shared libraries, which i need to package with the binary, and as per Debian Policy Rules

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.