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 de données, le tableau croisé dynamique affiche des valeurs qui ne devraient plus y figurer. Ceci est surtout vrai lorsque vous travaillez avec les filtres ou si vous gérez 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à effacé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 (mémoire temporaire) qui peut, avec le temps, devenir problématique.
La procédure VBA qui suit "nettoie" tous les tableaux croisés dynamiques dans le Classeur (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. Le mieux c'est de lancer cette procédure avant de manipuler le tableau croisé dynamique (par exemple dans une boucle, lancez le au début de chaque itération).
Pas familiers avec VBA? Voici comment procéder…
Si vous ne vous y connaissez pas en VBA et vous voulez résoudre ce problème uniquement avec Excel, c'est également possible…
- Effectuez le click droit sur le Tableau croisé dynamique
- Choisissez "Options du tableau croisé dynamique"
- Choisissez le volet "Données"
- Dans la partie "Conserver les éléments supprimés de la source de données", pour l'option "Nombre d'éléments à retenir par champ", choisissez "Aucun"
- Ensuite, il faudra encore Rafraîchir le TCD (click droit sur le TCD, puis "Actualiser")
Sachez que vous devez effectuer cette manipulation pour chaque Tableau croisé dynamique séparément.
Si votre Classeur contient plusieurs TCD, vous pouvez utiliser la procédure VBA qui suit…
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
Vous pouvez aussi adapter le code pour nettoyer la mémoire cache d'un tableau pivot en particulier. En ciblant un TCD particulier, vous gagnez du temps en ne vidant le cache que de ce TCD particulier.
Pour aller plus loin en VBA et dans la gestion des tableaux croisés dynamiques
Voici quelques articles et tutoriels pour vous aider à mieux gérer les TCD et également pour approfondir vos connaissances de VBA…
- Grand tutoriel sur le Tableau croisé dynamique dans Excel
- Afficher les détails des Tableaux croisés dynamiques dans un Classeur
- Autres Codes sources VBA utiles
- Liste de toutes les fonctions VBA
- Page web de Microsoft consacrée à l'Objet
PivotCache - et aussi… Comment utiliser RECHERCHEV en VBA
15 commentaires sur “Nettoyer la “cache” des tableaux croisés dynamiques”
Merci pour ce code. Il fonctionne à merveille et a résolu mon problème de filtrage de tables pivot.
super nickel, merci pour ces lignes !!
Je suis content d'avoir pu être utile…
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 ?
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
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
Bonjour,
Je suis fortement intéressé par votre code mais j'ai également un message d'erreur sur la ligne ".MissingItemsLimit = xlMissingItemsNone" –> erreur définie par l'application ou par l'objet.
Avez-vous des pistes de résolution svp.
Merci par avance
Bonjour,
j'ai la même erreur que Bory, je n'ai pas de feuilles protégées (quelques cellules protégées avec une macro qui décale la souris sur la cellule de droite uniquement, et même pas sur la seule feuille où j'ai un TCD).
Je cherche un code à adapter pour Telle feuille ou pour Tel TCD, je n'arrive pas à le modifier en ce sens,
En vous remerciant,
Estelle
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.
Super! Ravi d'entendre que mon code vous a aidé et vous a économisé le temps de travail…
Cordialement, Martin
Merci, cela permet aussi de ne plus voir apparaître dans les filtres d'anciennes valeurs qui n'existent plus dans les tableaux
Exactement!
Merci, super code! 🙂
Bonjour,
j'ai testé le code et dès le lancement j'ai le message "Une erreur est survenue".
Merci d'avance pour votre retour
Bonjour Emmanuel,
essayez d'exécuter le code ligne par ligne dans l'éditeur VBA pour voir quelle ligne exactement pose problème.
Vous pouvez faire cela avec la touche F8 – chaque fois que appuyez, une ligne sera exécutée.
Normalement, la ligne qui pose problème sera celle qui causera le saut sur la ligne avec le message d'erreur à la fin du code.