none
EXCEL VBA Trying unsuccessfully to modify macro to creates multiple pivot tables based on table, to PTs using Data model/External connection RRS feed

  • Question

  • Background

    I have a macro to create a large number of pivot tables (too many in timescale to do manually) with the parameters for each pivot table is listed in a table, 1 row per pivot. All working ok with that part.  

    I have two types of pivot to create - one using power pivot data model for Table x as it needs DistinctCount and one based on  just table X

    My knowledge on using the datamodel/powerpivot is limited to just ticking the box add to data model when manually creating a pivot table.

    My very out of date programming skills are fine for setting the logic but I rely heavily on recording and google for the VBA

    I'm using Excel with Office 365 ProPlus licence

    I have the picking up the pivot table requirements from my table and creation of table based pivotable working fine.  Am now trying to do the same for table added to the data model. From recording I've tried to modify to using variables for the creation but having no success with that let alone how to populate with fields.  I have googled and tried sample code but not understanding it and not worked.

    I'm now confused and at a total loss how to reproduce the pivot table set up when based on the data model so any help on coding this bit would be greatly appreciated.  

    Sub DatamodelPivot()
    
    '
    'some of the definitions
    '
        Dim pC As PivotCache
        Dim pt As PivotTable
        Dim pf As PivotField
       
        Dim wksSource As Worksheet  'source where data table is
        Dim wksDest As Worksheet    'destination for pivots
        Dim ExtSrcTbl As WorkbookConnection ' have tried different types and just confused myself!
    
    
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
             ActiveWorkbook.Connections(ExtSrcTbl)).CreatePivotTable TableDestination:=InsertPos, _
            TableName:=sCustomList(i, PtNamePos), DefaultVersion:=6
    
    Set pt = ActiveSheet.PivotTables(sCustomList(i, PtNamePos))
    '
    '  following code from working sub using a table version
    '
                  With pt
                     .ColumnGrand = False
                     .RowGrand = False
                     .RowAxisLayout xlTabularRow
                     .RepeatAllLabels xlRepeatLabels
                     .HasAutoFormat = False
    
                   End With
    '
    '   for count is position and number of fields in each row of field combination array
    '   Create each (row) field
    '
                    With pt
                         For x = StartFld To StartdataFld1 - 1
    
                            With .PivotFields(sCustomList(i, x))
                                 .Orientation = xlRowField
                                 .Position = x - 4
                            End With
                         Next x
                    End With
    '
    ' if data field blank skip???
    'else get field name and type
    '
            If sCustomList(i, StartdataFld1 + DFCalcOff) <> "" Then
                    dataFunc = sCustomList(i, StartdataFld1 + DFCalcOff)
                    dataCaption1 = sCustomList(i, StartdataFld1 + DFCapOff)
                    dataFld = sCustomList(i, StartdataFld1 + DFFldOff)
                    FuncOrient = sCustomList(i, DFOrient)
        
                     ActiveSheet.PivotTables(sCustomList(i, 2)).AddDataField ActiveSheet. _
                     PivotTables(sCustomList(i, 2)).PivotFields(dataFld), _
                     "Count of", xlCount
    '
    'sort out field parameters - really needs to be loop and more efficient
    '
                     For z = 0 To 7
                            If FuncList(z, 0) = dataFunc Then
                                  FuncFormat = FuncList(z, 1)
                                  FuncNum = z + 1
                            End If
                     Next z
                            Select Case FuncNum
                                    Case 1
                                        With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                            .Function = xlSum
                                            .NumberFormat = FuncList(z, 1)
                                             .Caption = dataCaption1
                                        End With
                                    Case 2
                                        With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                            .Function = xlCount
                                            .NumberFormat = "0"
                                             .Caption = dataCaption1
                                         End With
                                    Case 3
                                         With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                             .Function = xlAverage
                                            .NumberFormat = "0.0%"
                                            .Caption = dataCaption1
                                          End With
                                    Case 4
                                         With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                             .Function = xlDistinctCount
                                             .NumberFormat = "0"
                                             .Caption = dataCaption1
                                        End With
                                    Case 5
                                         With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                            .Orientation = xlDataField
                                             .Function = xlMax
                                              pf.NumberFormat = "dd/mm/yy"
                                              .Caption = dataCaption1
                                         End With
                                    Case 6
                                         With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                            .Function = xlMax
                                            .NumberFormat = "dd/mm/yy%"
                                             .Caption = dataCaption1
                                         End With
                                    Case 7
                                         With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                             .Function = xlMin
                                            .NumberFormat = "dd/mm/yy%"
                                             .Caption = dataCaption1
                                         End With
                                    Case 8
                                         With ActiveSheet.PivotTables(sCustomList(i, 2)).PivotFields("Count of")
                                             .Orientation = xlDataField
                                            .Function = xlMin
                                            .NumberFormat = "dd/mm/yy%"
                                             .Caption = dataCaption1
                                         End With
                              End Select
        End If
    
                            On Error Resume Next
                              pt.ManualUpdate = True
                              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
                              pt.ManualUpdate = False
                           
                            If (sCustomList(i, DFOrient) = "Row") Then
                                        With ActiveSheet.PivotTables(sCustomList(i, 2)).DataPivotField
                                              .Orientation = xlRowField
                                        End With
    
                                    End If
                                                                    
    '
    'add slicer
    '
     ActiveWorkbook.SlicerCaches(SlicerName).PivotTables.AddPivotTable _
     ActiveSheet.PivotTables(sCustomList(i, PtNamePos))
    End Sub


    • Moved by Perry-Pan Wednesday, January 2, 2019 2:28 AM related with VBA code
    Tuesday, January 1, 2019 7:42 PM

All replies

  • Hi JaneOAP,

    Thank you for visiting Excel IT forum. Here we mainly focus on issues and feedback regarding Excel desktop client. From the description, it was more related with VBA code. I'll help move this thread to Excel for Developer forum for a more dedicated response.

    Thank you for your understanding and support.

    Regards,

    Perry


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, January 2, 2019 2:28 AM