Microsoft 365 comment mettre plusieurs fichiers excel de même formats sur une seule feuille

[desto]

Bonjour à tous
je viens solliciter votre asssistance pour un coup de main sur la meilleur manière de regrouper plusieurs fichiers Excel provenant d'un dossier sur une nouvelle feuille de classeur .
en effet j'ai une cinquantaine de fichier par année que je voudrais fusionner afin de faire un traitement mais je n'arrive pas à trouver la meilleure façon de procéder .
Je vous prie de me donner quelques idées pour avancer.

 

[Deadpool_CC]

Bonjour, un fichier d'exemple serait bien car cela sera surement une macro qui fera le travail :
ouvrir les fichiers 1 à 1 puis copier les données à la suite des précédentes dans la feuille cible, ... ainsi de suite.
mais sans fichiers d'exemple pas facile de te faire une marco qui fonctionne selon ton attente.

 

[sylvanu]

Bonjour Desto,
Auriez vous un petit fichier test ? Ce serait plus parlant.
Est ce que tous les fichiers sont identiques sur le format ?
Est ce que tous les fichiers sont rangés dans le même dossier ?
Si oui, est ce que tous les fichiers dans ce dossiers sont à traiter ? ( hormis bien sur le fichier récapitulatif )
Dans ce fichier recap, voulez vous une feuille par fichier, ou toutes les données dans la même feuille ?

 

[Hasco]

Bonjour,

Par Power query qui est fait pour ce genre de traitement.

Cordialement

 

[desto]

Bonjour, un fichier d'exemple serait bien car cela sera surement une macro qui fera le travail :
ouvrir les fichiers 1 à 1 puis copier les données à la suite des précédentes dans la feuille cible, ... ainsi de suite.
mais sans fichiers d'exemple pas facile de te faire une marco qui fonctionne selon ton attente.

Bonsoir @Deadpool_CC

Je te prie de trouver ci joint quelques exemples. Les fichiers nommés"jan_2020_env & fev2020_env"" ont les mêmes entêtes et ceux nommés " Jan_2020_Pmt & Fev_2020_Pmt" aussi ont les mêmes entêtes.
Ce sont ces types de fichiers représentant chaque mois de l'année depuis 2018 que je souhaite consolider par année.

 

[desto]

Bonjour Desto,
Auriez vous un petit fichier test ? Ce serait plus parlant.
Est ce que tous les fichiers sont identiques sur le format ?
Est ce que tous les fichiers sont rangés dans le même dossier ?
Si oui, est ce que tous les fichiers dans ce dossiers sont à traiter ? ( hormis bien sur le fichier récapitulatif )
Dans ce fichier recap, voulez vous une feuille par fichier, ou toutes les données dans la même feuille ?

Bonsoir @sylvanu
j'ai joint les fichier en répondant à @Deadpool_CC

 

[desto]

Bonjour,

Par Power query qui est fait pour ce genre de traitement.

Cordialement

Bien reçu Hasco.
Je ne suis pas encore très doué avec Power Query

 

[sylvanu]

Bonsoir tout le monde,
Comme je n'ai pas eu de réponses à mes questions, un essai en PJ.
- Tous les fichiers à compiler doivent être dans le même dossier
- Y compris cette PJ.
- Toutes les données sont dans une seule feuille.
Avec :

Sub Compiler()
    Dim CeFichier$, Chemin$, Fichier$
    Range("A2:HK" & 1 + Range("A1000000").End(xlUp).Row).ClearContents  ' Effacer données présentes
    Application.ScreenUpdating = False
    CeFichier = ThisWorkbook.Name                       ' Mémorisation fichier courant
    Chemin = CurDir & "\"                               ' Mémorisation chemin courant
    Fichier = Dir(Chemin & "*.xls*", vbNormal)          ' Filtre fichier XL uniquement
    Do While Fichier <> ""
        If Fichier <> CeFichier Then                    ' Fichier à traiter
            Application.StatusBar = "Traitement du fichier : " & Fichier
            DL = 1 + Range("A1000000").End(xlUp).Row    ' Première ligne où écrire
            Workbooks.Open Filename:=Fichier            ' Ouverture du fichier à lire
            DLcible = Workbooks(Fichier).Sheets(1).Range("A1000000").End(xlUp).Row ' Dernière ligne à lire
            Workbooks(CeFichier).Sheets(1).Range("A" & DL & ":HK" & DL + DLcible - 2) = _
            Workbooks(Fichier).Sheets(1).Range("A2:HK" & DLcible).Value ' Transfert données
            Workbooks(Fichier).Close SaveChanges:=False ' Fermeture fichier cible sans enregistrer
        End If
        Fichier = Dir                                   ' Fichier suivant
    Loop
    Columns.AutoFit                                     ' Ajustement largeur colonnes
    Application.StatusBar = ""
