L’automatisation des tâches est dans l’air du temps ; surtout lorsqu’il s’agit de tâches répétitives dépourvues de valeur ajoutée. Il en est ainsi de tâches d’extraction / importation de données effectuées de manière récurrente.
L’exposé ci-après sera illustré par un exemple très concret : l’extraction de statistiques mensuelles des immatriculations de véhicules publié par la PFA. Dans un précédent article, l’extraction des statistiques était effectuée manuellement. Dans cet article, cette extraction sera automatisée à l’aide d’un programme original.
Si le langage VBA est le langage de prédilection pour automatiser l’exécution de tâches, le langage M est, lui, le langage d’extraction et de manipulation de données par excellence. L’association de ces deux langages permet de bénéficier de leurs capacités respectives.
Fonctionnement du programme (code-source détaillé plus loin) :
Pour lancer le programme, ouvrir le classeur intitulé “Automatisation import tableaux PDF.xlsm” téléchargeable au bas de cet article et cliquer sur le bouton “Importer tableaux PFA”.
Le programme demande à l’utilisateur de sélectionner le fichier PDF (à obtenir sur le site du PFA ou à télécharger en bas de page) à traiter (instruction .GetOpenFileName). Ensuite, cliquer sur le bouton Ouvrir. Le nom du fichier (et son chemin d’accès) est stocké dans la variable FichierPDF.
Ceci fait, la boucle compteur (For.. To… Next…) traite chacun des trois tableaux de données désignés successivement (à l’aide de la structure conditionnelle Select Case… End Select) par la variable TableEnCours.
Select Case i
Case 1:
TableEnCours = "Table003 (Page 2)"
SourceEnCours = "Table003"
Case 2:
TableEnCours = "Table005 (Page 3)"
SourceEnCours = "Table005"
Case 3:
TableEnCours = "Table007 (Page 4)"
SourceEnCours = "Table007"
End Select
Ensuite, le programme crée la requête (.Queries.Add Name:=”” & TableEnCours) interrogeant le fichier PDF. La formule de la requête est introduite par la commande “Formula:=“. La formule est rédigée en langage M.
Le langage M est le langage de manipulation de données implémenté par Microsoft sur le module ETL (Extract-Transform-Load) Power Query d’Excel. Power Query permet à Excel de se connecter à des sources de données variées (bases de données, classeurs Excel, fichiers ASCII / CSV, fichiers PDF…) et de mettre en forme les données pour répondre aux besoins de l’utilisateur. Un exemple de connexion à un fichier ASCII (en l’occurrence un FEC) est fourni dans l’article Analyser un FEC de plus d’un million de lignes dans Excel.
Un programme écrit en VBA est en mesure de prendre le contrôle de Power Query en vue d’automatiser des requêtes rédigées en langage M. C’est précisément ce que réalise le programme objet de cet article.
Requête en langage M :
La requête rédigée en Langage M est appelée par le programme écrit, lui, en langage VBA, ce qui appelle quelques commentaires.
Le texte de la requête est le suivant :
let
Source = Pdf.Tables(File.Contents("C:\Users\COLDIR01\Downloads\Flash-Marche-publication-mensuelle-PFA-0522.pdf"), [Implementation="1.3"]),
TableDonnées = Source{[Id="Table003"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(TableDonnées, [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Marque", type text}, {"Volume", Int64.Type}, {"%", type number}, {"Volume_1", Int64.Type}, {"%_2", type number}, {"%Var", type number}, {"Volume_3", Int64.Type}, {"%_4", type number}, {"Volume_5", Int64.Type}, {"%_6", type number}, {"%Var_7", type number}}),
#"Erreurs supprimées" = Table.RemoveRowsWithErrors(#"Type modifié", {"Volume"})
in
#"Erreurs supprimées"
La requête est introduite par l’instruction “Let“ ; elle est suivie de la définition de la source de données puis de la liste des champs de données à reprendre et, le cas échéant, des transformations de données (création de champs calculés, définition des formats…).
let Variablename = expression, #"Variable name" = expression2 in Variablename
La source de données est désignée ici par “Source =” suivi du type de source (ici PDF : Pdf.Tables) puis de la localisation de la source. Table.PromoteHeaders indique que la première ligne du tableau correspond à l’entête de colonne (nom du champ de données).
Le commande .TransformColumnTypes précise le type de données de chaque champ de données. Type text précise que le champ de données est de type alphanumérique, c’est-à-dire qu’il contiendra indifféremment du texte, des chiffres, des symboles… Type number spécifie un nombre réel (avec des décimales) alors que Int64.type spécifie un nombre entier (donc sans décimales). Si le type de données spécifié est numérique (entier ou réel) et que le contenu du champ est alphanumérique, un message d’erreur apparaîtra (“Error”) :
La commande .RemoveRowsWithErrors supprime les enregistrements (lignes) pour lesquelles le champ de données “Volume” génère une erreur (car non numérique). Dans le cas présent, le tableau de données intègre inutilement la dernière ligne citant la source AAA DATA :
Cette même requête est représentée sous cette forme dans l’éditeur avancé de Power Query (accessible à partir du classeur Excel généré par le code VBA) :
Requête en langage M intégrée au code VBA :
Le programme VBA automatise la génération des requêtes en langage M avec la commande .Queries.Add. La rédaction de la requête fait l’objet de quelques aménagements afin d’être intelligible par le code VBA.
Tout d’abord, pour le code VBA la requête en langage M n’a pas de signification particulière. Aussi cette requête, identifiée par la commande “Formula =“, est-elle stockée sous la forme d’une simple chaîne de caractères. Les guillemets servent à délimiter des chaînes de caractères tant en Langage M qu’en VBA, il est nécessaire d’ajouter un guillemet pour chaque série de guillemets présente dans la requête M.
Ensuite, pour que le programme VBA créé des requêtes personnalisées à la volée, il est nécessaire de remplacer le nom des sources de données et des tables de données initialement définies en dur (Table003, Table005…) par des variables. Une variable correspond à un emplacement mémoire qui stocke des données, des résultats de calculs… La valeur prise par une variable peut être changée à loisir durant l’exécution du programme. C’est cette possibilité qui est mise à profit ici pour créer trois requêtes semblables à l’aide d’une seule ligne de code.
Requête en langage M intégrée dans le programme VBA :
ActiveWorkbook.Queries.Add Name:="" & TableEnCours & "", Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" Source = Pdf.Tables(File.Contents(""" & FichierPDF & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & _
" TableDonnées = Source{[Id=""Table003""]}[Data]," & Chr(13) & "" & Chr(10) & _
" #""En-têtes promus"" = Table.PromoteHeaders(TableDonnées, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
" #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Marque"", type text}, {""Volume"", Int64.Type}, {""%"", type number}, {""Volume_1"", Int64.Type}, {""%_2"", type number}, {""%Var"", type number}, {""Volume_3"", Int64.Type}, {""%_4"", type number}, {""Volume_5"", Int64.Type}, {""%_6"", type number}, {""%Var_7"", type number}})," & Chr(13) & "" & Chr(10) & _
" #""Erreurs supprimées"" = Table.RemoveRowsWithErrors(#""Type modifié"", {""Volume""})" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & _
" #""Erreurs supprimées"""
Cette requête est créée à trois reprises (du fait de la boucle compteur For… To… Next) pour récupérer chacune des trois tables désignées tour à tour.
Une fois ces trois tables intégrées, une dernière requête est rédigée (toujours en langage M) afin de fusionner les trois premières. Cette fusion est opérée à l’aide de la commande .Combine suivie du nom des tables à fusionner :
let
//Fusion des trois tables précédemment importées
Source = Table.Combine({#"Table003 (Page 2)", #"Table005 (Page 3)", #"Table007 (Page 4)"})
in
Source
Le classeur Excel nouvellement créé par le programme est maintenant à disposition de l’utilisateur. Ses données peuvent servir de base pour créer, par exemple, un tableau de bord (cf à ce sujet les deux séries d’articles dédiées à ce sujet : tableaux de bord dynamiques et tableaux de bord visuels).
Les requêtes créées par la macro VBA peuvent être ouvertes par le lecteur pour analyse. Pour ce faire, dans le classeur créé par le programme VBA, se rendre dans le menu Données puis Requêtes et connexions :
Les requêtes disponibles s’affichent à droite de l’écran :
Pour visualiser le contenu de la requête : faire un clic droit sur une des requêtes puis sélectionner Modifier dans le menu contextuel. Power Query, le module ETL d’Excel s’ouvre alors et affiche le résultat de la requête (ici avec la requête ToutesTables) :
Pour visualiser le texte de la requête (et éventuellement l’amender), cliquer sur Editeur avancé (menu Accueil) :
Le code source de la macro VBA est retranscrit in extenso ci-après :
'---------------------------------------------------------------------------------------
'Automatisation de la récupération des tables chiffrées des états d'immatriculations PFA
'Ecrit par Benoît RIVIERE 07/2022
'---------------------------------------------------------------------------------------
Option Explicit
Sub ImportTablesDonnéesPFA()
'Définition des variables
'---Nom du fichier PDF à traiter (comprend le chemin d'accès)
Dim FichierPDF As Variant
'---Variable boucle compteur
Dim i As Integer
'---Nom de la table de données en cours de traitement
Dim TableEnCours As String
'---Source en cours de traitement
Dim SourceEnCours As String
'---Variable tableau contenant le nom des tables de données
Dim Tables(3) As String
'Collecte du nom du fichier PDF à traiter
FichierPDF = Application.GetOpenFilename("Fichiers PDF (*.pdf),*.pdf,Tous les fichiers (*.*),*.* ", 1, "Sélectionnez le fichier PDF à traiter", , False)
'---Si le traitement est annulé par l'utilisateur (bouton annulé, touche Echap...) -> arrêt du programme
If FichierPDF = False Then Exit Sub
'Création d'un nouveau classeur Excel
Workbooks.Add
'Boucle de traitement des tables de dnnées PDF
'---3 tabes de données à traiter
For i = 1 To 3
'En fonction de la position du compteur (1, 2 ou 3), définition du tableau de données à importer
Select Case i
Case 1:
TableEnCours = "Table003 (Page 2)"
SourceEnCours = "Table003"
Case 2:
TableEnCours = "Table005 (Page 3)"
SourceEnCours = "Table005"
Case 3:
TableEnCours = "Table007 (Page 4)"
SourceEnCours = "Table007"
End Select
Tables(i) = TableEnCours
'Création de la requête interrogeant le fichier PDF (formule rédigée en langage M)
ActiveWorkbook.Queries.Add Name:="" & TableEnCours & "", Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" Source = Pdf.Tables(File.Contents(""" & FichierPDF & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & _
" TableDonnées = Source{[Id=""" & SourceEnCours & """]}[Data]," & Chr(13) & "" & Chr(10) & _
" #""En-têtes promus"" = Table.PromoteHeaders(TableDonnées, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
" #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Marque"", type text}, {""Volume"", Int64.Type}, {""%"", type number}, {""Volume_1"", Int64.Type}, {""%_2"", type number}, {""%Var"", type number}, {""Volume_3"", Int64.Type}, {""%_4"", type number}, {""Volume_5"", Int64.Type}, {""%_6"", type number}, {""%Var_7"", type number}})," & Chr(13) & "" & Chr(10) & _
" #""Erreurs supprimées"" = Table.RemoveRowsWithErrors(#""Type modifié"", {""Volume""})" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & _
" #""Erreurs supprimées"""
Next i
'Création d'une requête (intutulée "ToutesTables") fusionnant le résultat des trois requêtes précédentes (toujours en langeg M)
ActiveWorkbook.Queries.Add Name:="ToutesTables", Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" Source = Table.Combine({#""" & Tables(1) & """, #""" & Tables(2) & """, #""" & Tables(3) & """})" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & _
" Source"
'Création d'une nouvelle feuille de travail dans le classeur en curs
ActiveWorkbook.Worksheets.Add
'Le résultat de la requête "ToutesTables" est affiché dans l'onglet nouvellement créé sous la forme d'un tableau de données
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ToutesTables;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [ToutesTables]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "ToutesTables"
.Refresh BackgroundQuery:=False
End With
End Sub
Afin de s’exercer, le lecteur trouvera ci-après en libre téléchargement le classeur Excel comprenant la macro VBA, les statistiques d’immatriculations de mai et juin 2022 (fichiers PDF) ainsi qu’un classeur Excel généré par la macro VBA.
Le lecteur souhaitant approfondir ses connaissances sur le langage M pourra consulter l’abondante documentation sur le site de Microsoft.
___
En savoir plus sur l’analyse de données / Programmer en VBA / Exécuter des requêtes SQL dans du code VBA
Derniers articles parBenoît RIVIERE (voir tous)
- Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
- Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
- Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
Pingback: Excel : automatiser l'extraction des tableaux d...