récupérer et gérer les erreurs de power query avec VBA excel

Version imprimable

09/10/2023, 09h31
JPaul_B

récupérer et gérer les erreurs de power query avec VBA excel

Bonjour,
J'ai réaliser un programme VBA sur Excel qui utilise PQ pour une compilation de divers tables venant d'autre fichiers Excel. Les utilisateurs pouvant mettre à jours eux-mêmes les tables de compilation et autre TCD. Cela fonctionne bien sauf lorsque j'ai un pb de chargement d'une table pour n'importe quel raison (le petit signe "Attention" sur la query!) je n'ai pas d'alerte. La MàJ ne se réalise pas ou partiellement (j'ai plusieurs querries différentes) et donc donne des résultats faux.
Comment peut-on récupérer ces erreurs avec VBA afin de pouvoir les traiter ou à minima pouvoir alerter l'utilisateur qu'il y a un pb?
Merci bcp de votre aide car toutes mes recherches n'ont rien donnés à ce jour.

Bonjour, j'utilise régulièrement la structure ci-dessous pour la gestion des erreurs, n'utilisant pas PQ je n'ai pas testé mais je pense que ça pourrait peut-être t'aider.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub TonCodePrincipal()
    On Error Resume Next
    ' Ton code Power Query ici
 
    ' Vérifier s'il y a une erreur
    If Err.Number <> 0 Then
        ' Il y a une erreur, tu peux la traiter ici ou simplement l'afficher
        MsgBox "Erreur lors du chargement de la table: " & Err.Description
        ' Réinitialiser l'objet d'erreur
        Err.Clear
    End If
    On Error GoTo 0 ' Réinitialiser le gestionnaire d'erreurs à son état normal
End Sub

09/10/2023, 14h46
JPaul_B

reponse à Franc

Merci de ton aide Franc,
Dans mon cas, je n'ai pas de code VBA pour Power Query, c'est traité à part dans la partie PQ d'Excel. Mon soucis est de savoir comment je peux vérifier avec VBA que l'opération de PQ s'est correctement passée, puis après de gérer via VBA les pbs éventuels.
Nota: j'utilise aussi dans les Pgms VBA de gestion d'erreurs un peu comme le tient, mais dans le cas de PQ cela ne déclenche pas la gestion d'erreur VBA et c'est là qu'est l'os :?
09/10/2023, 15h14
Philippe Tulliez

Bonjour,
Le gestionnaire d'erreur fonctionne aussi lorsque l'on actualise une requête Power Query. Maintenant le message d'erreur ne sera peut-être pas aussi précis que ce que l'on attend.
Ainsi pour une source non trouvée on aura bien une erreur 1004 avec un message clair mais pour une erreur de nom de tableau à l'étape "Navigation", on aura également une erreur 1004 avec comme message "[Expression Error] La clé ne correspondait à aucune ligne dans la table".
09/10/2023, 15h17
jurassic pork

Hello,
JPaul_B si tu as un classeur exemple sans données confidentielles avec le genre d'erreur que tu veux contrôler, cela sera plus simple pour essayer de te répondre.
Ami calmant, J.P
09/10/2023, 19h19
JPaul_B

Citation:

Envoyé par Philippe Tulliez

Bonjour,
Le gestionnaire d'erreur fonctionne aussi lorsque l'on actualise une requête Power Query. Maintenant le message d'erreur ne sera peut-être pas aussi précis que ce que l'on attend.
Ainsi pour une source non trouvée on aura bien une erreur 1004 avec un message clair mais pour une erreur de nom de tableau à l'étape "Navigation", on aura également une erreur 1004 avec comme message "[Expression Error] La clé ne correspondait à aucune ligne dans la table".

Merci Philippe,
J'ai mis en place un gestionnaire d'erreur juste avant la commande d'actualisation (mais peut-être mal fait??). le pb est qu'il y a des trous dans la raquette car j'ai eu des tables que ne se sont pas chargées sans aucun flag d'erreur. après analyse, l'une des causes était une colonne qui n'existait plus ou qui avait changé de nom dans l'un des tableaux que je croisais. Cela dit, je suis autodidacte sur Excel et PQ et ne pratique ce dernier depuis peu de temps j'ai peut-être mal utilisé les outils.
09/10/2023, 19h27
JPaul_B

Citation:

Envoyé par jurassic pork

Hello,
JPaul_B si tu as un classeur exemple sans données confidentielles avec le genre d'erreur que tu veux contrôler, cela sera plus simple pour essayer de te répondre.
Ami calmant, J.P

Bonsoir Jurassic pork,
il faut que je trouve une solution pour réaliser un fichier exemple, car J'ai des datas concernant des clients et si j'envoie mon programme même sans datas confidentielles, je fait en faire fuir plus d'un car il à quelques centaines de ligne!:(. de plus, je dois trouver la bonne méthode pour mettre les fichiers interrogés en même temps afin de tester les liaisons.