End Sub

 

[Hasco]

Bonjour,

Pour vous donner une idée du travail de power query et sur la base des fichiers que vous nous avez donnés au post#5.
Dans la requête nommée 'Nautes' à l'étape source, changez le chemin vers votre répertoire qui contient les fichiers (que vous nous avez donnés en exemple).
La requêtes sélectionne tous les fichiers qui contiennent _env ou _pmt sans prendre en compte minuscule ou majuscule. (Vos noms de fichier n'ont pas de modèle régulier (fev2020 ou Fev_2020)

Si vous avez des transformations à faire sur les données, faites les dans la requête "Transformer l'exemple de fichier" dans le groupe des requêtes d'assistances.
C'est powerquery qui construit tout seul ces requêtes. Dès que vous faites une transformation dans la requête sus-dite, cette transformation est reflétée automatiquement dans la fonction "Transformer le fichier" qui elle est appelée sur toutes les lignes de la requête "Nautes" à l'étape nommé (par PQ) "Appeler une fonction personnalisée1"

Dans le tableau de la requête finale, pour chaque ligne, vous trouverez une colonne contenant le nom du fichier source de la ligne. On est pas obligé de la conserver.

Cordialement

 

[desto]

Bonsoir tout le monde,
Comme je n'ai pas eu de réponses à mes questions, un essai en PJ.
- Tous les fichiers à compiler doivent être dans le même dossier
- Y compris cette PJ.
- Toutes les données sont dans une seule feuille.
Avec :

Sub Compiler()
    Dim CeFichier$, Chemin$, Fichier$
    Range("A2:HK" & 1 + Range("A1000000").End(xlUp).Row).ClearContents  ' Effacer données présentes
    Application.ScreenUpdating = False
    CeFichier = ThisWorkbook.Name                       ' Mémorisation fichier courant
    Chemin = CurDir & "\"                               ' Mémorisation chemin courant
    Fichier = Dir(Chemin & "*.xls*", vbNormal)          ' Filtre fichier XL uniquement
    Do While Fichier <> ""
        If Fichier <> CeFichier Then                    ' Fichier à traiter
            Application.StatusBar = "Traitement du fichier : " & Fichier
            DL = 1 + Range("A1000000").End(xlUp).Row    ' Première ligne où écrire
            Workbooks.Open Filename:=Fichier            ' Ouverture du fichier à lire
            DLcible = Workbooks(Fichier).Sheets(1).Range("A1000000").End(xlUp).Row ' Dernière ligne à lire
            Workbooks(CeFichier).Sheets(1).Range("A" & DL & ":HK" & DL + DLcible - 2) = _
            Workbooks(Fichier).Sheets(1).Range("A2:HK" & DLcible).Value ' Transfert données
            Workbooks(Fichier).Close SaveChanges:=False ' Fermeture fichier cible sans enregistrer
        End If
        Fichier = Dir                                   ' Fichier suivant
    Loop
    Columns.AutoFit                                     ' Ajustement largeur colonnes
    Application.StatusBar = ""
End Sub

Bonjour @sylvanu
Désolé de n'avoir pas fourni les réponses à tes questions.
Est ce que tous les fichiers sont identiques sur le format ?==Oui

Est ce que tous les fichiers sont rangés dans le même dossier ? == Oui Chaque dossier est nommé par le nom de l'année concernée.

Si oui, est ce que tous les fichiers dans ce dossiers sont à traiter ? ( hormis bien sur le fichier récapitulatif )== Oui , les fichiers à ne pas traiter seront supprimés du dossier avant le traitement
Dans ce fichier recap, voulez vous une feuille par fichier, ou toutes les données dans la même feuille ?== toutes les donnée dans la même feuille pour chaque dossier séléctionnée

 

[sylvanu]

Bonjour,
Ce sont les hypothèses que j'ai prises.
Donc ma PJ devrait faire le job.