Actualiser une requête Power Query automatiquement à l’ouverture du classeur

Un classeur alimenté par Power Query, partagé avec plusieurs personnes, ouvert le matin sans que personne ne pense à cliquer sur Actualiser tout : c’est la façon la plus banale de distribuer des chiffres faux sans le vouloir. La donnée affichée est celle de la dernière actualisation, pas celle du jour, et rien à l’écran ne le signale. En formation, je vois régulièrement ce réglage oublié sur des classeurs qui tournent depuis des mois.

Il existe une case à cocher pour ça. Elle marche, mais seulement dans les conditions où on l’attend. Je détaille ce qu’elle fait vraiment, les trois cas où elle reste inactive sans le signaler, et le code VBA qui reprend la main quand la case ne suffit plus.

La case à cocher, et ce qu’elle fait réellement

Dans l’onglet Données, un clic sur la petite flèche à côté d’Actualiser tout ouvre Propriétés de connexion (on y arrive aussi par un clic droit sur la requête, dans le volet Requêtes et connexions, puis Propriétés). Sous l’onglet Utilisation, la case Actualiser les données lors de l’ouverture du fichier déclenche l’actualisation de cette requête, et uniquement celle-là, dès que le classeur s’ouvre.

Côté modèle objet, cette case correspond à la propriété OLEDBConnection.RefreshOnFileOpen : un booléen, faux par défaut, porté par la connexion et non par la requête elle-même. C’est un point qui surprend : une requête Power Query chargée dans une feuille crée sa propre connexion, donc sa propre case à cocher. Dix requêtes chargées, c’est dix cases à cocher une par une, il n’existe pas de réglage global « tout actualiser à l’ouverture » dans l’interface.

Trois cas où elle reste inactive, sans le dire

La case ne prévient jamais quand elle ne joue pas son rôle. Trois situations la neutralisent, chacune pour une raison différente.

L’aperçu protégé. Un fichier reçu par courriel ou téléchargé depuis internet s’ouvre en mode Aperçu protégé tant que l’utilisateur n’a pas cliqué sur Activer la modification. Dans ce mode, ni les macros ni les actualisations automatiques ne se déclenchent, l’actualisation à l’ouverture y compris. Le classeur affiche des données de la veille sans que rien ne l’indique.

L’actualisation désactivée sur la connexion. La documentation Microsoft est explicite sur ce point : si la propriété EnableRefresh de la connexion vaut False, alors RefreshOnFileOpen est purement et simplement ignorée, quelle que soit sa valeur. EnableRefresh se règle en VBA, ou parfois par un assistant d’import qui la désactive sans le dire. La case à cocher reste cochée à l’écran, elle n’a simplement plus aucun effet.

L’ouverture par du code. Un classeur ouvert par une macro, un script, ou une tâche planifiée, via Workbooks.Open, ne déclenche pas RefreshOnFileOpen. La documentation le précise noir sur blanc : l’actualisation automatique ne se produit pas quand l’ouverture passe par du code. C’est le cas le plus facile à rater, puisqu’il ne dépend pas d’un réglage sur le classeur mais de la façon dont quelqu’un d’autre, ailleurs, choisit de l’ouvrir.

Reprendre la main avec Workbook_Open

Quand la fiabilité compte plus que la simplicité, la solution est de remplacer la case à cocher par une procédure Workbook_Open, dans le module ThisWorkbook. Contrairement à la case, cette procédure se déclenche aussi bien à l’ouverture interactive qu’à l’ouverture par du code (sauf si ce code désactive volontairement les événements avec Application.EnableEvents = False), ce qui couvre justement le troisième cas ci-dessus.

Private Sub Workbook_Open()
    Dim cn As WorkbookConnection
    For Each cn In ThisWorkbook.Connections
        If InStr(1, cn.OLEDBConnection.Connection, "Mashup", vbTextCompare) > 0 Then
            cn.OLEDBConnection.BackgroundQuery = False
            cn.Refresh
        End If
    Next cn
    Application.CalculateUntilAsyncQueriesDone
