Discussion :

[Zekraoui_Jakani]

Quelqu'un avec un oeil de lynx peut-il voir la source de l'erreur "Token Comma expected" dans ce script ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub getExtract()
 
Dim sSql As String
Dim debut, fin As Date
 
debut = ThisWorkbook.Sheets("Modèle Client").Range("D3")
fin = ThisWorkbook.Sheets("Modèle Client").Range("F3")
 
debut = DateSerial(Year(debut), Month(debut), Day(debut))
fin = DateSerial(Year(fin), Month(fin), Day(fin))
 
On Error Resume Next
ActiveWorkbook.Queries("MaTable_1").Delete
 
sSql = "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""MaTable""]}[Content], " & Chr(13) & "" & Chr(10) & ""
sSql = sSql & "#""Changed Type"" = Table.TransformColumnTypes(Source,{{""DateExcécution"", type datetime}, {""Sens"", type text}, {""OrdreNbr"", Int64.Type}, "
sSql = sSql & "{""Produit"", type text}, {""Mise"", type number}, {""Ouverture"", type number}, {""Clôture"", type number}, {""G_P"", type text}, {""Paiement"", " & _
"type number}})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", "
sSql = sSql & "each [DateExcécution] > #datetime(" & Year(debut) & ", " & Month(debut) & ", " & Day(debut) & ", 15, 20, 1) " & _
"And [DateExcécution] < #datetime(" & Year(fin) & ", " & Month(fin) & ", " & Day(fin) & ", 15, 20, 1))" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""
 
    ActiveWorkbook.Queries.Add Name:="MaTable_1", Formula:=sSql

[Raccourcix]

Bonjour
le mieux est d'intégrer les paramètres "début" et "fin" dans Power Query et de filtrer la table suivant ces dates et non de passer par VBA pour créer à chaque fois une nouvelle requête.
il est possible de lier ces paramètres avec un tableau ou des plages nommées directement dans l'onglet Excel.

Stéphane

[Raccourcix]

En complément voici un exemple illustré :

2 cellules nommées Début et Fin que l'on récupère dans PQ (A partir de tableau ou d'une plage, puis Drill down pour obtenir le contenu)
une table que l'on filtre d'abord avec les filtres proposés dans l'éditeur et on change la formule pour faire référence aux deux paramètres.
très rapide à mettre en place et on est tranquille ensuite.



Stéphane

[Philippe Tulliez]

Bonjour Stéphane,
Super exemple d'une utilisation simple et efficace de PowerQuery sans l'usage de VBA

[Zekraoui_Jakani]

Il est vrai que les solutions les plus simples sont les meilleures, mais il est des cas, comme celui-ci, où la pléthore des variables et des scénarii impose une conception complexe, mais efficiente !.
J'ai néanmoins intégré la suggestion de Raccourcix dans cet enchevêtrement, dont je n'ai montré que le haut de l'iceberg.

[Pierre Fauconnier]

Salut.

Je n'ai jamais très bien compris l'intérêt de passer par du VBA pour créer des requêtes Power Query.

A priori, dans la très grande majorité des cas, la solution Power Query est créée lors de la conception, en ce compris la mise en place des critères et paramètres, éventuellement récupérés d'Excel par requêtes et fonctions spécifiques DANS Power Query.

Souvent, le passage par VBA témoigne d'un défaut de maîtrise de Power Query et il est alors plus intéressant de combler la lacune plutôt que de la contourner.


My 2 cents, comme on dit

[Zekraoui_Jakani]

Merci pour les réponses tardives, mais j'ai déjà résolu le problème (je crois que j'ai clôturé la discussion; oubli de ma part?!).
N'en déplaise à certains, la combinaison VBA + PowerQuery est une prouesse réservée à ceux qui jouent dans la cour des grands; il suffit d'avoir un minimum de bon sens et de matière grise !

[Pierre Fauconnier]

