Mesure PowerPivot en VBA

Le 31/10/2024 à 11:17

Bonjour,

J'ai une macro VBA qui ouvre plusieurs fichiers d'un dossier pour inclure à chaque fichier un TCD avec des données filtrées et mises en forme.
La voici :

Sub TCD_AllFilesAllVersions()
'
' TCD1Fichier Macro
'
' Touche de raccourci du clavier: Ctrl+M

    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet

    folderPath = "L:\Pôle 3\EDS + TCD\EDS2 - Copie\"
    fileName = Dir(folderPath & "*.xlsx")

        Do While fileName <> ""

        Set wb = Workbooks.Open(folderPath & fileName)

        Set ws = wb.Sheets(1)

With ws

    Columns("T:T").Select
    Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("L:L").Select
    Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("T:T").Select
    Selection.NumberFormat = "0"
    Columns("L:L").Select
    Selection.NumberFormat = "0"
    Cells.Select
    Application.CutCopyMode = False
            wb.Connections.Add2 _
                "WorksheetConnection_Etat de secteur 2023-reportresu!$A:$AC", "", _
                "WORKSHEET;" & folderPath & "[" & fileName & "]Etat de secteur 2023-reportresu", _
                "Etat de secteur 2023-reportresu!$A:$AC", 7, True, False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections( _
        "WorksheetConnection_Etat de secteur 2023-reportresu!$A:$AC"), Version:= _
        xlPivotTableVersion15).CreatePivotTable TableDestination:="Feuil1!R3C1", TableName:= _
        "Tableau croisé dynamique1", DefaultVersion:= _
        xlPivotTableVersion15
    Sheets("Feuil1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = True
        .CompactRowIndent = 1
        .VisualTotals = False
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = True
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .DisplayEmptyRow = False
        .DisplayEmptyColumn = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .DisplayImmediateItems = True
        .ViewCalculatedMembers = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = True
        .RowAxisLayout xlCompactRow
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache. _
        RefreshOnFileOpen = False
    ActiveSheet.PivotTables("Tableau croisé dynamique1").RepeatAllLabels _
        xlRepeatLabels
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Indice CDU]")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Localisation]").CreatePivotFields
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "[Plage].[Localisation].[Localisation]").VisibleItemsList = Array( _
        "[Plage].[Localisation].&")
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Localisation]")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Statut prêt]").CreatePivotFields
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "[Plage].[Statut prêt].[Statut prêt]").VisibleItemsList = Array( _
        "[Plage].[Statut prêt].&", "[Plage].[Statut prêt].&[LEX]", _
        "[Plage].[Statut prêt].&[LPC]")
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Statut prêt]")
        .Orientation = xlPageField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Etat]").CreatePivotFields
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "[Plage].[Etat].[Etat]").VisibleItemsList = Array("[Plage].[Etat].&")
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Etat]")
        .Orientation = xlPageField
        .Position = 3
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Unica Sudoc]").CreatePivotFields
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "[Plage].[Unica Sudoc].[Unica Sudoc]").VisibleItemsList = Array( _
        "[Plage].[Unica Sudoc].&")
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Plage].[Unica Sudoc]")
        .Orientation = xlPageField
        .Position = 4
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields.GetMeasure _
        "[Plage].[Code-à-barres]", xlCount, "Nombre de Code-à-barres"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Measures].[Nombre de Code-à-barres]"), "Nombre de Code-à-barres"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields.GetMeasure _
        "[Plage].[Nb de prêts depuis 5 ans]", xlSum, _
        "Somme de Nb de prêts depuis 5 ans"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Measures].[Somme de Nb de prêts depuis 5 ans]"), _
        "Somme de Nb de prêts depuis 5 ans"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").CubeFields.GetMeasure _
        "[Plage].[Année de publication]", xlSum, "Somme de Année de publication"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").CubeFields( _
        "[Measures].[Somme de Année de publication]"), "Somme de Année de publication"
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "[Measures].[Somme de Année de publication]")
        .NumberFormat = "0"
        .Caption = "Moyenne de Année de publication"
        .Function = xlAverage
    ActiveSheet.PivotTables("Tableau croisé dynamique1").NullString = "0"
    ActiveSheet.PivotTables ("Tableau croisé dynamique1")
        Range("D9").Select
    Application.CutCopyMode = False
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ScopeType = xlFieldsScope

    End With
    Sheets("Feuil1").Select
    Sheets("Feuil1").Name = "TCD"
    Sheets("TCD").Select
    Sheets("TCD").Move After:=Sheets(2)

    End With

wb.Close SaveChanges:=True

    fileName = Dir
    Loop

End Sub

Je l'ai faite à partir de l'enregistreur de macros en ajoutant quelques lignes pour qu'il le fasse sur plusieurs fichiers.
Or, je souhaite créer, pour chaque fichier, une mesure PowerPivot nommée "Taux de rotation", avec cette formule : =([Somme de Nb de prêts depuis 5 ans]/5)/[Nombre de Code-à-barres]
Cette mesure serait à ajouter en colonne E du TCD.
Sur toutes les versions d'Excel que j'ai utilisé, l'enregistreur de macros ne tient pas compte des mesures créées via PowerPivot. Auriez-vous une solution ?

Merci !

Le 31/10/2024 à 11:40

Bonjour CelluleGrise,

Pourquoi ne fais-tu pas l’assemblage de tes fichiers avec Power Query plutôt qu’avec une macro ?

Les mesures ne sont faisables que dans Power Pivot, pas dans Power Query. Si tu ajoutes ta requête finale (d’assemblage de tes données) dans ton « modèle de données », tu pourras alors :

  • Y construire ta mesure ;
  • Créer un TCD global sur la base de ton modèle de données ;
  • Ta mesure apparaîtra dans les données à inclure dans ton TCD.

La seule macro qui sera éventuellement utile après ça sera celle qui déclenchera l’actualisation de tes données.

Rechercher des sujets similaires à "mesure powerpivot vba"