End Sub

Argument par argument, ce que fait ce code :

• ThisWorkbook.Connections : la collection de toutes les connexions du classeur, qu’elles soient chargées dans une feuille, dans le modèle de données, ou en connexion seule. Une requête jamais chargée nulle part n’y figure pas, mais une requête utilisée uniquement comme source d’une autre y figure bel et bien.
• cn.OLEDBConnection.Connection : la chaîne de connexion complète. Les requêtes Power Query s’appuient toutes sur le fournisseur Microsoft.Mashup.OleDb.1 ; tester la présence de « Mashup » dans cette chaîne isole les connexions Power Query des connexions classiques (une base SQL, un fichier texte importé à l’ancienne) qui n’ont pas besoin du même traitement.
• BackgroundQuery = False : c’est la ligne qui change tout. Par défaut, une actualisation Power Query se lance en arrière-plan : le classeur redevient utilisable, et donc lisible, avant que la donnée ait fini d’arriver. En VBA, c’est un problème direct, puisque la ligne suivante peut s’exécuter alors que la table n’est pas encore à jour. Mettre BackgroundQuery à False avant Refresh force une actualisation synchrone : la ligne cn.Refresh ne rend la main qu’une fois la donnée arrivée.
• cn.Refresh : déclenche l’actualisation de cette connexion précise. Chaque connexion s’actualise indépendamment des autres, d’après la documentation Microsoft : si l’une échoue (source injoignable, colonne renommée), les suivantes s’exécutent quand même, la boucle ne s’arrête pas net.
• Application.CalculateUntilAsyncQueriesDone : un filet de sécurité placé après la boucle, qui force l’achèvement de toute requête OLEDB ou OLAP encore en cours. Utile notamment pour une requête chargée dans le modèle de données, qui ne se comporte pas toujours comme une requête chargée dans une feuille.

Ce que VBA ne voit pas : les dépendances entre requêtes

Une question revient souvent : dans quel ordre actualiser des requêtes qui se référencent entre elles, une requête de préparation utilisée comme source par deux autres, par exemple ? La réponse tient en une phrase, et elle simplifie les choses : il n’existe pas de modèle objet VBA pour les dépendances entre requêtes Power Query, et il n’y en a pas besoin. Le moteur M résout les références au moment de l’évaluation, quelle que soit la connexion sur laquelle la boucle For Each tombe en premier : actualiser la requête finale déclenche automatiquement le recalcul de ce dont elle dépend. L’ordre de la boucle ci-dessus n’a donc pas d’incidence sur l’exactitude du résultat, seulement, dans de rares cas, sur le temps total si une même source est interrogée plusieurs fois faute de mise en cache entre deux requêtes indépendantes qui la consomment toutes les deux.

Disponibilité

RefreshOnFileOpen, BackgroundQuery, EnableRefresh et la méthode CalculateUntilAsyncQueriesDone appartiennent au modèle objet des connexions de données externes d’Excel, antérieur à Power Query : CalculateUntilAsyncQueriesDone figure déjà dans la documentation développeur d’Excel 2007. Rien de tout cela n’exige Microsoft 365 ni une version récente d’Excel : le code fonctionne à l’identique sur un poste resté en Excel 2016, tant que Power Query y est installé (nativement depuis 2016, en module complémentaire gratuit pour 2010 et 2013).

Ce que je retiens

La case à cocher suffit dans le cas le plus fréquent : un classeur ouvert normalement, par la même personne, sans aperçu protégé. Elle ne suffit plus dès qu’un fichier circule par courriel, qu’un script l’ouvre pour le traiter, ou qu’une connexion a été verrouillée en coulisses par un import antérieur. Dans ces cas-là, quelques lignes dans Workbook_Open coûtent moins cher que le doute sur la fraîcheur des chiffres affichés.

La documentation complète des éléments utilisés : OLEDBConnection.RefreshOnFileOpen, OLEDBConnection.EnableRefresh, OLEDBConnection.BackgroundQuery et Application.CalculateUntilAsyncQueriesDone.