Macro VBA – Diagnostiquer les tableaux croisés dynamiques d’un classeur Excel

diagnostic-tcd
(Last Updated On: 15 avril 2020)

Avez-vous déjà eu ce message d’erreur lorsque vous tentez de mettre à jour des tableaux croisés dynamiques ?

Diagnostiquer un problème de chevauchement entre plusieurs rapports de tableaux croisés dynamiques

“Impossible d’afficher le rapport de tableau croisé dynamique sur un rapport existant”

Oui, ce message. Encore que si vous n’avez que deux TCD dans votre classeur, il est facile d’identifier les coupables qui jouent des coudes et veulent prendre la place de l’autre…

Mais, l’autre jour je me plongeais dans le fichier Excel d’un collègue, et, en tentant d’actualiser les données, voilà qu’Excel m’informe gentiment avec le message qu’il n’est pas possible d’actualiser mon TCD car celui-ci risque de prendre la place de son camarade à côté, ce qui n’est pas permis.

Mais de quel TCD s’agit-il ?  Là est le problème lorsque que votre classeur Excel contient des dizaines de TCD dans tous les sens 😆

Mon conseil étant au passage de ne pas juxtaposer plusieurs TCD dans une feuille si vous savez que les données sont mises à jour régulièrement.

Bon, ce n’est pas bien grave en soi, mais cela peut demander du temps d’aller chercher chaque TCD et tenter de l’actualiser pour identifier la source du problème. Y-a-t-il un moyen d’éviter ce travail de recherche fastidieux ?

Une solution est d’automatiser cette tâche de recherche, il vous faut donc pour cela un utilitaire tel que celui que je vous propose aujourd’hui dans cet article pour lister tous les tableaux croisés dynamiques contenus dans un fichier Excel, et obtenir ainsi des informations sur le risque de chevauchement avec d’autres TCD, ou bien l’origine de la source de données, le nombre de segments (ou filtres) actifs sur le TCD, etc.

Diagnostiquer un problème de référence de source non valide

“Référence non valide.”

“La référence à la source de données n’est pas valide.”

“Le nom du champ de tableau croisé dynamique n’est pas valide. Pour créer un rapport de tableau croisé dynamique, vous devez utiliser des données sous forme de liste avec des étiquettes de colonnes. Si vous changez le nom d’un champ de tableau croisé dynamique, vous devez taper un nouveau nom pour le champ.”

Un autre problème courant avec les tableaux croisés dynamiques concerne les sources non valides.

Cela peut arriver lorsqu’on cherche à actualiser un TCD et que la source initiale n’existe plus par exemple. Très probablement, la raison de l’erreur est l’une ou plusieurs des suivantes :

  • Le nom du fichier Excel contient les caractères crochets “[” ou “]”
  • Le fichier provient d’un e-mail ou d’internet et n’est pas enregistré sur le disque dur local
  • La source de données du tableau croisé dynamique fait référence à une plage inexistante
  • La source de données fait référence à une plage nommée avec une référence non valide.

Comment la macro peut vous aider à trouver l’origine du problème ?

L’utilitaire liste pour chaque TCD du classeur Excel les informations comme le type de données et l’adresse de la plage source de données. Si dans la liste la cellule du type de source ou l’emplacement de la source est vide, c’est que Excel ne trouve pas la source et que c’est donc ce TCD qui pose problème.

Macro VBA pour lister les tableaux croisés dynamiques

Le code proposé vérifie chaque feuille de calcul et répertorie les informations suivantes pour chaque tableau croisé dynamique du fichier Excel :

  • Nom de la feuille de calcul
  • Nombre de tableaux croisés dynamiques sur la feuille
  • Nom du tableau croisé dynamique
  • Emplacement du tableau croisé dynamique
  • Autres tableaux croisés dynamiques dans les mêmes lignes – peuvent entraîner un chevauchement
  • Autres tableaux croisés dynamiques dans les mêmes colonnes – peuvent entraîner un chevauchement
  • Type de source de données (Plage fixe, plage nommée ou objet Tableau)
  • Nom de la plage
  • Adresse de la plage
  • Si c’est une source provenant d’un autre classeur (Externe)

