XL 2016 Combiner plusieurs feuilles de plusieurs fichiers

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
 

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

vivi21

XLDnaute Occasionnel
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é) ?
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

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

vivi21

XLDnaute Occasionnel
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
Merci, je vais regarder si cela fonctionne de mon côté
 

vivi21

XLDnaute Occasionnel
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
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.
 

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

vivi21

XLDnaute Occasionnel
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
Je vais retenter merci pour votre rapidité de réponse
 

vivi21

XLDnaute Occasionnel
Je vais retenter merci pour votre rapidité de réponse
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
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.
 

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 ?
 

vivi21

XLDnaute Occasionnel
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 ?
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.
 

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
 

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.
 

Discussions similaires