Boucle ouvrir plusieurs fichiers Excel d'un dossier

Le 22/01/2021 à 11:49

Bonjour à tous,

J'ai un dossier avec plusieurs fichiers Excel.

Dans ces fichiers, j'ai toujours 6 onglets. Le nom de ces onglets commence toujours de la même façon "1+nom différent", "2+nom différent ", "3+ nom différent", "4+nom différent", "5+nom différent", "6+nom différent".

J'ai créé à côté de cela 6 fichiers nommés "Compilation 1", "Compilation 2", "Compilation 3"....

Je souhaite faire une macro à partir de mon fichier "Compilation 1". A l'aide d'une boucle, Excel devra copier-coller dans le fichier "Compilation 1" l'ensemble des onglets qui commence par "1" dans les fichiers de mon dossier.

J'e suis débutant (excusez moi d'avance) mais j'essaie de comprendre comment cela fonctionne. Ci-dessous la macro mais je suis bloqué à une étape. Comment lui dire d'ouvrir le fichier de mon dossier ?

Merci

Sub test()

Dim dossier As Object
Dim chemin_dossier As String
Dim fichier As Object

Dim i As Integer
Dim DerniereLigne As Integer

Dim Lastrowconsolidation As Integer

'identifier chemin du dossier
chemin_dossier = "C:\Users\p\Desktop\Learning\Fichiers"

'Créer un objet
Set Fso = CreateObject("Scripting.FileSystemObject")

'Définir cet objet comme un dossier
Set dossier = Fso.getfolder(chemin_dossier)

'boucle sur les fichiers du dossier
For Each fichier In dossier.Files


'Ouvrir le fichier excel avec les data PROBLEME ICI
Workbooks.Open

'Sélectionner les data à copier
Sheets(1).Select
DerniereLigne = Range("A100000").End(xlUp).Row
For i = 2 To DerniereLigne
Sheets(1).Select
Rows(i).Select
Selection.Copy

'Coller les données dans le fichier compilation

Workbooks("Compilation 1").Select
Sheet1.Activate
Lastrowconsolidation = Range("A100000").End(xlUp).Row + 1
Cells(Lastrowconsolidation, 1).Select
ActiveSheet.Paste
Application.cutecopymode.false

Macro pas terminée....

Next i

Workbooks.Close

Next

End Sub

Le 22/01/2021 à 12:38

Bonjour,

Voici un essai de code si j'ai bien compris.

On colle les valeurs des fichiers à sonder (contenant chacun 6 onglets) dans le fichier Compilation correspondant (dont le nom termine par l'index de l'onglet du fichier à sonder), qui ne contient qu'un seul onglet accueillant les infos.

Ici, il faut que :

- le code soit exécuté à partir d'un nouveau classeur !!!

- les fichiers compilation soient réunis dans un même dossier, différent de celui des fichiers à sonder. Il faudra adapter le chemin.

- tous les fichiers au départ soient fermés, à l'exception du classeur exécutant qui est indépendant.

Sub test()

dim wbsonde as workbook, wbdest as workbook
dim ws as worksheet
dim dossiersonde$, dossiercompil$, filecompil$, filesonde$, num$
dim dl&, nvl&

dossiersonde = "C:\Users\p\Desktop\Learning\Fichiers\" 'dossier à sonder (fichiers avec 6 onglets)
dossiercompil = "C:\Users\p\Desktop\Learning\Fichiers\Compil\" 'dossier des fichiers compilation <<<< ADAPTER

application.screenupdating = false

'ouverture de tous les fichiers compilation
filecompil = dir(dossiercompil & "Compil*.xls*")
while filecompil <> "" 'tant qu'il existe des fichiers compilation dans le dossier
    workbooks.open dossiercompil & filecompil 'on ouvre le fichier
    filecompil = dir
wend

