XL 2021 VBA et Power Query : ecrire des requetes avec variables

[Florent74]

Bonjour à tous,

Je cherche a importer par vba le contenu de fichiers pdf dans Excel. Le nombre de pages et le nombre de tableaux par pages étant variables. Il s'agit de résultats d'analyses d'eau potable. L'objectif est de pouvoir scinder le pdf contenant plusieurs analyses, chaque analyses faisant un nombre de pages variables, de scinder le pdf en autant de pdf qu'il y a d'analyses, et en les renommant selon plusieurs critères selon la date, le numéro de l'analyse et le point de prélèvement.

Je me suis orienté vers l'importation de sources de données avec Power Query. L'enregistreur de macro n'est pas satisfaisant car il écrit en dur chaque requetes selon le nombre de pages, le nombre de tableaux par page et le nombre de colonne par tableaux.

Je cherche à automatiser tout cela avec VBA mais je n'ai pas assez de connaissances en vba/Power Query pour adapter le code avec des boucles ou variables selon le nombre de pages, de tableaux et de colonnes par tableau. Et je trouve peux de ressource sur les différents forum à ce sujet.

Je dispose de la version d'Adobe. En pièce jointe, un exemple de fichier pdf (ici, le fichier ne comporte qu'une seule analyse sur une page, mais j'aimerais arriver à l'importer automatiquement par Power Query avec en résultat dans Excel, un onglet par page et tableaux).

Merci si vous arrivez à m'éclairer ou m'orienter sur une solution !!

 

[Florent74]

Hello,
Florent74 tu n'as pas demandé dans les informations à extraire la commune de prélèvement ce qui permet de repérer à quoi correspond une analyse.
J'ai légèrement modifié le code de mromain pour extraire cette info :
Pour le labo Lidal :

ExtractCommune = let txt = List.Select(BufferInfosPage1, each Text.Contains(_, "Commune* :")){0} in [Commune = Text.AfterDelimiter(txt, "Commune* : ")],
                    ConcatResult = ExtractDatePrelevement & ExtractDateReception & ExtractRefEchantillon & ExtractTypeVisite & ExtractTypeAnalyse & ExtractCommune

Pour le labo Savoie Labo :

ExtractCommune = let txt = List.Select(BufferInfosPage1, each Text.Contains(_, "Commune :")){0} in [Commune = Text.AfterDelimiter(txt, "/",1)],             
                    ConcatResult = ExtractDates & ExtractRefEchantillon & ExtractTypes & ExtractCommune

et voici ce que cela donne :
Regarde la pièce jointe 1206245

et comme le précise mromain il faut du code différent pour chaque type de labo car leurs PDF n'a pas la même structure. J'espère qu'il n'y a pas trop de labos différents sinon cela risque d'être difficile à gérer.

Pour l'extraction des résultats voici une requête PowerQuery qui fait le minimum :

let
    Source = Pdf.Tables(File.Contents("D:\Temp\E24.4967.pdf"), [Implementation="1.3"]),
    TableRes = Source{[Id="Table003"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(TableRest, [PromoteAllScalars=true]),
    #"Colonnes renommées" = Table.RenameColumns(#"En-têtes promus",{{"Column1", "Séléction"}})
in
    #"Colonnes renommées"

Il faut choisir la table qui contient les résultats (ici la Table003)
Regarde la pièce jointe 1206246

Ami calmant, J.P

Merci Jurassik Pork !!

Heureusement, il n’y a que ces deux labos !!

Pour l’extraction des résultats, tu spécifies en dur dans la requete la Table003. Est-ce que, par rapport à la structure du pdf, le numéro de table qui contient les résultats est toujours le même ?

Je vais faire quelques essais de mon côté avec les exemples que vous m’avez fournis et reviendrais vers vous ensuite pour vous indiquez mes avancées !!

Merci encore !!

 

[jurassic pork]

Merci Jurassik Pork !!
Heureusement, il n’y a que ces deux labos !!
Pour l’extraction des résultats, tu spécifies en dur dans la requete la Table003. Est-ce que, par rapport à la structure du pdf, le numéro de table qui contient les résultats est toujours le même ?

C'est surtout mromain qu'il faut remercier, moi je n'ai fait presque rien.
Pour l'extraction des résultats, mromain dans son code à repérer les pages et tables qui sont pour la même analyse (colonne Id_Analyse). Il suffit de repérer pour une Id_Analyse quelle est la table qui contient "Paramètres analytiques" ou "PARAMETRE ANALYTIQUE" et d'extraire les données de cette table. Je suis sûr que mromain ou chris ou une autre personne sera capable de faire ce traitement. Tu n'as pas besoin de connaître le nombre de pages car la table contient TOUS les résultats.
Ensuite en mettant tous les PDF dans un même répertoire on doit être capable de boucler sur l'extraction de tous les PDF. Mais il faut savoir si tu veux tous les résultats dans un même classeur.
Dans ce cas sur la première feuille, il y a les infos des analyses avec une colonne rajoutée avec un lien
vers l'onglet où se trouvent les extractions de résultats.
Tout cela je ne sais pas si c'est facilement faisable car je ne connais pas assez Power Query.

 

[mromain]

Bonjour Florent74, chris, jurassic pork, le forum

Ci-joint une autre proposition (testée avec le fichier ExempleAnalyses.pdf fourni au post #13).
Il y a toujours le tableau existant listant les différentes analyses du fichier pdf.
Il y a en plus une seconde requête qui permet d'accéder au détail d'une analyse particulière à partir de l'ID_Analyse.
Le détail renvoyé est le suivant :

• pour le labo SavoieLabo : le tableau dont la première cellule vaut PARAMETRE ANALYTIQUE ;
• pour le labo Lidal : le tableau contenant une cellule avec µS/cm.

Une première colonne contenant le texte concaténé de la ligne est également rajoutée.

Il est toujours possible de piloter par VBA si tu veux traiter ensuite le détail de l'analyse :

Sub Test()
Dim pathPdf As Variant
Dim idAnalyse As Long
    'sélectionner le fichier
    pathPdf = Application.GetOpenFilename("Fichier pdf (*.pdf), *.pdf", , "Fichier analyse", , False)
    If pathPdf = False Then Exit Sub
    
    With Feuil1
        'charger la liste des analyses du fichier
        .Range("Rng_PathPdf").Value = pathPdf
        .ListObjects("Tab_ExtractAnalyses").Refresh
        'boucler sur chaque analyse
        For idAnalyse = 1 To .ListObjects("Tab_ExtractAnalyses").ListRows.Count
            'charger le détail de l'analyse
            .Range("Rng_IdAnalyse").Value = idAnalyse
            .ListObjects("Tab_DetailAnalyse").Refresh
            'effectuer le traitement sur le détail de l'analyse
            '...
            '...
            Application.Goto .ListObjects("Tab_DetailAnalyse").Range, True
            MsgBox "Détail de l'analyse " & idAnalyse & " sur " & .ListObjects("Tab_ExtractAnalyses").ListRows.Count, vbInformation, "Info"
        Next idAnalyse
    End With
End Sub

comment as tu crées la requete ? Ecrite à la main ? Dans ce cas, où puis-je trouver les ressources pour comprendre toute la syntaxe de la requete ?

Une bonne partie oui, depuis l'Éditeur avancé.
Pour les ressources, il y a l'ensemble de cours Power Query M Primer de Ben Gribaudo. C'est en anglais, mais ça permet de bien appréhender le langage M (types de données, syntaxe, ...). Ensuite, il y a la documentation des fonctions soit chez Microsoft, soit une autre version un peu plus agrémentée chez PQ HOW.

A+

 

[jurassic pork]

En modifiant légèrement le code VBA de mromain, on peut créer dans le classeur une feuille pour chaque analyse (qui se nomme dans mon exemple Analyse <idAnalyse> :

Sub Test()
Dim pathPdf As Variant
Dim idAnalyse As Long
Dim ws As Worksheet
Dim linkCell As Range
    'sélectionner le fichier
    pathPdf = Application.GetOpenFilename("Fichier pdf (*.pdf), *.pdf", , "Fichier analyse", , False)
    If pathPdf = False Then Exit Sub
   
    With Feuil1
        'charger la liste des analyses du fichier
        .Range("Rng_PathPdf").Value = pathPdf
        .ListObjects("Tab_ExtractAnalyses").Refresh
        'boucler sur chaque analyse
        For idAnalyse = 1 To .ListObjects("Tab_ExtractAnalyses").ListRows.Count
            'charger le détail de l'analyse
            .Range("Rng_IdAnalyse").Value = idAnalyse
            .ListObjects("Tab_DetailAnalyse").Refresh
            'effectuer le traitement sur le détail de l'analyse
            '...
            '...
            .ListObjects("Tab_DetailAnalyse").Range.Copy
            'Application.Goto .ListObjects("Tab_DetailAnalyse").Range, True
            Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            ws.Name = "Analyse " & CStr(idAnalyse)
            ws.Range("A1").Select
            ws.Paste
            Set linkCell = .ListObjects("Tab_ExtractAnalyses").ListColumns(1).DataBodyRange(idAnalyse)
            ActiveSheet.Hyperlinks.Add Anchor:=linkCell, _
                              Address:="", _
                              SubAddress:="'" & ws.Name & "'!A1", _
                              TextToDisplay:=ws.Name
            'MsgBox "Détail de l'analyse " & idAnalyse & " sur " & .ListObjects("Tab_ExtractAnalyses").ListRows.Count, vbInformation, "Info"
        Next idAnalyse
    End With
    MsgBox ("FIN")
End Sub

A noter que le code crée aussi un lien entre les id de la colonne ID_Analyse de la Feuil1 vers la Feuille correspondante contenant l'analyse.
Ami calmant, J.P