Excel : automatiser l’extraction des tableaux de données d’un fichier PDF

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.

PQ Power Query Table003 (Page 2)

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”.

Bouton 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.

PQ Sélection fichier PDF

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”) :

PQ Power Query 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 :

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) :

PQ Table003 (Page 2)

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 :

Excel menu Données Requêtes

Les requêtes disponibles s’affichent à droite de l’écran :

Excel Liste Requêtes et connexions

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) :

PQ ToutesTables

Pour visualiser le texte de la requête (et éventuellement l’amender), cliquer sur Editeur avancé (menu Accueil) :

PQ Power Query ToutesTables éditeur avancé

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

Share Button
Automatisation import tableaux PDF
Automatisation import tableaux PDF
Automatisation-import-tableaux-PDF.xlsm
27 KiB
145 téléchargements
Détails...
Marché automobile français (VP) : Mai 2022
Marché automobile français (VP) : Mai 2022
Flash-Marche-publication-mensuelle-PFA-0522.pdf
347 KiB
67 téléchargements
Détails...
Marché automobile français (VP) : Juin 2022
Marché automobile français (VP) : Juin 2022
Flash-Marche-publication-mensuelle-PFA-0622_2.pdf
353 KiB
58 téléchargements
Détails...
Exemple de classeur créé par le programme VBA
Exemple de classeur créé par le programme VBA
Exemple-de-classeur-cree-par-le-programme-VBA.xlsx
30 KiB
85 téléchargements
Détails...
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Derniers articles parBenoît RIVIERE (voir tous)

Un commentaire

  1. Pingback: Excel : automatiser l'extraction des tableaux d...

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.