[...]la combinaison VBA + PowerQuery est une prouesse réservée à ceux qui jouent dans la cour des grands; il suffit d'avoir un minimum de bon sens et de matière grise !

La preuve avec un Token comma Expected La cour est tellement grande que tu y as perdu la virgule...


Power Query se crée lors de la conception, il ne sert souvent à rien d'essayer de créer du Power Query par VBA. PQ, comme les graphiques ou les TCD se créent lors de la conception du classeur et c'est prendre le problème par le mauvais bout que de créer la solution Power Query par VBA.

Qu'est-ce qui ne faut pas lire comme conn***, parfois ("ceux qui jouent dans la cour des grands"... et dire que tu me parlais d'ego en mp). Je suis mort de rire.

[Zekraoui_Jakani]

A propos de "conn***", le nom de l'interlocuteur se compose de 3 syllabes; par chance, ça s'écrit en 1 seul mot !

[Pierre Fauconnier]

*** Réponse "humoristique" qui pourra servir à celles et ceux qui veulent utiliser Excel, VBA, Power Query et autres... "comme des grands"... ***

Il y a là quelques bonnes pratiques à glaner pour qui veut progresser dans l'utilisation d'Excel, même indépendamment de Power Query

Texte écrit sur base du code donné (11 lignes...) en premier message de la discussion

[...]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub getExtract()
 
Dim sSql As String
Dim debut, fin As Date
 
debut = ThisWorkbook.Sheets("Modèle Client").Range("D3")
fin = ThisWorkbook.Sheets("Modèle Client").Range("F3")
 
debut = DateSerial(Year(debut), Month(debut), Day(debut))
fin = DateSerial(Year(fin), Month(fin), Day(fin))
 
On Error Resume Next
ActiveWorkbook.Queries("MaTable_1").Delete
 
sSql = "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""MaTable""]}[Content], " & Chr(13) & "" & Chr(10) & ""
sSql = sSql & "#""Changed Type"" = Table.TransformColumnTypes(Source,{{""DateExcécution"", type datetime}, {""Sens"", type text}, {""OrdreNbr"", Int64.Type}, "
sSql = sSql & "{""Produit"", type text}, {""Mise"", type number}, {""Ouverture"", type number}, {""Clôture"", type number}, {""G_P"", type text}, {""Paiement"", " & _
"type number}})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", "
sSql = sSql & "each [DateExcécution] > #datetime(" & Year(debut) & ", " & Month(debut) & ", " & Day(debut) & ", 15, 20, 1) " & _
"And [DateExcécution] < #datetime(" & Year(fin) & ", " & Month(fin) & ", " & Day(fin) & ", 15, 20, 1))" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""
 
    ActiveWorkbook.Queries.Add Name:="MaTable_1", Formula:=sSql

[...]il suffit d'avoir un minimum de bon sens et de matière grise !

Le minimum de bon sens et de matière grise aurait dû te permettre de comprendre que, surtout lorsque l'on joue dans la cour des grands avec du VBA et PQ, renommer les étapes en évitant les caractères diacritiques et les espaces permet d'éviter les hashtags et les guillemets autour des noms des étapes de la requête, ce qui évite d'alourdir le code VBA, notamment avec le doublement des guillemets dans la chaine de caractères qui reconstitue la requête Power Query. C'est déjà pelant de concaténer ce genre de texte (dans la cour des grands, on ne connaît que ce moyen... Dans la cour des petits, on joue avec du texte enregistré quelque part dans lequel on a inséré des balises pertinentes et on remplace simplement les balises...), alors, autant s'éviter au maximum du code ingérable. Ca fait "pro" face à un débutant (wouah, le code!!), mais c'est juste du code amateur et mal torché

[...]prouesse réservée à ceux qui jouent dans la cour des grands[...]