Bonjour JPaul,
Voici un code exemple où l'on actualise la table requête t_TimeSheet

Code:

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 RefreshPowerQuery()
    Dim ls As ListObject
    Dim qt As QueryTable
    Dim erreurMessage As String
 
    On Error GoTo ErrorHandler ' Gestion d'erreur
 
    ' Référence à la feuille de calcul et la table de requête
    Set ls = Range("t_TimeSheet").ListObject
    Set qt = ls.QueryTable
    ' Actualiser la table de requête
    qt.Refresh BackgroundQuery:=False
 
ErrorHandler:
  ' Si Erreur
  If Err.Number Then
    ' Message d'erreur personnalisé
    erreurMessage = "Erreur " & Err.Number & " : " & Err.Description
    MsgBox erreurMessage, vbCritical
  End If
  ' Fin
  On Error GoTo 0
  Set qt = Nothing: Set ls = Nothing
End Sub

09/10/2023, 21h11
JPaul_B

[QUOTE=Philippe Tulliez;11976910]Bonjour JPaul,
Voici un code exemple où l'on actualise la table requête t_TimeSheet

Super merci bcp Philippe je vais le tester!
par contre, je fait un ActiveWorkbook.RefreshAll et j'ai 15 querries. Comment je peux adapter ton code pour balayer ces Queries...
Merci bcp pour ton temp passé!

Citation:

Envoyé par Philippe Tulliez

Bonjour JPaul,
Voici un code exemple où l'on actualise la table requête t_TimeSheet

Code:

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 RefreshPowerQuery()
    Dim ls As ListObject
    Dim qt As QueryTable
    Dim erreurMessage As String
 
    On Error GoTo ErrorHandler ' Gestion d'erreur
 
    ' Référence à la feuille de calcul et la table de requête
    Set ls = Range("t_TimeSheet").ListObject
    Set qt = ls.QueryTable
    ' Actualiser la table de requête
    qt.Refresh BackgroundQuery:=False
 
ErrorHandler:
  ' Si Erreur
  If Err.Number Then
    ' Message d'erreur personnalisé
    erreurMessage = "Erreur " & Err.Number & " : " & Err.Description
    MsgBox erreurMessage, vbCritical
  End If
  ' Fin
  On Error GoTo 0
  Set qt = Nothing: Set ls = Nothing
End Sub

bonjour Philippe
J'ai testé ta gestion d'erreur mais j'ai eu un soucis:
j'ai remplacer le "t_TimeSheet" par le nom d'une de mes requêtes mais j'obtient l'erreur "la méthode range de l'objet "_worksheet" a échoué (1004) à la ligne:
Set ls = Range("Nbr_Facture_Devis").ListObject, Nbr_Facture_Devis est le nom du tableau issu de la requête du même nom.

J'ai enfin trouvé le pourquoi: le programme était dans une des feuilles worksheet. Je l'ai mis dans un module est cela à fonctionné.
le code final est le suivant sachant que je dois MàJ plusieurs queries que j'ai listé dans un tableau.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
'pour la gestion d'erreur des queries
Sub RefreshPowerQuery()
Dim ls As ListObject
Dim qt As QueryTable
Dim c As Range
Dim erreurMessage As String
 
On Error GoTo ErrorHandler
For Each c In [T_Requetes] 'table des liste de querries à MàJ
    ' Référence à la feuille de calcul et la table de requête
     Set ls = Range(c.Value).ListObject
     Set qt = ls.QueryTable
    ' Actualiser la table de requête
     qt.Refresh BackgroundQuery:=False
Next c
 
ErrorHandler:
' Si Erreur
If Err.Number Then
    ' Message d'erreur personnalisé
    Call EnregistrerAction("Erreur " & Err.Description & " dans " & c.Value & ";module RefreshPowerQuery") 'inscription dans fichier log
    erreurMessage = "Erreur " & Err.Number & " : " & Err.Description & " dans " & c.Value
    MsgBox erreurMessage, vbCritical
End If
' Fin
On Error GoTo 0
Set qt = Nothing: Set ls = Nothing
End Sub

Bonjour JPaul,
Pour lister les tables issues de Power Query ou Power Pivot, on peut vérifier la propriété SourceType et donc les actualiser

Exemple

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Sub t()
  Dim s As Worksheet
  Dim t As ListObject
  For Each s In ThisWorkbook.Worksheets
    For Each t In s.ListObjects
      If t.SourceType = xlSrcQuery Or t.SourceType = xlSrcModel Then
         Debug.Print t.Name  ' Remplacer cette ligne par l'actualisation
      End If
    Next
  Next
  Set s = Nothing: Set t = ListObject
End Sub