De plus, les informations suivantes sont affichées :

  • Nombre d’enregistrements
  • Nombre de colonnes
  • Nombre de cellules d’en-tête ne contenant pas de valeurs
  • Réparer en-tête : un X indique qu’il faut renseigner un ou des en-têtes vides
  • Date d’actualisation la plus récente du TCD => très utile pour comparer des versions du fichier différentes
  • Nombre de segments connectés au TCD

INSTRUCTIONS

  1. Téléchargez le fichier Excel “Diagnostic TCD.xlsm” via le lien ci-dessous.
  2. Ouvrez vos fichiers Excel pour lesquels vous souhaitez établir la liste des TCD
  3. Cliquez sur le bouton diagnostic TCD
  4. Une nouvelle feuille est créée listant tous les TCD du classeur Excel choisi
  5. Dans la colonne commentaires, regardez s’il y a un risque de chevauchement entre les TCD ou d’autres problèmes potentiels identifiés.

📥TÉLÉCHARGER LE FICHIER

Télécharger le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

diagnostic-tcd

Diagnostiquer les tableaux croisés dynamiques d’un classeur Excel

Macro VBA permettant d'obtenir des informations sur les tableaux croisés dynamiques contenus dans un classeur Excel. En effet, il peut être utile de savoir combien de TCD sont présents dans…
Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

Dites-moi en commentaire si vous avez des idées d’amélioration pour cette macro, ou si vous rencontrez d’autres problèmes avec les tableaux croisés dynamiques que vous aimeriez résoudre… MERCI !

Souscrivez à ma newsletter :

11 commentaires

  1. Nezeraud Erik dit

    Bonjour,
    Je viens d’utiliser l’utilitaire que vous avez mis à disposition (Diagnostic des TCD d’un classeur) et tiens à vous en remercier.
    L’outil, son ergonomie et les données de prise en main sont de qualité et m’ont apporté une valeur immédiate.
    Vous souhaitant de poursuivre votre action avec un plaisir constant.

    • Lionel dit

      Bonjour

      Content que le diagnostic TCD d’XL Business Tools vous soit utile !

      Ayant été confronté régulièrement à des problèmes de chevauchement de tableaux croisés dynamiques et les messages d’erreurs d’Excel étant assez vagues, cela m’a incité à développer cet utilitaire pour trouver l’origine des problèmes et la solution rapidement.

      Lionel

  2. Nouamane dit

    Bonjour,

    Premièrement je souhaite vous remercier pour ce fichier que je trouve formidable et très complet.
    Je l’ai utilisé, et tout va bien sauf pour les Plages Sources, il me donne comme plage (….je vous épargne le chemin en entier….)!C1:C12, alors que mes sources sont totalement différentes.
    Dois je faire quelque chose pour ça se corrige ? A noter que j’ai essayé de trouver de ça vient au niveau du code VBA mais en vain. merci d’avance pour votre retour.

    • Lionel dit

      Bonjour,

      Merci pour votre message !
      Si vous cliquez sur le TCD et faites changer la source, est-ce que la source affichée correspond au diagnostic ?
      Vérifiez que vous regardez bien le bon classeur Excel si vous avez plusieurs classeurs Excel ouverts et aussi le type de source de données, plage de cellules, plage nommée ou Table Excel. Car l’utilitaire convertit l’emplacement des sources en plage de cellules (comme !C1:C12), pour avoir le nom de la plage ou tableau, il faut regarder le champs Nom de la source.

      En espérant que cela vous mette sur une piste.

      Lionel

  3. BOSCHI André dit

    Bonjour

    Merci pour ces articles plus qu’intéressants car compréhensibles par tout un chacun. J’ai découvert ces outils grâce à votre formation très complète.

    J’ai réussi à monter un dossier complexe et me suis heurté aux petits problèmes que vous évoquez. L’outil de diagnostic me sera donc fort utile, c’est le moins que l’on puisse dire !!

    Par contre j’ai toujours un problème pour ajouter une colonne calculée à un Tcd . Qu’est ce qui rend ce bouton inactif….. Mes recherches me laissent croire à une incidence de la structure Olap…

    Un peu de lumières sur ce sujet serait le bienvenu ….

    Avec tous mes remerciements
    André

    • Lionel dit

      Bonjour, merci pour votre commentaire, pour votre question, je ne suis pas sûr de bien comprendre ce que vous entendez par le bouton est inactif pour ajouter colonne calculée dans un TCD. Pouvez-vous mettre une capture d’écran ?

Répondre à Nouamane Annuler la réponse

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *