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 !!

 

[chris]

Bonjour

VBA est inutile ou à la marge

Joins au moins 2 autres cas que l'on voit justement tes pdf multipages et multi analyses ainsi qu'un exemple de la restitution attendue

 

[jurassic pork]

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

Hello,
ce n'est pas très clair ce que tu veux faire . Tu veux regénérer des PDF ( scinder le pdf en autant de pdf) ou importer toutes les analyses dans un classeur Excel (un onglet par page et tableaux) ? Je pense que tu ne pourra pas regénérer les PDF avec la mise en page initiale des PDF. Tu parles aussi de renommer les PDF mais dans si c'est une importation dans Excel ce sera le nom de l'onglet qui contiendra ce nom ? Peux-tu nous montrer par rapport au PDF que tu as joint à quoi doit ressembler ce nom. Dans le PDF que tu as joint si il n'y a que les résultats de l'analyse à importer cela ne semble pas trop difficile car ces résultats sont dans une table PQ.
Ami calmant, J.P

 

[Florent74]

Merci pour votre réponse !!

Voici ci-joint un fichier excel correspondant à l'importation d'un fichier pdf de 2 pages (2 pages pour une analyse). Je souhaiterais automatiser l'importation pour des fichiers de taille variable en nombre d'analyses, donc de pages et obtenir dans le fichier excel autant d'onglet que de pages et tableaux du pdf. Je n'arrive pas à téléhergares les pièces jointes (fichiers excel et pdf, qui sont supérieurs à 1 mO. Est-ce que c'est normal ?

Bonjour

VBA est inutile ou à la marge

Joins au moins 2 autres cas que l'on voit justement tes pdf multipages et multi analyses ainsi qu'un exemple de la restitution attendue

.

Hello,
ce n'est pas très clair ce que tu veux faire . Tu veux regénérer des PDF ( scinder le pdf en autant de pdf) ou importer toutes les analyses dans un classeur Excel (un onglet par page et tableaux) ? Je pense que tu ne pourra pas regénérer les PDF avec la mise en page initiale des PDF. Tu parles aussi de renommer les PDF mais dans si c'est une importation dans Excel ce sera le nom de l'onglet qui contiendra ce nom ? Peux-tu nous montrer par rapport au PDF que tu as joint à quoi doit ressembler ce nom. Dans le PDF que tu as joint si il n'y a que les résultats de l'analyse à importer cela ne semble pas trop difficile car ces résultats sont dans une table PQ.
Ami calmant, J.P

Bonjour, je vous remercie pour votre retour !



Effectivement, je peux préciser ma demande car elle comporte deux problèmes distincts.

1er problème : séparation d’un pdf multipages. Je reçois des résultats d’une ou plusieurs analyses dans un fichier pdf. Chaque analyse peut avoir un nombre variables de pages. Cette indication du nombre de pages pour chaque analyse est donné soit par le texte sous le bandeau gris du numéro du rapport d’analyse ("Il comporte x pages"), soit par le pied de page ("Page 1/x).

Je cherche une solution pour automatiser la création de pdf pour chaque analyses contenu dans le pdf principal. Le nom de chaque fichier pdf doit contenir le numéro du rapport (E23.279.1) et le code PSV (0000000007).



Je dispose d’Adobe Pro. Au départ, je pensais utiliser par macro VBA la recherche de mots clefs. J’arrive bien à trouver le mot clé "comporte" par exemple, mais je ne connais pas les commandes pour extraire les caractères suivants un mot clefs.

Je me suis donc tourné vers l’importation du pdf par Power Quéry. L’enregistreur de macro me donne un code qui écrit en dur le nombre de pages, de tableaux et de colonnes pour chaque tableau à importer depuis le fichier d’origine et importe les résultats dans autant d’onglets qu’il y a de pages et de tableau dans le pdf.

Cette solution n’est pas satisfaisante car le nombre de pages et de tableau, ainsi que le nombre de colonne par tableaux est variable selon les pdf d’origine à importer.

Je souhaiterais pouvoir écrire le code me permettant d’importer dans Excel les pages et tableaux contenus dans le pdf d’origine, quels que soient le nombre de pages et de tableau qu’il contient.



2ème problème : je souhaiterais importer de la manière la plus propre possible les pdf unitaires crées dans Excel, de manière à récupérer toutes les informations (numéro d’analyse, code et nom PSV, date, ainsi que tous les paramètres et leurs résultats) pour stocker le tout dans un fichiers excel de gestion des résultats.



Est-ce que vous auriez des pistes de réfléxions ?



J’espère que la problématique est un peu mieux énoncées ?



Merci !!

 

[jurassic pork]

ton dernier fichier PDF n'est pas trop représentatif car il a bien deux pages mais il n'y a rien d'intéressant sur la deuxième page. Il faudrait un fichier dont les résultats s'étalent sur plusieurs pages. Si c'est à cause de la taille des fichiers (limitée dans le forum) tu peux mettre tes fichiers sur un site de transfert et fournir dans le forum un lien vers ces fichiers. J'ai repéré un site intéressant pour faire cela c'est SwissTransfer qui est basé en suisse (avec la sécurité suisse )
Voici un extrait des conditions d'utilisation ( à lire sur le site) :

Article 2 - Description et portée du service
2.1 Infomaniak fournit au Client une infrastructure numérique pour le partage de fichiers pour lequel :

L’utilisation est gratuite ;
Le volume de données de chaque transfert est limité à cinquante (50) giga-octets (Go) ;
Le nombre de destinataires au maximum est fixé à vingt (20) ;
Infomaniak s’engage à transmettre les données de connexion via https ;
Le nombre maximal de téléchargements par fichier est de deux-cent cinquante (250) ;
Le nombre maximal de fichiers pouvant être envoyés par transfert est de cinq mille (5000) ;
Le nombre maximal de transferts par jour et par adresse IP est de cinq cent (500) ;
L’accès aux fichiers par l'URL de téléchargement est définit par la période de disponibilité choisie par le Client, soit au choix un (1), sept(7), quinze (15) ou trente (30) jours ;

Cela m'a l'air sérieux sans inscription. Il faut choisir le partage de lien (pas l'email).
J'espère que mon message ne va pas être censuré sachant que je n'ai rien touché de SwissTransfer pour faire la pub du site

 

[mromain]

Bonjour Florent74, chris, jurassic pork,

En complément d'un jeu de données plus représentatif comme demandé par jurassic pork, il serait intéressant que tu précises quelles sont les informations à récupérer pour chaque analyse.

Sinon, un début de pise avec l'ajout d'une colonne ID_Analyse au contenu du PDF. L'ID est incrémenté à chaque fois qu'on trouve Page 1 :

let
    Source = Pdf.Tables(File.Contents("C:\...\E23.279.1.pdf"), [Implementation="1.3"]),
    AddColumnID = 
        let
            ListDataLastCell = List.Buffer(List.Transform(Source[Data], each List.Last(List.RemoveNulls(Record.FieldValues(Table.Last(_)))))),
            ListAnalyseID = List.Accumulate(ListDataLastCell, {}, (s, c) => s & {(List.Max(s) ?? 0) + (if Text.StartsWith(c, "Page 1") then 1 else 0)})
        in
            Table.FromColumns(Table.ToColumns(Source) & {ListAnalyseID}, Table.ColumnNames(Source) & {"ID_Analyse"})
in
    AddColumnID

A+

 

[Florent74]

Bonjour Florent74, chris, jurassic pork,

En complément d'un jeu de données plus représentatif comme demandé par jurassic pork, il serait intéressant que tu précises quelles sont les informations à récupérer pour chaque analyse.

Sinon, un début de pise avec l'ajout d'une colonne ID_Analyse au contenu du PDF. L'ID est incrémenté à chaque fois qu'on trouve Page 1 :

let
    Source = Pdf.Tables(File.Contents("C:\...\E23.279.1.pdf"), [Implementation="1.3"]),
    AddColumnID =
        let
            ListDataLastCell = List.Buffer(List.Transform(Source[Data], each List.Last(List.RemoveNulls(Record.FieldValues(Table.Last(_)))))),
            ListAnalyseID = List.Accumulate(ListDataLastCell, {}, (s, c) => s & {(List.Max(s) ?? 0) + (if Text.StartsWith(c, "Page 1") then 1 else 0)})
        in
            Table.FromColumns(Table.ToColumns(Source) & {ListAnalyseID}, Table.ColumnNames(Source) & {"ID_Analyse"})
in
    AddColumnID

A+

Merci !!

Voici en pdf un peu plus intéressant. Il ne comprend qu’une seule analyse mais bien plusieurs page de résultats. L’idée étant de pouvoir détecter que le pdf ne comporte qu’une seule analyse, en comparant le nombre de pages du pdf avec le nombre de page que comporte l’analyse, (dans l’exemple en pièce jointe, le pdf fait 11 pages et l’analyse comporte 11 pages, donc le pdf ne contient qu’une seule analyse), (et dans ce cas, pas besoin de le scinder en plusieurs pdf) et de récupérer sur Excel toutes les données qu’il contient :

- description du prélèvements, c’est les données du cadre de la 1ere page, c’est à dire, identification du dossier, identification de l’échantillon, …, jusqu’à prélèvement : date et heure du prélèvement, date et heure de la réception de l’échantillon,

- résultat de l’analyse avec tous les paramètres analysés et les résultats associés.



Dans ta réponse mromain, tu joins le script d’une requete Power Query. Est-il possible de la coder en vba ? Dans mon objectif, je souhaiterais pouvoir automatiser le traitement des fichiers pdf que je reçois régulièrement

 

[Florent74]

ton dernier fichier PDF n'est pas trop représentatif car il a bien deux pages mais il n'y a rien d'intéressant sur la deuxième page. Il faudrait un fichier dont les résultats s'étalent sur plusieurs pages. Si c'est à cause de la taille des fichiers (limitée dans le forum) tu peux mettre tes fichiers sur un site de transfert et fournir dans le forum un lien vers ces fichiers. J'ai repéré un site intéressant pour faire cela c'est SwissTransfer qui est basé en suisse (avec la sécurité suisse )
Voici un extrait des conditions d'utilisation ( à lire sur le site) :

Cela m'a l'air sérieux sans inscription. Il faut choisir le partage de lien (pas l'email).
J'espère que mon message ne va pas être censuré sachant que je n'ai rien touché de SwissTransfer pour faire la pub du site

Je récupère demain un fichier plus complet et essaye ta méthode de transfert !!

 

[jurassic pork]

C'est bien ce que je pensais en fait , PQ récupère les résultats de tes 11 pages dans une seule table. Si tu as plusieurs analyses il y aura certainement plusieurs tables.
Premières lignes et dernières lignes (par masquage) de la table des résultats extraites sans traitement par PQ :

 

[Florent74]

Ca pourrait éventuellement me convenir !! A partir du moment où les données du pdf sont dans Excel je peux me débrouiller en vba pour faire les extractions dont j’ai besoin.



Par contre, avec quelle requete PQ arrives-tu à récupérer le pdf dans un seul tableau ? Et comment peux s’écrire cette requete en VBA ?



Merci !!

 

[jurassic pork]

Moi je ne suis pas un spécialiste de PQ , attend le retour de chris ou de mromain

 

[Florent74]

Moi je ne suis pas un spécialiste de PQ , attend le retour de chris ou de mromain

J’avais pas vu le tableau que tu avais joint !! C’est pas mal !! Jusqu’à présent, PQ collait les résultats dans plusieurs tableaux différents. Est-ce que tu pourrais me montrer la requete que tu as créée pour arriver à ce résultat ?

D’autre part, PQ m’insère dans des onglets Excel, les pages qui correspondent aux zones de texte du pdf. Et j’en aurai également besoin pour récupérer la description de l’analyse (date, identifiant, type d’analyse,…). Quelle requete faut-il créer ?



Merci encore !! Et ok pour voir avec chris ou mromain !!

 

[mromain]

Re-bonjour,

Et j'en aurai également besoin pour récupérer la description de l'analyse (date, identifiant, type d'analyse,…). Quelle requete faut-il créer ?

Plusieurs remarques :

• Extraire des infos d'un pdf n'est pas forcément trivial, même si c'est souvent faisable. Cela vient de comment PowerQuery voit les données du pdf.
• A ça, il faut rajouter le fait que tu as plusieurs types de pdf (laboratoire Lidal, laboratoire Savoie Labo) qui ont chacun leur propre structure. Il faudra à chaque fois un code particulier pour extraire les infos.

Cela dit, tu trouveras ci-joint un fichier avec une requête qui semble fonctionner pour extraire quelques infos sur les exemples que tu as fournis.

Pour tester, il faut :

• Renseigner l'emplacement du fichier pdf en B1 ;
• Mettre à jour le tableau (clic-droit > Actualiser).

Pour le coté pilotage par VBA, cela ne sert à rien de créer la requête en VBA, il suffit de la rafraichir.
Par exemple :

Sub Test()
    With Feuil1
        .Range("Rng_PathPdf").Value = "C:\...\ExempleAnalyses.pdf"
        .ListObjects("Tab_ExtractAnalyses").Refresh
    End With
End Sub

Je joins également un fichier pdf exemple qui contient les 3 analyses que tu nous as fournies précédemment.

A+

 

[jurassic pork]

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 :

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)

Ami calmant, J.P

 

[Florent74]

Re-bonjour,



Plusieurs remarques :

• Extraire des infos d'un pdf n'est pas forcément trivial, même si c'est souvent faisable. Cela vient de comment PowerQuery voit les données du pdf.
• A ça, il faut rajouter le fait que tu as plusieurs types de pdf (laboratoire Lidal, laboratoire Savoie Labo) qui ont chacun leur propre structure. Il faudra à chaque fois un code particulier pour extraire les infos.

Cela dit, tu trouveras ci-joint un fichier avec une requête qui semble fonctionner pour extraire quelques infos sur les exemples que tu as fournis.

Pour tester, il faut :

• Renseigner l'emplacement du fichier pdf en B1 ;
• Mettre à jour le tableau (clic-droit > Actualiser).

Pour le coté pilotage par VBA, cela ne sert à rien de créer la requête en VBA, il suffit de la rafraichir.
Par exemple :

Sub Test()
    With Feuil1
        .Range("Rng_PathPdf").Value = "C:\...\ExempleAnalyses.pdf"
        .ListObjects("Tab_ExtractAnalyses").Refresh
    End With
End Sub

Je joins également un fichier pdf exemple qui contient les 3 analyses que tu nous as fournies précédemment.

A+

Punaise, merci mromain !! Cela semble très prometteur !!



J’ai plusieurs questions :

- 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 ?

- quelle colonne faut-il rajouter au tableau pour récupérer le nombre de pages de chaque analyse du fichier pdf exemple que tu as crées en fusionnant les trois exemples ?

- Supposons que j’ai un fichier qui ne contient qu’une seule analyse, comportant une ou plusieurs page, quelle requete faut-il créer pour récupérer le tableau des résultats en une seule table, comme dans l’exemple fournit par Jurassik Pork ?



Je vais essayer de modifier ton fichier xlsx pour rajouter des colonnes au tableau que ta requete permet d’importer afin de récupérer d’autres infos décrivant l’analyse.



En tout cas, merci déjà pour le temps consacré !!