XL 2016 Combiner plusieurs feuilles de plusieurs fichiers

vivi21 · 7 Juin 2022

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 · 7 Juin 2022

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 · 7 Juin 2022

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

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

sylvanu · 7 Juin 2022

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

vivi21 · 7 Juin 2022

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

Merci, je vais regarder si cela fonctionne de mon côté

vivi21 · 30 Juin 2022

Bonjour

vivi21 à dit:
Merci, je vais regarder si cela fonctionne de mon côté

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.

vivi21 · 30 Juin 2022

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

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 · 30 Juin 2022

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.

vivi21 · 30 Juin 2022

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

Je vais retenter merci pour votre rapidité de réponse

vivi21 · 30 Juin 2022

vivi21 à dit:
Je vais retenter merci pour votre rapidité de réponse

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

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 · 30 Juin 2022

Bonsoir,
J'ai essayé avec 4 fichiers de 450 lignes. Ca marche chez moi.
Par contre combien de fichiers avez vous, c'est peut être que le problème est là.

sylvanu · 1 Juillet 2022

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 · 1 Juillet 2022

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 ?

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 · 1 Juillet 2022

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 · 14 Juillet 2022

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.

XL 2016 Combiner plusieurs feuilles de plusieurs fichiers

vivi21

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

vivi21

XLDnaute Occasionnel

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

vivi21

XLDnaute Occasionnel

vivi21

XLDnaute Occasionnel

vivi21

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Pièces jointes

vivi21

XLDnaute Occasionnel

vivi21

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

vivi21

XLDnaute Occasionnel

Hasco

XLDnaute Barbatruc

Michel_T

XLDnaute Nouveau

Discussions similaires