Nettoyer la “cache” des tableaux croisés dynamiques 8

 dans Codes sources VBA  étiqueté Table pivot / Tableau croisé dynamique / Utilisation pratique

Avez-vous déjà vécu la situation où votre tableau croisé dynamique (TCD) affiche une valeur qui ne devrait pas se trouver dans vos données? Si oui, sachez qu'il s'agit d'un problème de mémoire cache des tableaux croisés dynamiques (TCD). Et que l'on peut facilement y remédier grâce à VBA.

Résoudre le problème de "mémoire cache" des TCD

Il arrive qu'après un certain temps et après des manipulations des données, le tableau croisé dynamique affiche des valeurs qui ne devraient plus y figurer. Ceci est surtout vrai lorsque vous travaillez avec les "Pivot tables" via VBA. Lors de l'énumération des champs et des valeurs disponibles (par ex. en créant un filtre du champ), les anciennes valeurs (déjà éffacées) peuvent resurgir. Ce qui est assez ennuyeux et peut même être dangereux (cela peux biaiser les résultats de vos calculs).

Il n'est pas très connu que les tableaux croisés dynamiques ont leur propre mémoire cache qui peut, avec temps, devenir problématique.

Le code qui suit "nettoie" tous les tableaux croisés dynamiques dans le Workbook. N'hésitez pas à l'utiliser au cas où vous construisez ou manipulez ces tableaux via le code VBA. Il vaut mieux prévenir que guérir.

Code VBA pour nettoyer la mémoire cache des tableaux croisés dynamiques

La procédure VBA qui suit fait une boucle dans tous les tableaux croisés dynamiques dans le Classeur et vide leur mémoire cache. Le code est prêt à l'emploi – il vous suffit de le copier & coller dans un Module de votre projet.

Sub NettoyerCacheTablePivot()
'par: Excel-Malin.com ( https://excel-malin.com )

    On Error GoTo ProcedureErreur

    Dim TablePivot As PivotTable
    Dim Feuille As Worksheet
   
    For Each Feuille In ActiveWorkbook.Worksheets
        For Each TablePivot In Feuille.PivotTables
            With TablePivot.PivotCache
                .MissingItemsLimit = xlMissingItemsNone
                .Refresh
            End With
        Next TablePivot
    Next Feuille 
    
    Set TablePivot = Nothing
    Set Feuille = Nothing
Exit Sub

ProcedureErreur:
    MsgBox "Une erreur est survenue..."
End Sub

Pour aller plus loin

29 0



Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.

8 commentaires sur “Nettoyer la “cache” des tableaux croisés dynamiques”

  • Répondre ↓
    Bory

    Bonjour,

    J'ai essayé de mettre ce code dans mon fichier mais cela me bloque à la ligne ".refresh" j'ai une erreur 1004.
    Je ne comprends pas pourquoi :'(
    Je suis sous office 2016, peut-être que c'est la raison ?

    • Répondre ↓
      excel-malin.com Auteur de l’article

      Bonjour Bory,
      je viens de tester le code dans Excel 2016 et il fonctionne correctement.

      Par contre j'ai réussi à reproduire cette erreur: elle survient lorsqu'une Feuille qui contient le (un des) tableaux croisés dynamiques est protégé par le mot de passe.
      C'est peut-être le cas dans votre fichier?
      Assurez vous que toutes les Feuilles de votre Classeur soient disponibles (= pas protégées).

      J'espère que ma réponse vous aidera. Bien à vous, Martin

      • Répondre ↓
        Bory

        Bonsoir Martin,

        Bizarre, je n'ai aucune protection de la feuille. Les particularités avec les TCDs que j'utilise dans ce fichier sont :
        – L'utilisation d'un add-on logiciel appelé MicroStrategy qui me créé mes TCDs dans le fichier excel
        – L'utilisation d'une option lors de la création du TCD qui me permet de faire un count distinct :/

        Cordialement

  • Répondre ↓
    O. Pinchart

    Un tout grand merci pour cette information. J'ai adapté le code pour mes besoins et le résultat fut exceptionnel. La création automatique de présentations PowerPoint me prenait près de deux heures et je ne comprenais pas pourquoi (pour 19 fichiers et 26 slides).

    J'ai rajouté la ligne de code suivant régulièrement car je fais beaucoup d'opération avec mon TCD =
    ActiveSheet.PivotTables("Tableau croisé dynamique13").PivotCache.MissingItemsLimit = xlMissingItemsNone

    Grâce à ce nettoyage du cache, l'ensemble tourne en une minute et trente secondes !!! Et encore, c'était si rapide que j'ai dû mettre une instruction "wait" entre mes différentes macros, sinon Excel ne prenait pas les bonnes valeurs.