Perso, tant qu'à jouer dans la cour des grands, je stockerais mes requêtes dans un tableau Excel en utilisant des balises pour les valeurs à modifier dans le texte, puis je passerais par une petite fonction très simple pour remplacer tout en une fois, plutôt que de me casser à tenter d'insérer les valeurs des paramètres dans une concaténation de 7 lignes. Ca permettrait probablement d'éviter de perdre une virgule dans la cour des grands (cfr ton erreur Token Comma Expected)... Au passage, je suppose que se créer des bibliothèques de code générique à réutiliser à l'envi, ce n'est pas pour la cour des grands...

Dans la cour des grands, on ne supprime pas une requête pour la recréer, on modifie simplement sa formule...

Normalement, lorsque l'on joue dans la cour des grands, on sait que "and" s'écrit en minuscules en langage M. L'écrire "And" tel que tu l'as fait amène Power Query à générer une erreur "Token Comma Expected" et le "développeur" (... du dimanche) cherche alors partout une virgule manquante alors que c'est en fait un problème de A <> a C'est vrai que ce n'est pas donné à tout le monde de savoir que le langage M différencie les majuscules des minuscules

Tant qu'à jouer dans la cour des grands, nommer une requête Table_1 (ça vient probablement du tableau structuré Excel qui s'appelle Table), ce n'est pas forcément le meilleur choix, surtout lorsqu'il y a pléthore de variables et de scenarii comme tu te plais à le signaler dans ce message (le "haut de l'iceberg"... ). C'est vrai que Table_1, Table_2, Table_3, ça permet de s'y retrouver nettement mieux dans la pléthore des cas à gérer... C'est vrai que le script PQ que tu exposes est terriblement complexe (2 paramètres et 4 étapes dont 2 créées automatiquement par Power Query... Wouah, la pléthore... Oufti, la prouesse)...

Tant qu'à jouer dans la cour des grands, on n'utilise pas On Error Resume Next de la manière que tu exposes, puisque cela va neutraliser les erreurs d'exécution sans pouvoir les gérer. La gestion correcte des erreurs dans du code VBA fait partie des jeux auxquels on ne joue pas dans la cour des grands, apparemment.

Tant qu'à jouer dans la cour des grands, passe-toi des lignes debut = DateSerial(Year(debut), Month(debut), Day(debut)) et fin = DateSerial(Year(fin), Month(fin), Day(fin)) qui ne servent strictement à rien. Si tu utilises Year(debut), c'est que debut contient une date. Du coup, debut = DateSerial(Year(debut), Month(debut), Day(debut)), c'est "rigolo", mais ça ne fait pas très sérieux dans la cour des grands. Dans la cour des petits, on bosse, alors on n'a pas le temps d'écrire du code inutile.

