XL 2019 Importer des classeurs à la suite dans un tableau existant

[Oscar21]

Bonjour,

J'ai plusieurs factures en Excel que j'aimerai insérer dans un fichier Excel récapitulatif.
Le tableau récapitulatif est déjà existant et j'aimerai rajouter les données de chaque facture à la suite dans ce tableau, sans créer un nouveau tableau.
Enfin, comment répéter cette opération facilement pour chaque nouvelle facture ?

J'avais pensé à un bouton dans le fichier destination qui permettrai d'aller chercher les factures dans un répertoire précis ?
En annexe deux fichiers exemples , une facture et le récapitulatif.

Merci pour votre aide

 

[Hasco]

Bonjour,

Souvent c'est l'inverse qui se fait, on part d'un tableau qui compulse toutes les éléments de facture pour éditer ensuite une ou pluiseur facture sur un modèle définit et à partir de ces éléments
Mais bien-sûr on peut faire comme vous le voulez.
Pour plus de facilité et simplicité des requêtes il serait interressant de mettre le numéro de facture dans le nom du fichier, en début si possible, et mieux encore, avec la date : exemple "2023-02-22 55 Facture avec calcul TVA.xlsx"
Cela permettrait de sélectionner uniquement celles qui sont traitées de celles qui ne le sont pas encore sans avoir à les ouvrir (par requête) à chaque actualisation.
Autre avantage, le tri et le classement des factures.

 

[Oscar21]

Bonjour Hasco et merci pour votre réponse.
Je suis néophite en Power Query.

Oui, en effet mettre la date et le n° de facture permettrai certainement de mieux trier les factures ou documents. Ces deux fichiers sont juste un exemple pour illustrer ce que j'aimerai faire. Je ne peux pour des raisons de confidentialité mettre les vrais fichiers en ligne.

Ce qu'on doit faire c'est importer des données de documents Excel hebdomadaire au format identiques dans un tableau. Ces documents doivent être importé régulièrement et les données doivent être ajoutées au fur et à mesure à la suite l'une de l'autre dans le même tableau.

