XL 2016 Combiner plusieurs feuilles de plusieurs fichiers

V

vivi21

XLDnaute Occasionnel
Bonjour,

J'ai un fichier d'activités par personne dans lequel j'ai plusieurs feuilles excel qui représente une fiche d'activités mensuelle (janvier, février, mars...) et une feuille "consolidation" qui récupère les données sur l'année. Le tableau se compose de plusieurs colonnes (nom, mois, année, compte, activités, commentaire et temps).
Je souhaite créer de manière automatique un fichier unique qui va récupérer la feuille "consolidation" de toutes les personnes de l'équipe.

Pouvez-vous m'aiguiller programmer ma demande ?

Merci.
Vivi21
 
Trier par date Trier par votes
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Vivi,
Cinq questions :
1- Tous les fichiers sont ils dans le même dossiers ?
2- N'y a t-il dans ce dossier que ces fichiers et votre fichier de consolidation
3- Ces feuilles de consolidation ont elles ou non des calculs provenant de feuilles autres qu'elles même ?
4- Quand on lance la macro, peut on détruire toutes les feuilles du fichier de consolidation pour le reconstruire ?
5- Auriez vous un petit fichier test d'un fichier de personne ( anonymisé) ?
 
Vote positif 0 Vote négatif
V

vivi21

XLDnaute Occasionnel
sylvanu à dit:
Bonjour Vivi,
Cinq questions :
1- Tous les fichiers sont ils dans le même dossiers ?
2- N'y a t-il dans ce dossier que ces fichiers et votre fichier de consolidation
3- Ces feuilles de consolidation ont elles ou non des calculs provenant de feuilles autres qu'elles même ?
4- Quand on lance la macro, peut on détruire toutes les feuilles du fichier de consolidation pour le reconstruire ?
5- Auriez vous un petit fichier test d'un fichier de personne ( anonymisé) ?
Cliquez pour agrandir...
Bonjour Sylvanu,

1- Oui tous les fichiers seront dans le même dossier
2- oui
3- oui (voir fichier) mais on peut coller uniquement les valeurs si c'est possible
4-oui on peut
5- le fichier représente la feuille consolidation
 

Pièces jointes

  • test.xlsx
    15.4 KB · Affichages: 9
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
Un essai en PJ.
J'ai pris l'option de nommer chaque feuille importée avec le nom du fichier. Pour le test c'est plus simple.
Pensez à modifier le chemin d'accès au dossier. ( Chemin = "C:\.....\ )
VB: 
    Chemin = "C:\Users\PC_PAPA\Desktop\Vivi\"   ' Doit se terminer par "\"
    CeFichier = ThisWorkbook.Name               ' Mémorisation nom du fichier courant
    ' Suppression des feuilles présentent
    Application.DisplayAlerts = False           ' Alertes inhibées pour confirmation de suppression
    For Each F In Worksheets
        If F.Name <> "Consolidation" Then Sheets(F.Name).Delete ' On ne garde que la feuille Consolidation
    Next F
    Application.ScreenUpdating = False
    ' Import des feuilles
    Fichier = Dir(Chemin & "*.xls*", vbNormal)
    Do While Fichier <> ""
        If Fichier <> CeFichier Then
            Workbooks.Open Filename:=Chemin & Fichier
                If Exist = True Then   ' Vérifie si la feuille Consolidation existe
                    With Workbooks(CeFichier)                   ' Créé une nouvelle feuille dans le classeur
                        .Activate
                        .Sheets.Add(after:=Sheets(.Worksheets.Count)).Name = Fichier    ' Créé feuille avec nom du fichier
                        Workbooks(Fichier).Worksheets("Consolidation").Cells.Copy .Worksheets(Fichier).[A1] 'Copie-colle la feuille
                        Workbooks(Fichier).Close False          'Referme le fichier source sans l'enregistrer
                    End With
                End If
        End If
        Fichier = Dir
    Loop
    Workbooks(CeFichier).Activate
    Sheets("Consolidation").Select
    Application.DisplayAlerts = True
End Sub
Function Exist() As Boolean
' Renvoie True si une feuille Consolidation existe dans le fichier
Exist = False
On Error Resume Next
    x = Sheets("Consolidation").[A1].Address
    If Err.Number = 0 Then Exist = True
On Error GoTo 0
End Function
 

Pièces jointes

  • Vivi.xlsm
    32.6 KB · Affichages: 15
Vote positif 0 Vote négatif
V

vivi21

XLDnaute Occasionnel
sylvanu à dit:
Re,
Un essai en PJ.
J'ai pris l'option de nommer chaque feuille importée avec le nom du fichier. Pour le test c'est plus simple.
Pensez à modifier le chemin d'accès au dossier. ( Chemin = "C:\.....\ )
VB: 
    Chemin = "C:\Users\PC_PAPA\Desktop\Vivi\"   ' Doit se terminer par "\"
    CeFichier = ThisWorkbook.Name               ' Mémorisation nom du fichier courant
    ' Suppression des feuilles présentent
    Application.DisplayAlerts = False           ' Alertes inhibées pour confirmation de suppression
    For Each F In Worksheets
        If F.Name <> "Consolidation" Then Sheets(F.Name).Delete ' On ne garde que la feuille Consolidation
    Next F
    Application.ScreenUpdating = False
    ' Import des feuilles
    Fichier = Dir(Chemin & "*.xls*", vbNormal)
    Do While Fichier <> ""
        If Fichier <> CeFichier Then
            Workbooks.Open Filename:=Chemin & Fichier
                If Exist = True Then   ' Vérifie si la feuille Consolidation existe
                    With Workbooks(CeFichier)                   ' Créé une nouvelle feuille dans le classeur
                        .Activate
                        .Sheets.Add(after:=Sheets(.Worksheets.Count)).Name = Fichier    ' Créé feuille avec nom du fichier
                        Workbooks(Fichier).Worksheets("Consolidation").Cells.Copy .Worksheets(Fichier).[A1] 'Copie-colle la feuille
                        Workbooks(Fichier).Close False          'Referme le fichier source sans l'enregistrer
                    End With
                End If
        End If
        Fichier = Dir
    Loop
    Workbooks(CeFichier).Activate
    Sheets("Consolidation").Select
    Application.DisplayAlerts = True
End Sub
Function Exist() As Boolean
' Renvoie True si une feuille Consolidation existe dans le fichier
Exist = False
On Error Resume Next
    x = Sheets("Consolidation").[A1].Address
    If Err.Number = 0 Then Exist = True
On Error GoTo 0
End Function
Cliquez pour agrandir...
Merci, je vais regarder si cela fonctionne de mon côté
 
Vote positif 0 Vote négatif
V

vivi21

XLDnaute Occasionnel
sylvanu à dit:
Re,
Un essai en PJ.
J'ai pris l'option de nommer chaque feuille importée avec le nom du fichier. Pour le test c'est plus simple.
Pensez à modifier le chemin d'accès au dossier. ( Chemin = "C:\.....\ )
VB: 
    Chemin = "C:\Users\PC_PAPA\Desktop\Vivi\"   ' Doit se terminer par "\"
    CeFichier = ThisWorkbook.Name               ' Mémorisation nom du fichier courant
    ' Suppression des feuilles présentent
    Application.DisplayAlerts = False           ' Alertes inhibées pour confirmation de suppression
    For Each F In Worksheets
        If F.Name <> "Consolidation" Then Sheets(F.Name).Delete ' On ne garde que la feuille Consolidation
    Next F
    Application.ScreenUpdating = False
    ' Import des feuilles
    Fichier = Dir(Chemin & "*.xls*", vbNormal)
    Do While Fichier <> ""
        If Fichier <> CeFichier Then
            Workbooks.Open Filename:=Chemin & Fichier
                If Exist = True Then   ' Vérifie si la feuille Consolidation existe
                    With Workbooks(CeFichier)                   ' Créé une nouvelle feuille dans le classeur
                        .Activate
                        .Sheets.Add(after:=Sheets(.Worksheets.Count)).Name = Fichier    ' Créé feuille avec nom du fichier
                        Workbooks(Fichier).Worksheets("Consolidation").Cells.Copy .Worksheets(Fichier).[A1] 'Copie-colle la feuille
                        Workbooks(Fichier).Close False          'Referme le fichier source sans l'enregistrer
                    End With
                End If
        End If
        Fichier = Dir
    Loop
    Workbooks(CeFichier).Activate
    Sheets("Consolidation").Select
    Application.DisplayAlerts = True
End Sub
Function Exist() As Boolean
' Renvoie True si une feuille Consolidation existe dans le fichier
Exist = False
On Error Resume Next
    x = Sheets("Consolidation").[A1].Address
    If Err.Number = 0 Then Exist = True
On Error GoTo 0
End Function
Cliquez pour agrandir...
Bonjour Sylvanu, je n'arrive pas à faire fonctionner la macro. Il supprime les feuilles mais après il n'y a rien qui se produit. Comment faire pour mettre des alerte et voir dans le programme de la macro où cela pourrait planter.
Désolée mais je n'ai pas pu regarder avant.
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Vivi,
En PJ un zip que j'ai utilisé et qui marche. Vous le dézippez où vous voulez, et lancer Vivi.xlsm.
Il faut bien sur dans le code changer le chemin avec le votre :
VB: 
Chemin = "C:\Users\PC_PAPA\Desktop\Vivi\"   ' Doit se terminer par "\"

Pour le debug, c'est simple. vous ouvrez la fenêtre éditeur VBA et vous positionnez le curseur sur la première ligne.
A chaque appui sur la touche F8 ( ou Menu Debogage/Pas à pas détaillé ) vous exécutez une ligne de code, cette ligne se met en jaune, cela vous permet de voir les variables dans la fenêtre Variables locales.
20220630_154827.gif
 

Pièces jointes

  • Vivi.zip
    61.8 KB · Affichages: 6
Vote positif 0 Vote négatif
V

vivi21

XLDnaute Occasionnel
sylvanu à dit:
Bonjour Vivi,
En PJ un zip que j'ai utilisé et qui marche. Vous le dézippez où vous voulez, et lancer Vivi.xlsm.
Il faut bien sur dans le code changer le chemin avec le votre :
VB: 
Chemin = "C:\Users\PC_PAPA\Desktop\Vivi\"   ' Doit se terminer par "\"

Pour le debug, c'est simple. vous ouvrez la fenêtre éditeur VBA et vous positionnez le curseur sur la première ligne.
A chaque appui sur la touche F8 ( ou Menu Debogage/Pas à pas détaillé ) vous exécutez une ligne de code, cette ligne se met en jaune, cela vous permet de voir les variables dans la fenêtre Variables locales.
Regarde la pièce jointe 1144040
Cliquez pour agrandir...
Je vais retenter merci pour votre rapidité de réponse
 
Vote positif 0 Vote négatif
V

vivi21

XLDnaute Occasionnel
vivi21 à dit:
Je vais retenter merci pour votre rapidité de réponse
Cliquez pour agrandir...
sylvanu à dit:
Bonjour Vivi,
En PJ un zip que j'ai utilisé et qui marche. Vous le dézippez où vous voulez, et lancer Vivi.xlsm.
Il faut bien sur dans le code changer le chemin avec le votre :
VB: 
Chemin = "C:\Users\PC_PAPA\Desktop\Vivi\"   ' Doit se terminer par "\"

Pour le debug, c'est simple. vous ouvrez la fenêtre éditeur VBA et vous positionnez le curseur sur la première ligne.
A chaque appui sur la touche F8 ( ou Menu Debogage/Pas à pas détaillé ) vous exécutez une ligne de code, cette ligne se met en jaune, cela vous permet de voir les variables dans la fenêtre Variables locales.
Regarde la pièce jointe 1144040
Cliquez pour agrandir...
Merci Sylvanu, effectivement j'avais bien changé le chemin mais j'avais oublié un terme. Merci pour les explications. Par contre lorsque je teste en ajoutant mes propres fichiers il bug car mon tableau de consolidation fait pour chaque personne 421 lignes. Du coup, Excel ne répond plus.
Pensez-vous que cela vient du nombres de lignes importantes.
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour,
Je viens de retester avec 50 fichiers de 1000 lignes, et c'est passé sans problème.
( J'ai eu une erreur mais c'est parce qu'un nom de fichier avait plus de 31 caractères à cause de duplication de fichiers pour le test , genre
Sophie - Copie - Copie - Copie - Copie.xlsx, vérifier bien ce point )
Autre point. Dans le Statusbar je met le nom de fichier en cours de traitement. Lors de votre "bug" que dit ce Statusbar ? Ce fichier en cours serait il trop volumineux, nom de fichier trop long, fichier déjà ouvert .... ?
Pouvez vous me dire combien de fichiers vous essayez de consolider et combien en moyenne chaque fichier à de lignes ?
 
Vote positif 0 Vote négatif
V

vivi21

XLDnaute Occasionnel
sylvanu à dit:
Bonjour,
Je viens de retester avec 50 fichiers de 1000 lignes, et c'est passé sans problème.
( J'ai eu une erreur mais c'est parce qu'un nom de fichier avait plus de 31 caractères à cause de duplication de fichiers pour le test , genre
Sophie - Copie - Copie - Copie - Copie.xlsx, vérifier bien ce point )
Autre point. Dans le Statusbar je met le nom de fichier en cours de traitement. Lors de votre "bug" que dit ce Statusbar ? Ce fichier en cours serait il trop volumineux, nom de fichier trop long, fichier déjà ouvert .... ?
Pouvez vous me dire combien de fichiers vous essayez de consolider et combien en moyenne chaque fichier à de lignes ?
Cliquez pour agrandir...
Bonjour Sylvanu,
J'ai retesté avec 2 fichiers, j'avais un souci de nom de feuille. Le traitement est long par fichier mais cela fonctionne. J'active la macro pas à pas pour voir s'il n'y a pas de bug et voir où j'en suis dans les étapes.
En fait, j'ai 450 lignes par fichier et j'ai une quinzaine de fichiers donc vu votre essai (et je vous en remercie) cela devrait fonctionner à la perfection.
Dans mes fichiers initiaux, j'ai juste la feuille consolidation qui est masquée. Pour l'instant, je l'affiche pour être certains que cela fonctionne.
Bonne journée et encore merci pour votre aide.
 
Vote positif 0 Vote négatif
Hasco

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Vous avez excel 2016, pourquoi ne pas utiliser Power query, créé pour ce genre de choses.
Données/Obtenir des données/A partir d'un fichier/A partir d'un dossier.

Eventuellement préparez un .zip de deux ou trois fichiers exemple anonymisés.

Cordialement
 
Vote positif 0 Vote négatif
M

Michel_T

XLDnaute Nouveau
Excel recycle très mal la mémoire : si vous ouvrez puis fermez plusieurs fichiers, il est de plus en plus lent, et le gestionnaire des tâches montre qu'il ne libère pas la mémoire.
Vous gagnerez en rapidité et en stabilité en créant une nouvelle instance de l'application Excel dans la boucle pour ouvrir le fichier (et en la détruisant dans la boucle aussi).

Mais j'abonde : Excel 2016 intègre Power Query (les requêtes), et la source Dossier est bien plus souple et bien plus rapide que le VBA pour récupérer des données dans un ensemble de fichiers.
 
Vote positif 0 Vote négatif

Discussions similaires

N
  • Résolu(e)
XL 2019 Joindre des données de plusieurs feuilles
Réponses
4
Affichages
561
Nasseuz
N
Z
  • Question
Microsoft 365 Segment commun avec plusieurs onglets source de données
Réponses
12
Affichages
554
Zazagally
Z
S
  • Question
XL 2016 compter une valeur spécifique avec plusieurs critères
Réponses
6
Affichages
513
Cousinhub
Cousinhub
vgendron
  • Question
Power Query [Resolu] Importer plusieurs fichiers excel provenant de dossiers différents sur sharepoint
Réponses
6
Affichages
1 K
Staple1600
Staple1600
B
  • Question
XL 2013 Transposition de filtres multiples sur plusieurs feuilles
Réponses
11
Affichages
627
BIROULIG
B
P
  • Question
XL 2019 Mise en forme conditionnelle sur plusieurs feuilles
Réponses
6
Affichages
708
JHA
J
B
  • Question
XL 2019 ventiler une base de données sur plusieurs feuilles d'un même classeur
Réponses
5
Affichages
591
bernardhericher
B
D
  • Question
Microsoft 365 combiner deux plages de données sur deux feuilles excel différentes pour un TCD
Réponses
4
Affichages
454
Daniel200666
D
V
  • Question
XL 2016 Convertir en tableau
Réponses
6
Affichages
664
cp4
cp4
H
  • Question
Autres mise a jour automatique feuille excel vba
Réponses
2
Affichages
530
HANN
H

Membres actuellement en ligne

10 membres et 90 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
315 090
Messages
2 116 104
Membres
112 661
dernier inscrit
ceucri