'ouverture d'un fichier à sonder à la fois
filesonde = dir(dossiersonde & "*.xls*")
while filesonde <> "" 'tant qu'il existe des fichiers dans le dossier à sonder
    set wbsonde = Workbooks.Open(dossiersonde & filesonde) 'on ouvre le fichier
    with wbsonde 'avec ce fichier
        for each ws in .worksheets 'pour chaque feuille
            num = cstr(ws.index) 'num vaut numéro d'index
            dl = ws.cells(ws.rows.count, 1).end(xlup).row 'derniere ligne non vide
            set wbdest = workbooks("Compilation " & num) 'fichier compilation correspondant à l'onglet en cours
            with wbdest.sheets(1) 'sur la feuille 1 du fichier compilation visé
                nvl = .cells(.rows.count, 1).end(xlup).row + 1 'nouvelle ligne (1ere non vide)
                .range(nvl & ":" & nvl + dl - 2).value = ws.range("2:" & dl).value 'les lignes de compil prennent valeurs de la feuille ws de sondé
            end with
        next ws 'prochaine feuille
        .close true 'fermeture et sauvegarde du fichier sondé
    end with
    filesonde = dir 'on passe au suivant
wend

'for each wb in workbooks 'pour chaque classeur de la collection
'    if wb.name like 'Compil*" then wb.close true 'si fichier compil, on ferme et sauve
'next wb

application.screenupdating = true

msgbox "terminé"

End Sub

Ce n'est pas testé donc les erreurs sont probables !

Cdlt,

Le 22/01/2021 à 15:04

Merci beaucoup pour votre réponse. C'est très intéressant de voir comment vous l'avez construit !

J'ai effectivement une erreur. Je ne comprends pas pourquoi il n’accepte pas la ligne ci-dessous. Pourtant dans mon dossier mes fichiers se nomment bien Compilation 1, Compilation 2...

Set wbdest = Workbooks("Compilation " & num)

Le 22/01/2021 à 15:14

Ah mince, moi qui espérais que ça passe du premier coup .

Est-ce que vous pourriez modifier la ligne ainsi :

Set wbdest = Workbooks("Compilation " & num & ".xlsx") 'ou .xlsm << enfin avec l'extension

Remarque : pour poster du code, vous pouvez vous aider de l'icone </> sur le ruban de commentaire.

Pouvez-vous également m'indiquer la valeur de num ? Lors du débogage, dans la fenêtre variables locales apparaissent toutes les variables ainsi que leur valeur.

Je n'ai déclaré aucune variable par souci de rapidité. Parfois, c'est bloquant dans le code. Je vous conseille au moins de déclarer la variable num.

En cas d'erreur, pouvez-vous me donner le message également ? Je vais éditer le code en attendant.

Merci,

Le 22/01/2021 à 15:34

Le code d'erreur est "erreur d'éxécution '9. L'indice n'appartient pas à la sélection."

Dans la variable locale, j'ai bien 1 qui apparaît sur la ligne num. Je viens de faire la modification avec le nouveau code mais j'ai toujours le même messaeg d'erreur. Mes fichiers excel sont bien en .xlsx.

Merci pour l'astuce du code. je ne connaissais pas.

Le 22/01/2021 à 15:38

Ca signifie que le classeur n'est pas trouvé. Pourtant, il est censé être ouvert par la première boucle while.

Alors, soit il faut bien vérifier les noms des classeurs Compilation et ajuster le code, soit, la première boucle est un échec, elle n'a trouvé aucun fichier compilation et donc ceux-ci ne seraient pas ouverts au moment du bug (je pense que c'est plutôt ça !).

Dans ce cas, il faut bien :
- définir le bon chemin dans la variable dossiercompil,
- regrouper tous ces fichiers Compilation dans ce dossier.

Cdlt,

Le 22/01/2021 à 15:56

Oui effectivement cela fonctionne !

Un grand merci à vous 3GB !!

Le 22/01/2021 à 16:00

Super ! Merci pour ce retour !

Bonne continuation à vous,

Cdlt,

Le 22/01/2021 à 17:14