Les documents ne sont pas conservés, seules les données sont conservées. J'ai utilisé Power Query car il me permet de remettre mes données dans le bon ordre. Le problème à chaque importation d'un document avec Power Query il crèe un nouveau tableau.
Ma première question
Y-a-t-il une solution, pour importer plusieurs document sans devoir à chaque importation refaire la procédure (Données, importer des données d'un fichier excel, mise en forme etc...). ?

La deuxième
Y-a-t-il une solution pour que Power Query mette les données à la suite les unes des autres dans le même tableau ?

J'espère que mes explications sont compréhensibles

 

[Hasco]

Bonjour

Les documents ne sont pas conservés,

Un élément TRES important que vous n'aviez pas fourni et qui compliquait les choses, Le sachant j'aurais perdu moins de temps.

Dans le classeur joint vous trouverez une requête "Factures dossier" qui récupère les informations dans un dossier de tous les fichiers dont le nom contient "Facture"
Dans la feuille "Facture dossier" en F1 nommée "Dossier" vous avez le chemin vers le dossier. La requête se sert de cette cellule et de son nom, alors vérifiez son contenu avant d'actualiser la requête par le bouton vert qui se trouve sur la même feuille.
Une fois toutes les données du dossier récupérées, cliquez sur le bouton bleu qui transférera ces données vers le tableau de feuille "Résumé des factures" et que j'ai nommé "T_Factures" (c'est plus explicite que "Tableau1"

Dans vos factures, vous avez plusieurs lignes de détails (description), la fonction de récupération des données, concatène toutes ces lignes dans la colonne "Description".

Le zip contient les factures exemple et le récapitulatif

Cordialement

 

[Oscar21]

Bonjour Hasco,
Merci pour votre réponse. Désolé de vous avoir fait perdre du temps la première fois.
Ce n'est pas évident d'expliquer ce qu l'on veut faire sans mettre les vrais fichiers en ligne.
J'ai donc dû créer deux fichiers pour expliquer au mieux ce que je dois faire.

Je vais regarder votre fichier et essayer de tout comprendre.
Je reviens vers vous.
Cordialement

 

[Oscar21]

Whoaw, c'est exactement ce dont j'ai besoin. Maintenant je dois juste décrypter le code (je suis aussi novice en VBA) et adapter tout cela à mes fichiers .
En tout cas déjà un grand merci

 

[Oscar21]

Bonjour Hasco,
J'ai regardé le fichier et j'aimerai l’adapter.
Je connais un peu PowerQuery, mais pas beaucoup le VBA.

J'ai vu que tu as créer deux requêtes Power Query (Facture dossier, RécupérationDonnéesExempel).
Il y a aussi une fonction ? fx fnRécupérationDonnées
Plus deux macro liées aux boutons (RafraîchirFacturesDossier et TransférerFacturesDossiers)
Ensuite le code en Visual basic (ci-dessous)

Puis-je te demander comment tout cela fonctionne?

Encore merci pour le temps que tu consacre à ma demande.
Cordialement
oscar




Sub TransférerFacturesDossier()
Dim Dest As Range
Dim nb As Long

With ThisWorkbook
' Source des données à copier/coller
With .Sheets("Résumé des factures").ListObjects("T_Factures")
' si la ligne d'insertion n' est active
' on crée une ligne de tableau qu'on prend pour destination
If .InsertRowRange Is Nothing Then
Set Dest = .ListRows.Add().Range
Else
' sinon on prend la ligne d'insertion comme destination
Set Dest = .InsertRowRange
End If
End With
' Destination des données
With .Sheets("Factures dossier").ListObjects("Factures_dossier")
' Si le tableau contient des lignes
If Not .DataBodyRange Is Nothing Then
nb = .ListRows.Count ' nombre de lignes de données
.DataBodyRange.Copy Dest ' copier et coller dans la destination

' sur demande, supprimer les lignes d'origines
If MsgBox(nb & " ligne(s) transférée(s) !" & vbCrLf & vbCrLf _
& "Voulez-vous supprimer les lignes ici ?", _
vbYesNo + vbQuestion, "Transfert de lignes") = vbYes Then
.DataBodyRange.Delete ' supprimer les lignes (facultatif)
End If
End If

End With
End With
End Sub

 

[Hasco]

Bonjour,

La partie Power query :
1 - préambule :
Power query ne permet pas de mettre à jour un tableau avec uniquement des données nouvelles de la source de départ.

2 - objectif : rajouter au tableau T_Factures de la feuille "Résumé des factures", les données des nouveaux fichiers factures du dossier source.

3 - moyen :
3.1 - créer une requête "FacturesDossiers" qui liste les fichiers factures du dossier source et transforme en ligne de résumé grâce à la fonction "fnRécupérationDonnées". Les lignes de résumé sont chargées dans un tableau nommé automatiquement "FacturesDossier".
3.2 - les données de ce tableau sont transférées dans le tableau "T_Factures" de l feuille "Résumé"

4 - problématique :
En règle générale dans ce genre de situation, dans la requête qui liste les fichiers, sont exclus les lignes des fichiers déjà traités dans le tableau de destination finale (pour vous, T_Factures) pour ne pas créer de doublons avec les fichiers du dossier déjà traités. Pour cela il faut comparer soit les noms de fichiers qui auraient été gardés avec les résumés dans "T_Factures". Soit ouvrir tous les fichiers et comparer les contenus avec les résumés existants.
La demande ne spécifiait pas la conservation du nom de fichier d'origine.
C'est pour cette raison que je vous conseillais de mettre au moins le n° de facture dans le nom du fichier. Mais comme vous m'avait dit que vous ne conserviez pas les fichiers, inutile d'exclure les lignes qui comporteraient d'anciens fichiers. Tous les fichiers facture du dossier sont sensés être nouveaux et donc ne pas avoir été traités par la requête.
Bien entendu on peut changer ce comportement. Si vous voulez conserver les anciens fichiers traités, dites le moi. en précisant si vous mettrez ou non le numéro de facture dans leur nom (requête plus rapide)

5 - la requête "RécupérationDonnéesExemple" :
Je devrais plutôt l'appeler "RécupérationDonnéesBrouillon" car c'est un brouillon de travaille que je transforme fonction (préfixée 'fn') qui devra être appelé sur les lignes de la requête "FacturesDossier" à l'étape "Datas".
Affichez "fnRécupérationDonnées" dans l'éditeur avancé et ensuite "RécupérationDonnéesExemple" pour voir les petites différences. Ce qui varie ce sont surtout les 2 ou 3 premières lignes, l'appel ne se faisant pas de la même manière.
Si il y a un problème avec la fonction, il est plus facile de corriger une requête normale puis de modifier, quand tout est ok, la fonction correspondante.

La partie VBA :
Ce code se contente de copier les données du tableau issus de la requête pour les coller en nouvelle position de votre tableau "T_Factures".
Il me semblait avoir suffisamment commenté les lignes de codes. Et je ne sais pas quoi vous dire de plus. C'est un langage, il faut l'apprendre, mais là n'est pas le but du forum, même si on peut essayer d'être un peu didactique.

Si vous avez des questions précises sur le code, une ligne, un mot clef, posez votre question.

P.S. lorsque vous publiez un code dans vos posts, utilisez le bouton </> de la barre de menu de l'éditeur de post. Sinon c'est quasi imbuvable à la lecture.

 

[Oscar21]

Bonjour Hasco,
Je vous remercie pour toutes ces explications.
Je vais analyser cela et essayer de comprendre

Cordialement
PS : OK pour </>

 

[Oscar21]

Bonjour,

La partie Power query :
1 - préambule :
Power query ne permet pas de mettre à jour un tableau avec uniquement des données nouvelles de la source de départ.

2 - objectif : rajouter au tableau T_Factures de la feuille "Résumé des factures", les données des nouveaux fichiers factures du dossier source.

3 - moyen :
3.1 - créer une requête "FacturesDossiers" qui liste les fichiers factures du dossier source et transforme en ligne de résumé grâce à la fonction "fnRécupérationDonnées". Les lignes de résumé sont chargées dans un tableau nommé automatiquement "FacturesDossier".
3.2 - les données de ce tableau sont transférées dans le tableau "T_Factures" de l feuille "Résumé"

4 - problématique :
En règle générale dans ce genre de situation, dans la requête qui liste les fichiers, sont exclus les lignes des fichiers déjà traités dans le tableau de destination finale (pour vous, T_Factures) pour ne pas créer de doublons avec les fichiers du dossier déjà traités. Pour cela il faut comparer soit les noms de fichiers qui auraient été gardés avec les résumés dans "T_Factures". Soit ouvrir tous les fichiers et comparer les contenus avec les résumés existants.
La demande ne spécifiait pas la conservation du nom de fichier d'origine.
C'est pour cette raison que je vous conseillais de mettre au moins le n° de facture dans le nom du fichier. Mais comme vous m'avait dit que vous ne conserviez pas les fichiers, inutile d'exclure les lignes qui comporteraient d'anciens fichiers. Tous les fichiers facture du dossier sont sensés être nouveaux et donc ne pas avoir été traités par la requête.
Bien entendu on peut changer ce comportement. Si vous voulez conserver les anciens fichiers traités, dites le moi. en précisant si vous mettrez ou non le numéro de facture dans leur nom (requête plus rapide)

5 - la requête "RécupérationDonnéesExemple" :
Je devrais plutôt l'appeler "RécupérationDonnéesBrouillon" car c'est un brouillon de travaille que je transforme fonction (préfixée 'fn') qui devra être appelé sur les lignes de la requête "FacturesDossier" à l'étape "Datas".
Affichez "fnRécupérationDonnées" dans l'éditeur avancé et ensuite "RécupérationDonnéesExemple" pour voir les petites différences. Ce qui varie ce sont surtout les 2 ou 3 premières lignes, l'appel ne se faisant pas de la même manière.
Si il y a un problème avec la fonction, il est plus facile de corriger une requête normale puis de modifier, quand tout est ok, la fonction correspondante.

La partie VBA :
Ce code se contente de copier les données du tableau issus de la requête pour les coller en nouvelle position de votre tableau "T_Factures".
Il me semblait avoir suffisamment commenté les lignes de codes. Et je ne sais pas quoi vous dire de plus. C'est un langage, il faut l'apprendre, mais là n'est pas le but du forum, même si on peut essayer d'être un peu didactique.

Si vous avez des questions précises sur le code, une ligne, un mot clef, posez votre question.

P.S. lorsque vous publiez un code dans vos posts, utilisez le bouton </> de la barre de menu de l'éditeur de post. Sinon c'est quasi imbuvable à la lecture.

Re bonjour Hasco,
j'ai bien analysé le code VBA (les deux macros) et la fonction "fnrécupération des données". J'ai enfin pu tout comprendre avec l'aide d'un ami et je l'ai adapté à mes documents. Cependant vu que ce n'est pas moi qui vais utiliser ce programme, j'ai des craintes que la personne clic deux fois sur le bouton transférer, ce qui créera des doublons.
Dans votre proposition vous me disiez qu'il y avait moyen d'exclure les factures déjà traitées.

Bien entendu on peut changer ce comportement. Si vous voulez conserver les anciens fichiers traités, dites le moi. en précisant si vous mettrez ou non le numéro de facture dans leur nom (requête plus rapide)

Je suis interessé pour avoir cette solution, sachant que les fichiers factures portent un numéro
Cordialement

 

[Hasco]

Bonjour,

Peut-être pourrais-je vous proposer la solution en fin d'après-midi ou demain matin. Sinon je ne pourrais sans doute pas avant plusieurs jours.

Il faudra un ou deux fichiers exemples nommés sur le modèle définitif, afin de déterminer où seront les numéros de facture.

Cordialement

 

[Oscar21]

Bonjour,
Voici en annexe quelques exemples de factures, le format reste toujours le même.
le nom de la facture commencera toujours par son numéro (en général 6 chiffres) suivi d'un "_" puis le nom du document.

 

[Oscar21]

Il n'y a pas d'urgence, c'est déjà très gentil de m’aider dans ce projet.
Cordialement

 

[Hasco]

Bonjour,

Voici une proposition. La feuille "Factures dossier" peut être cachée. Le code est dans le module de code de la feuille "Résumé des facture" et une petite macro d'initialisation dans "Module 1"

j'ai des craintes que la personne clic deux fois sur le bouton transférer, ce qui créera des doublons.

Vous n' empêcherez jamais les utilisateurs de faire des bêtises. On ne peut qu'en réduire le risque et faire appel à la pédagogie et leur responsabilité.



Cordialement

 

[Oscar21]

Bonjour Hasco,

Merci pour ce fichier qui manifestement fonctionne super bien.
Comme je peux voir au premier coup d’œil, tout le code se trouve maintenant essentiellement dans PowerQuery.

Le programme effectue une comparaison de ce qui a déjà été importé dans l'onglet Résumé des factures avec ce qui se trouve dans le répertoire des factures. Ça va nettement plus vite !!.

Je vais pouvoir analyser cela plus facilement, vu que je ne connais pas bien VisualBasic

Juste une petite question, l'onglet "facture dossier" est-il toujours nécessaire ?

Cordialement
Oscar