Vos formules GETPIVOTDATA renvoient #REF! lorsque le classeur OneDrive d’origine est fermé ? Voici pourquoi Excel sollicite d.docs.live.net et comment corriger durablement le problème, avec des méthodes stables pour vos tableaux de bord.
Erreur « #REF » avec GETPIVOTDATA et demande de connexion d.docs.live
Vue d’ensemble du problème
- Vous utilisez GETPIVOTDATA pour récupérer des valeurs d’un tableau croisé dynamique (TCD) stocké sur OneDrive.
- Lorsque le classeur contenant le TCD est fermé, les cellules qui s’appuient sur GETPIVOTDATA affichent #REF!.
- Excel ouvre parallèlement une invite vous demandant de vous connecter à
d.docs.live.net/…pour « actualiser » ou « accéder à la source ».
Racine du problème : GETPIVOTDATA cible le cache du TCD (PivotCache) et non une simple cellule. Si ce cache n’est pas disponible (classeur fermé, session OneDrive non authentifiée, accès hors‑ligne, chemin modifié), la référence est rompue et Excel retourne #REF!. L’invite provient du mécanisme de connexion (WebDAV/ODC) utilisé par Excel pour accéder aux classeurs hébergés sur OneDrive via d.docs.live.net.
Solutions et bonnes pratiques
| Objectif | Méthode | Étapes clés / points d’attention |
|---|---|---|
| Conserver GETPIVOTDATA sans erreur | Placer la formule dans le même fichier que le TCD, puis exposer le résultat via une « feuille relais » vers un second classeur. | Dans le classeur A (celui du TCD), créez une feuille « Relais » avec toutes vos formules GETPIVOTDATA. Dans le classeur B (tableau de bord), créez de simples liaisons du type ='[A.xlsx]Relais'!B2. Avantage : le TCD peut rester fermé côté utilisateur du classeur B ; la liaison pointe une cellule standard déjà calculée. |
| Éliminer la dépendance au TCD | Remplacer GETPIVOTDATA par une recherche dynamique (INDEX + EQUIV ou XLOOKUP) sur la source ou une copie valeurs du TCD. | Convertissez la plage source en Table (Ctrl + T) pour bénéficier des noms structurés. Exemple : =XLOOKUP(critère; Table[Clé]; Table[Valeur]). Les formules restent valides même si le TCD est supprimé, déplacé ou clos. |
| Rendre les résultats statiques | Copier‑coller les GETPIVOTDATA en valeurs lorsque les chiffres n’ont plus vocation à évoluer. | Sélectionnez les cellules → Copier → Collage spécial > Valeurs. Plus de risque de #REF!, mais plus d’actualisation automatique. |
| Stabiliser l’accès OneDrive | Vérifier authentification et chemin. | Excel > Fichier > Compte : confirmez que vous êtes connecté au même compte que votre OneDrive. Évitez de renommer ou déplacer les dossiers OneDrive utilisés par vos liaisons ; l’URL change et rompt les liens. |
| Moderniser la récupération de données | Utiliser Power Query (Obtenir & Transformer) ou Power Pivot. | Importez la source du TCD via Power Query et chargez‑la en tableau, ou créez un modèle de données. Mise à jour pilotée (bouton, macro, planification), sans invite intrusive d.docs.live.net. |
Informations complémentaires utiles
- Désactiver la génération automatique de GETPIVOTDATA : Fichier > Options > Formules > Travail sur les TCD : décochez « Générer des formules GETPIVOTDATA ». Pratique si vous préférez vos propres formules.
- Sauvegarde hors OneDrive : l’historique de versions OneDrive (clic droit > Historique des versions) aide à revenir en arrière, mais complétez‑le avec une sauvegarde locale/dédiée (images système, planifications).
- Diagnostic des connexions : Données > Connexions : sélectionnez la connexion OneDrive → Propriétés → onglet Définition (vérifiez le chemin complet). Dans Paramètres de connexion, « Enregistrer le mot de passe » peut éviter les invites répétées (selon votre politique sécurité).
Pourquoi GETPIVOTDATA casse quand le classeur est fermé
GETPIVOTDATA n’interroge pas la cellule affichée mais l’arborescence du TCD : champs, éléments, mesures, filtres. Cela suppose l’accès au PivotCache du classeur d’origine. Lorsqu’un TCD réside dans un fichier hébergé sur OneDrive :
- Si le classeur source est fermé, Excel tente d’ouvrir la source via WebDAV (
d.docs.live.net) pour atteindre le cache. En l’absence d’authentification valide, Excel affiche une invite et renvoie #REF!. - Si le chemin a changé (renommage du dossier, déplacement, déconnexion/reconnexion à un autre compte), la chaîne d’adresse n’est plus valide.
- Hors‑ligne, Excel ne peut ni ouvrir la source ni reconstituer le cache : #REF! est immédiat.
À retenir : GETPIVOTDATA fonctionne parfaitement tant que le TCD est dans le même classeur ou que les deux classeurs sont ouverts et authentifiés simultanément. Dès que l’un des deux est fermé → dépendance cassée.
Scénarios typiques et symptômes
| Scénario | Symptôme | Cause probable | Correction recommandée |
|---|---|---|---|
| TCD dans A.xlsx (OneDrive), tableau de bord dans B.xlsx | #REF! dans B.xlsx, invite d.docs.live.net | PivotCache inaccessible (A.xlsx fermé/non authentifié) | Feuille relais dans A.xlsx ; B.xlsx lie des cellules calculées |
| Renommage du dossier OneDrive « Projets » → « Projets‑2025 » | « Impossible d’ouvrir la source de données », liens cassés | Chemin OneDrive modifié, ID WebDAV différent | Réparer liens ; éviter les renommages de dossiers référencés |
| PC hors‑ligne (train/avion) | #REF! et demande d’identifiants | Aucune session WebDAV disponible | Copier‑coller valeurs ou relier une Table locale via Power Query |
| Changement de compte Microsoft | Invite d’authentification récurrente | Jeton/session OneDrive expiré ou compte différent | Se reconnecter au bon compte dans Excel > Fichier > Compte |
Mise en œuvre pas à pas des solutions
Solution A : feuille relais dans le classeur du TCD
- Ouvrez le classeur A.xlsx contenant le TCD.
- Ajoutez une feuille nommée Relais et y placez toutes vos formules
GETPIVOTDATAnécessaires aux indicateurs. - Vérifiez que chaque formule renvoie la valeur attendue. Exemple :
=GETPIVOTDATA("Montant";'[A.xlsx]Pivot'!$A$3;"Année";2025;"Produit";"Alpha") - Dans B.xlsx (votre tableau de bord), créez des liaisons simples :
='[A.xlsx]Relais'!B2,='[A.xlsx]Relais'!C5, etc. - Fermez A.xlsx et testez l’ouverture de B.xlsx : les liaisons renvoient des nombres sans GETPIVOTDATA côté B. Plus d’invite
d.docs.live.net.
Astuce : combinez avec Formules > Calcul manuel dans A.xlsx si vous souhaitez maîtriser le coût de recalcul du TCD.
Solution B : remplacer GETPIVOTDATA par XLOOKUP/INDEX
- Identifiez la table source du TCD. Si elle n’existe pas, exportez le TCD en valeurs (clic droit sur le TCD > Copier > Collage spécial > Valeurs) ou récupérez sa source.
- Convertissez la plage en Table (Ctrl + T). Nommez‑la par exemple Ventes.
- Créez des recherches dynamiques :
=XLOOKUP($A2; Ventes[Produit]; Ventes[Montant])
ou=INDEX(Ventes[Montant]; EQUIV($A2; Ventes[Produit]; 0)) - Option multi‑critères (produit + année) :
=INDEX(Ventes[Montant]; EQUIV(1; (Ventes[Produit]=$A2)*(Ventes[Année]=$B2); 0))
Ces formules pointent une table locale et restent fonctionnelles quelle que soit l’ouverture du TCD d’origine.
Solution C : figer les chiffres en valeurs
- Sélectionnez les cellules à figer.
- Ctrl + C → Collage spécial > Valeurs.
- Ajoutez une date de gel (p.ex. « Clôture au 30/09/2025 ») pour la traçabilité.
Solution D : Power Query ou Power Pivot
Avec Power Query
- Données > Obtenir des données > Depuis un classeur.
- Sélectionnez la source (A.xlsx) dans votre dossier OneDrive synchronisé localement pour éviter l’invite WebDAV.
- Nettoyez/transformez, puis Charger vers une table.
- Dans B.xlsx, vos indicateurs pointent cette table locale, rafraîchissable à la demande.
Avec Power Pivot / Modèle de données
- Activez l’onglet Power Pivot, importez la source.
- Créez des mesures DAX (p.ex.
Montant Total := SUM(Ventes[Montant])) et des TCD basés sur le modèle. - Vos rapports se construisent sur le modèle, non sur un TCD externe.
Bon réflexe : si vous devez conserver GETPIVOTDATA, isolez‑le dans le fichier source et ne publiez vers l’extérieur que des cellules « résultats ».
Réglages et hygiène des fichiers
Désactiver la génération automatique de GETPIVOTDATA
Fichier > Options > Formules > Travail sur les TCD : décochez « Générer des formules GETPIVOTDATA ». Vous pourrez alors cliquer dans un TCD sans qu’Excel insère automatiquement GETPIVOTDATA.
Options du TCD qui influencent la stabilité
- Options du TCD > Données : « Enregistrer les données source avec le fichier » accélère l’ouverture mais alourdit le classeur. Cela n’élimine pas la dépendance de GETPIVOTDATA lorsqu’il est utilisé depuis un autre fichier.
- Activez « Rafraîchir les données à l’ouverture du fichier » si vous voulez garantir des chiffres à jour côté feuille relais.
Gestion des identifiants OneDrive
- Excel > Fichier > Compte : connectez‑vous au même compte que celui qui héberge le classeur source.
- Windows > Gestionnaire d’identifiants > Identifiants Windows : supprimez/réinitialisez les entrées obsolètes associées à
d.docs.live.netsi les invites persistent. - Évitez de déplacer/renommer les répertoires utilisés par les liens Excel.
Architecture recommandée des fichiers
| Fichier | Rôle | Contenu | Interaction |
|---|---|---|---|
| A.xlsx | Source & calcul | TCD, feuille relais avec GETPIVOTDATA | Seul A calcule ; B se contente de lire les cellules « résultat » |
| B.xlsx | Tableau de bord | Graphiques, KPIs, mises en forme | Liaisons ='[A.xlsx]Relais'!Cellule, aucun GETPIVOTDATA |
| (Optionnel) C.xlsx | Extraction | Table(s) Power Query | Alimente A et/ou B sans TCD externe |
Check‑list rapide de dépannage
- Ouvrez le classeur du TCD. L’erreur #REF! disparaît‑elle ? Si oui : dépendance au cache.
- Testez la connexion OneDrive dans Excel > Fichier > Compte (même compte ?).
- Vérifiez Données > Connexions > Propriétés : chemin
d.docs.live.netcohérent ? - Essayez depuis un chemin local synchronisé (dossier OneDrive dans l’Explorateur) au lieu d’une URL.
- Isoler GETPIVOTDATA dans une feuille relais et remplacer côté rapport par des liaisons simples.
- En dernier recours : figer en valeurs, ou basculer vers Power Query/Modèle de données.
Exemples concrets de formules
GETPIVOTDATA ciblant un TCD nommé « TCD_Ventes »
=GETPIVOTDATA("Montant"; "[A.xlsx]Synthèse"!$A$3; "Année"; 2025; "Produit"; "Alpha")Problème : si A.xlsx est fermé, Excel tente d’atteindre le cache via d.docs.live.net → #REF!.
Remplacement par XLOOKUP sur une Table « Ventes »
=XLOOKUP($B$2 & "|" & $C$2; Ventes[Produit] & "|" & Ventes[Année]; Ventes[Montant])Liaison depuis la feuille relais
='[A.xlsx]Relais'!D5La cellule D5 de A.xlsx contient la valeur calculée par GETPIVOTDATA. B.xlsx ne fait qu’afficher ce résultat stable.
Spécificités Windows, Mac et Excel pour le web
- Windows : OneDrive expose des chemins WebDAV du type
\\d.docs.live.net\<ID>\. Excel déclenche volontiers l’invite d’identification si la session expire. - macOS : les invites sont plus rares mais GETPIVOTDATA reste dépendant du classeur source. Préférez les chemins du dossier OneDrive synchronisé localement.
- Excel pour le web : GETPIVOTDATA fonctionne dans le même fichier. Pour des rapports multi‑fichiers, utilisez tables/Power Query et évitez les dépendances croisées au cache d’un TCD.
Performance et maintenance
- Réduire les TCD « tampons » : un TCD minimal par jeu de données + mesures claires vaut mieux qu’une prolifération d’objets.
- Nommez vos TCD (Analyse du TCD > Nom du TCD) et vos tables pour une maintenance lisible.
- Regroupez toutes les GETPIVOTDATA dans la feuille relais, documentée (onglet, légende, date de mise à jour).
- Planifiez un rafraîchissement contrôlé (bouton macro, Power Automate, tâche) plutôt que des rafraîchissements implicites à chaque ouverture.
Sécurité et conformité
L’option « Enregistrer le mot de passe » dans les paramètres de connexion limite les invites, mais n’est acceptable que si votre politique interne l’autorise. Privilégiez :
- Des chemins locaux synchronisés (OneDrive Desktop) plutôt qu’une URL WebDAV explicite.
- Des sources read‑only pour les rapports de production.
- Des comptes de service dédiés pour les flux automatisés (Power Query/Power Automate), si votre DSI le permet.
Erreurs cousines et comment les traiter
- #N/A : la combinaison de champs demandée n’existe pas dans le TCD ; vérifiez filtres/orthographe.
- #VALEUR! : concaténation ou calcul sur du texte renvoyé par GETPIVOTDATA ; convertissez explicitement en nombre (
--valeur). - « Impossible d’ouvrir la source » : chemin invalide ou droits insuffisants ; réparez les liens et recréez l’authentification OneDrive dans Excel.
Foire aux questions
Puis‑je faire fonctionner GETPIVOTDATA quand le classeur du TCD est fermé ?
Non, pas de manière fiable si la formule est dans un autre fichier. Par conception, GETPIVOTDATA interroge le cache du TCD du fichier source. Utilisez la feuille relais, des liaisons simples ou des recherches sur une table locale. Quelle différence entre ='[A.xlsx]Relais'!B2 et GETPIVOTDATA ?
La première lit une cellule calculée (valeur figée côté source), la seconde interroge la structure du TCD. La cellule liée reste accessible même si le TCD est fermé, car le calcul a déjà eu lieu dans A.xlsx. Les fonctions CUBE (si j’utilise le Modèle de données) contournent‑elles le problème ?
Oui, lorsqu’elles pointent un modèle de données intégré au même fichier. Pour des références inter‑fichiers, préférez des pipelines Power Query et des liaisons de cellules. Comment éviter l’invite d.docs.live.net ?
Ouvrez/rafraîchissez via le dossier OneDrive synchronisé localement, restez connecté au bon compte Microsoft dans Excel, et évitez d’utiliser des URL WebDAV explicites dans vos connexions. Idéalement, basculez vers des sources locales (Power Query) et des relais de cellules.
Procédure de correction express
- Ouvrez le fichier source (A.xlsx) et vérifiez que les TCD se recalculent correctement.
- Créez une feuille relais ; déplacez‑y toutes les GETPIVOTDATA utiles au reporting.
- Dans le fichier rapport (B.xlsx), remplacez chaque GETPIVOTDATA par une liaison vers la cellule correspondante de la feuille relais.
- Fermez A.xlsx et rouvrez B.xlsx : plus de #REF!, plus d’invite
d.docs.live.net. - Documentez la date/heure de mise à jour et, si nécessaire, ajoutez un bouton « Actualiser » (Power Query ou macro) pour industrialiser.
Résumé exécutif
GETPIVOTDATA est une fonction puissante, mais structurelle : elle exige l’accès au cache du TCD. Dans des architectures multi‑fichiers et cloud (OneDrive), cela provoque des #REF! et des invites d’authentification. Trois approches robustes existent : 1) feuille relais dans le fichier du TCD, 2) recherches dynamiques sur tables locales (XLOOKUP/INDEX), 3) pipelines Power Query/Modèle de données. En appliquant ces modèles, vous éliminez l’erreur #REF!, supprimez les boîtes de dialogue intempestives et sécurisez vos tableaux de bord.
Annexe : modèles de messages et actions
| Message Excel | Traduction/Contexte | Action immédiate |
|---|---|---|
| « Vous devez vous connecter à d.docs.live.net pour accéder à ce fichier » | Excel tente d’ouvrir une source OneDrive via WebDAV | Se reconnecter au bon compte, ou préférer un chemin local synchronisé |
| #REF! dans une formule GETPIVOTDATA | PivotCache non accessible | Ouvrir la source, déplacer la formule dans le fichier du TCD, ou utiliser une liaison/Power Query |
| « Impossible d’ouvrir la source de données » | Chemin invalide/renommé | Réparer les liens, éviter les renommages de dossiers OneDrive |
Bonnes pratiques à long terme
- Standardisez vos emplacements (p.ex.
OneDrive\BI\Sources\etOneDrive\BI\Rapports\). - Documentez chaque rapport (source, fréquence de rafraîchissement, propriétaire, dépendances).
- Testez hors‑ligne régulièrement pour vérifier la robustesse en mobilité.
- Versionnez les fichiers sensibles (copie « JJMMAAAA ») en plus de l’historique de versions OneDrive.
Conclusion
Si votre rapport Excel dépend d’un TCD situé dans un autre fichier OneDrive, n’utilisez pas GETPIVOTDATA directement dans le rapport. Placez les GETPIVOTDATA dans le fichier source, exposez des cellules relais ou préférez des tables/recherches via Power Query. Vous obtiendrez un reporting fiable, sans invite et pérenne pour vos utilisateurs.