Tant qu'à jouer dans la cour des grands, j'aurais nommé les plages contenant les dates de début et de fin. Ca permet de découpler le code de l'emplacement de ces données dans la feuille, et ça permet d'éviter de travailler avec ThisWorkbook.Sheets("Modèle Client") (au passage, ThisWorkbook est inutile, sauf lorsqu'il y a pléthore de variables, de scenarii, etc... ), car si la feuille est renommée par la suite, ton code plantera (je suppose que, dans la cour des grands, on se fout des détails de ce genre...)

Tant qu'à jouer dans la cour des grands, j'aurais variabilisé le moment de la journée (15:20:01) histoire de ne pas devoir le rechercher dans le code fouillis que tu as proposé le jour où l'heure limite est modifiée. Les bonnes pratiques comme "ne pas mettre de données dans le code (hard coding)", ce n'est pourtant pas réservé à la cour des petits, et dans la pléthore des cellules gérant tes variables, tu aurais pu trouver une petite place pour ajouter celle-là. Les "bonnes pratiques", c'est aussi pour les grands

Quand on joue dans la cour des grands, surtout lorqu'il y a pléthore de variables, de scenarii et de colonnes dans la multitude des tables à gérer (Wouah, attention aux chevilles qui enflent), on essaie de pas se gourer dans les noms de colonnes (DateExcécution devrait s'écrire DateExécution) Ca évitera au développeur professionnel qui repassera derrière le bricoleur pour corriger ses âneries et utiliser correctement Power Query de se casser les dents sur une bête erreur de frappe (Cas vécu de multiples fois lorsque le petit que je suis doit reprendre le code d'un "grand" qui a pondu de la mer***). Mais bon, je suppose que c'est de la faute du petit plouc dans son bac à sable qui a créé le tableau (quel imcompétent, quand même, ce plouc...)...



Autant d'amateurisme dans aussi peu de code créé dans la cour des grands à coup de bon sens et de matière grise, c'est juste affligeant.

Voici le code que j'aurais utilisé dans le bac à sable de la cour des petits, s'appuyant sur un tableau structuré qui contient les scripts, comme dans le fichier joint...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub UpdateQuery()
  Dim Index As Long
  Dim Text As String
  Dim StartDate As Date, EndDate As Date, LimitTime As Date
 
  StartDate = Range("Début").Value
  EndDate = Range("Fin").Value
  LimitTime = Range("HeureLimite").Value
 
  Index = Application.Match("OrdresPassés", Range("t_Requêtes[Nom]"), 0)
  Text = Range("t_Requêtes[Requête]")(Index).Value
  ReplaceStrings Text, Array( _
    "|AnneeDebut|", Year(StartDate), "|MoisDebut|", Month(StartDate), "|JourDebut|", Day(StartDate), _
    "|HeureDebut|", Hour(LimitTime), "|MinutesDebut|", Minute(LimitTime), "|SecondesDebut|", Second(LimitTime), _
    "|AnneeFin|", Year(EndDate), "|MoisFin|", Month(EndDate), "|JourFin|", Day(EndDate), _
    "|HeureFin|", Hour(LimitTime), "|MinutesFin|", Minute(LimitTime), "|SecondesFin|", Second(LimitTime))
  ActiveWorkbook.Queries("OrdresPassés").Formula = Text
End Sub
 
Function ReplaceStrings(Source As String, Values, Optional Compare As VbCompareMethod = vbTextCompare)
  Dim i As Long
 
  For i = LBound(Values) To UBound(Values) Step 2
    Source = Replace(Source, Values(i), Values(i + 1), Compare)
  Next
End Function

Enfin non, car lorsque j'ai du Power Query à mettre en place, je ne m'embête pas à utiliser du VBA. Je maîtrise Power Query et je crée ma pléthore de requêtes scénarisées et variabilisées dans l'outil créé pour, à savoir l'interface Power Query qui offre un environnement de développement de requêtes convivial et confortable. Je développe des requêtes PQ à longueur d'année pour mes clients dans Excel et Power BI et je n'ai JAMAIS dû créer une ligne de code VBA pour gérer cela, malgré la réelle pléthore de variables et de scenarii manipulés.



Pour la route, un billet de mon blog qui explique comment paramétrer PQ au départ d'Excel, avec une plage nommée ou un tableau structuré (lorsqu'il y a pléthore de paramètres).

[chrtophe]

N'en déplaise à certains, la combinaison VBA + PowerQuery est une prouesse réservée à ceux qui jouent dans la cour des grands;

Je pense que c'est surtout un contournement de problème de conception. Un peu comme si tu fais une addition en VBA après une requête SQL alors que tu peux la faire directement en SQL.

[Pierre Fauconnier]

Je pense que c'est surtout un contournement de problème de conception. Un peu comme si tu fais une addition en VBA après une requête SQL alors que tu peux la faire directement en SQL.

De toute façon, le script Power Query proposé à l'entame de la discussion est simplement un code récupéré de Power Query en VBA et nullement un code créé de toutes pièces en VBA. Les appellations des étapes PWQ le prouvent, et personne de sensé n'écrirait une requête PWQ en VBA de cette façon. Après, de malheureuses tentatives de le modifier en VBA amènent à des erreurs du côté de Power Query.

C'est un peu comme si on pondait un code C# en Java. C'est juste totalement déb***