access2010 ピボットテーブルのVBAコーディング

とりあえず、単純なところまで。

Option Compare Database
Option Explicit
'参照設定：Microsoft Office XP Web Components
'C:\Program Files\Microsoft Office\Office14\OFFOWC.DLL

Private Sub inipvt()
    Dim fst As OWC10.PivotFieldSet

    With Me.PivotTable.activeview
        Do Until .RowAxis.FieldSets.Count = 0
            Set fst = .RowAxis.FieldSets(0)
            .RowAxis.RemoveFieldSet fst
        Loop

        Do Until .ColumnAxis.FieldSets.Count = 0
            Set fst = .ColumnAxis.FieldSets(0)
            .ColumnAxis.RemoveFieldSet fst
        Loop

        Do Until .FilterAxis.FieldSets.Count = 0
            Set fst = .FilterAxis.FieldSets(0)
            .FilterAxis.RemoveFieldSet fst
        Loop

        Do Until .DataAxis.Totals.Count = 0
            .DataAxis.RemoveTotal .DataAxis.Totals(0)
        Loop
        
        Do Until .Totals.Count = 0
            .DeleteTotal .Totals(0).Name
        Loop
    End With
End Sub

Private Sub Form_Load()

    Call inipvt
    
    Dim Fst1 As OWC10.PivotFieldSet
    Dim fld As OWC10.PivotField
    Dim ttl As OWC10.PivotTotal

    With Me.PivotTable.activeview
        Set Fst1 = .FieldSets("F_num1")
        .DataAxis.InsertFieldSet Fst1
        Set Fst1 = .FieldSets("F_num2")
        .DataAxis.InsertFieldSet Fst1
        
        Set Fst1 = .FieldSets("F_date")
        .RowAxis.InsertFieldSet Fst1
        
'        .FilterAxis.InsertFieldSet Fst1

        Set fld = .FieldSets("F_num1").Fields("F_num1")
        .AddTotal "合計：F_num1", fld, plFunctionSum
        Set ttl = .Totals("合計：F_num1")
        .DataAxis.InsertTotal ttl
        Set fld = .FieldSets("F_num2").Fields("F_num2")
        .AddTotal "合計：F_num2", fld, plFunctionSum
        Set ttl = .Totals("合計：F_num2")
        .DataAxis.InsertTotal ttl
    End With
End Sub