Dernière question, je pensais réussir à le faire mais non... . est-ce possible faire juste un copier-coller des onglets dans les fichiers "Compilation 1", "Compilation 2"... et ne pas faire de compilation sur un même onglet ?

      For Each ws In .Worksheets 'pour chaque feuille
            num = CStr(ws.Index) 'num vaut numéro d'index
            dl = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'dernière ligne non vide
            Set wbdest = Workbooks("Compilation " & num) 'fichier compilation correspondant à l'onglet en cours
            With wbdest.Sheets(1) 'sur la feuille 1 du fichier compilation visé
                nvl = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 'nouvelle ligne (1ere non vide)
                .Range(nvl & ":" & nvl + dl - 2).Value = ws.Range("2:" & dl).Value 'les lignes de compil prennent valeurs de la feuille ws de sondé
            End With
Le 22/01/2021 à 17:26

"Tout", enfin presque, est possible. Il faut bien définir le besoin en amont. Ici, j'ai repris les valeurs parce que c'est plus rapide. On peut copier-coller les cellules à la suite (ou en écrasant), on peut copier des onglets, ...

Le 22/01/2021 à 18:50

Effectivement. A la place de consolider les données sur un seul onglet, il faudrait copier coller les onglets en intégralité dans les fichiers "Compilation".

Il y a aura donc l'ensemble des onglets "1" dans le fichier "Compilation 1", les onglets "2" dans "Compilation 2"...

Je n'arrive pas à jongler entre les deux Workbooks...

Le 22/01/2021 à 19:32

Voici un essai en copiant les feuilles dans les classeurs Compilation. C'est même plus simple selon moi :

Sub test()

dim wbsonde as workbook, wbdest as workbook
dim ws as worksheet
dim dossiersonde$, dossiercompil$, filecompil$, filesonde$, num$
dim dl&, nvl&

dossiersonde = "C:\Users\p\Desktop\Learning\Fichiers\" 'dossier à sonder (fichiers avec 6 onglets)
dossiercompil = "C:\Users\p\Desktop\Learning\Fichiers\Compil\" 'dossier des fichiers compilation <<<< ADAPTER

application.screenupdating = false

'ouverture de tous les fichiers compilation
filecompil = dir(dossiercompil & "Compil*.xls*")
while filecompil <> "" 'tant qu'il existe des fichiers compilation dans le dossier
    workbooks.open dossiercompil & filecompil 'on ouvre le fichier
    filecompil = dir
wend

'ouverture d'un fichier à sonder à la fois
filesonde = dir(dossiersonde & "*.xls*")
while filesonde <> "" 'tant qu'il existe des fichiers dans le dossier à sonder
    'n = n + 1 'incrementation compteur fichiers
    set wbsonde = Workbooks.Open(dossiersonde & filesonde) 'on ouvre le fichier
    with wbsonde 'avec ce fichier
        for each ws in .worksheets 'pour chaque feuille
            num = cstr(ws.index) 'num vaut numéro d'index
            set wbdest = workbooks("Compilation " & num) 'fichier compilation correspondant à l'onglet en cours
            ws.copy after:=wbdest.sheets(wbdest.sheets.count)
            'wbdest.sheets(wbdest.sheets.count).name = format(Date, "YYMMDD") & " fichier" & n '<<< pour renommer !
        next ws 'prochaine feuille
        .close true 'fermeture et sauvegarde du fichier sondé
    end with
    filesonde = dir 'on passe au suivant
wend

'for each wb in workbooks 'pour chaque classeur de la collection
'    if wb.name like 'Compil*" then wb.close true 'si fichier compil, on ferme et sauve
'next wb

application.screenupdating = true

msgbox "terminé"

End Sub

J'ai mis 2 lignes en commentaires dans la seconde boucle while qui permettraient de renommer directement ces feuilles en fonction de la date du jour.

Cdlt,

Le 25/01/2021 à 10:51

Merci beaucoup 3GB. C'est parfait !

Rechercher des sujets similaires à "boucle ouvrir fichiers dossier"