Consolider plusieurs fichiers Excel d'un dossier dans un seul classeur (macro VBA)

Douze fichiers mensuels, un par agence ou par commercial… et un tableau de synthèse à refaire chaque fois par copier-coller. Cette macro VBA automatise l'opération : elle ouvre chaque classeur d'un dossier, en recopie les données dans un seul onglet et ajoute une colonne indiquant le fichier d'origine.

Ce que fait la macro

Elle vous demande de choisir un dossier, puis ouvre en lecture seule chaque fichier .xlsx qu'il contient. Elle prend la première feuille de chaque classeur, recopie les lignes situées sous l'en-tête et ajoute en colonne A le nom du fichier source. Le résultat arrive dans l'onglet Consolidation du classeur qui contient la macro ; les fichiers sources ne sont jamais modifiés.

Prérequis

• Excel pour Windows. Le code utilise l'explorateur de dossiers d'Excel et la fonction Dir ; l'accès aux dossiers fonctionne différemment sous Mac, où la macro risque de ne pas fonctionner.
• Des fichiers .xlsx construits sur le même modèle : données dans la première feuille, en-têtes en première ligne, mêmes colonnes dans le même ordre.
• Un classeur pour héberger la macro, enregistré au format .xlsm (le format Excel qui accepte les macros).
• L'onglet Développeur affiché : Fichier > Options > Personnaliser le ruban, puis cocher « Développeur ».

Le code

Ouvrez l'éditeur VBA avec Alt + F11, insérez un module (Insertion > Module) et collez le code ci-dessous. Il est commenté étape par étape.

Option Explicit

' Consolide tous les classeurs .xlsx d'un dossier dans l'onglet "Consolidation"
' du classeur qui contient cette macro.
' Hypothèse : dans chaque fichier, la PREMIÈRE feuille contient un tableau
' dont la première ligne est l'en-tête, avec les mêmes colonnes d'un fichier à l'autre.
Sub ConsoliderClasseursDuDossier()
    Dim dossier As String
    Dim nomFichier As String
    Dim classeurSource As Workbook
    Dim feuilleSource As Worksheet
    Dim feuilleCible As Worksheet
    Dim plage As Range
    Dim donnees As Range
    Dim ligneCible As Long
    Dim nbLignes As Long
    Dim nbColonnes As Long
    Dim nbColonnesReference As Long
    Dim nbFichiers As Long
    Dim enteteEcrit As Boolean
    Dim fichiersIgnores As String
    Dim messageErreur As String
    Dim message As String

    ' 1) Choix du dossier
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choisissez le dossier contenant les classeurs à consolider"
        If .Show <> -1 Then Exit Sub   ' l'utilisateur a annulé
        dossier = .SelectedItems(1)
    End With
    If Right$(dossier, 1) <> Application.PathSeparator Then
        dossier = dossier & Application.PathSeparator
    End If

    ' 2) Préparation de l'onglet de destination
    On Error Resume Next
    Set feuilleCible = ThisWorkbook.Worksheets("Consolidation")
    On Error GoTo 0
    If feuilleCible Is Nothing Then
        Set feuilleCible = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        feuilleCible.Name = "Consolidation"
    Else
        If MsgBox("L'onglet Consolidation existe déjà : son contenu sera remplacé. Continuer ?", _
                  vbYesNo + vbQuestion) <> vbYes Then Exit Sub
        feuilleCible.Cells.Clear
    End If

    ' 3) Boucle sur les fichiers du dossier
    Application.ScreenUpdating = False
    On Error GoTo Nettoyage

    nomFichier = Dir(dossier & "*.xlsx")
    Do While nomFichier <> ""
        ' On ignore les fichiers temporaires "~$..." que crée Excel quand un fichier est ouvert
        If Left$(nomFichier, 2) <> "~$" Then
            Set classeurSource = Workbooks.Open(Filename:=dossier & nomFichier, ReadOnly:=True, UpdateLinks:=0)
            Set feuilleSource = classeurSource.Worksheets(1)

            If Application.WorksheetFunction.CountA(feuilleSource.Cells) > 0 Then
                Set plage = feuilleSource.UsedRange
                nbColonnes = plage.Columns.Count

                If Not enteteEcrit Then
                    ' Premier fichier : on écrit l'en-tête, précédé de la colonne "Fichier source"
                    feuilleCible.Cells(1, 1).Value = "Fichier source"
                    feuilleCible.Cells(1, 2).Resize(1, nbColonnes).Value2 = plage.Rows(1).Value2
                    nbColonnesReference = nbColonnes
                    enteteEcrit = True
                    ligneCible = 2
                End If

                If nbColonnes <> nbColonnesReference Then
                    ' Structure différente du premier fichier : on le signale au lieu de le mélanger
                    fichiersIgnores = fichiersIgnores & vbCrLf & nomFichier
                Else
                    ' Lignes de données = plage utilisée moins sa première ligne (l'en-tête)
                    nbLignes = plage.Rows.Count - 1
                    If nbLignes > 0 Then
                        Set donnees = plage.Offset(1, 0).Resize(nbLignes, nbColonnes)
                        feuilleCible.Cells(ligneCible, 2).Resize(nbLignes, nbColonnes).Value2 = donnees.Value2
                        feuilleCible.Cells(ligneCible, 1).Resize(nbLignes, 1).Value = nomFichier
                        ligneCible = ligneCible + nbLignes
                    End If
                    nbFichiers = nbFichiers + 1
                End If
            End If

            classeurSource.Close SaveChanges:=False
            Set classeurSource = Nothing
        End If
        nomFichier = Dir   ' fichier suivant (ne pas appeler Dir ailleurs dans la boucle)
    Loop

    feuilleCible.Columns.AutoFit

Nettoyage:
    ' On passe ici en fin normale comme en cas d'erreur
    If Err.Number <> 0 Then messageErreur = Err.Description
    On Error Resume Next
    If Not classeurSource Is Nothing Then classeurSource.Close SaveChanges:=False
    Application.ScreenUpdating = True
    On Error GoTo 0

    If messageErreur <> "" Then
        MsgBox "La consolidation s'est arrêtée sur une erreur : " & messageErreur, vbExclamation
    ElseIf nbFichiers = 0 Then
        MsgBox "Aucun fichier .xlsx exploitable trouvé dans ce dossier.", vbInformation
    Else
        message = nbFichiers & " fichier(s) consolidé(s), " & (ligneCible - 2) & " ligne(s) de données."
        If fichiersIgnores <> "" Then
            message = message & vbCrLf & vbCrLf & "Ignorés (colonnes différentes du premier fichier) :" & fichiersIgnores
        End If
        MsgBox message, vbInformation
    End If
End Sub

Ce que fait chaque partie

• Choix du dossier. L'explorateur d'Excel évite d'écrire un chemin en dur ; annuler la fenêtre arrête la macro sans rien changer.
• Onglet de destination. Créé au besoin ; s'il existe déjà, la macro demande confirmation avant d'en effacer le contenu.
• Boucle Dir. Elle renvoie les fichiers un à un : ne l'appelez pas ailleurs dans la boucle, sous peine de perdre la position. Les fichiers temporaires ~$…, créés par Excel pour tout fichier ouvert, sont ignorés.
• Copie des valeurs. Elles passent directement d'une plage à l'autre, sans presse-papiers : c'est plus rapide, mais les formules et la mise en forme ne sont pas reprises, seulement les résultats.
• Colonnes et nettoyage. Un fichier dont le nombre de colonnes diffère du premier est listé dans le message final au lieu d'être mélangé aux autres. Que tout se passe bien ou non, le classeur resté ouvert est refermé sans enregistrer et l'affichage est rétabli.

Utiliser la macro pas à pas

1. Faites d'abord un essai sur une copie du dossier, avec deux ou trois fichiers : la macro ne modifie pas les sources, mais c'est le bon réflexe avant de l'utiliser sur des données réelles.
2. Enregistrez votre classeur au format .xlsm, collez le code dans un module, puis, dans l'onglet Développeur, cliquez sur Macros, sélectionnez ConsoliderClasseursDuDossier et lancez-la.
3. Choisissez le dossier. Un message indique le nombre de fichiers et de lignes consolidés, et signale les fichiers ignorés.
4. Contrôlez l'onglet Consolidation : le nombre de lignes doit être égal à la somme de celles de vos fichiers, en-têtes exclus.

Erreurs fréquentes

• « Aucun fichier .xlsx exploitable » : le dossier ne contient que d'anciens fichiers .xls ou des classeurs vides. La macro ne traite que les .xlsx ; pour d'autres formats, il faut adapter le filtre de Dir.
• Des fichiers « ignorés » : leurs colonnes ne correspondent pas à celles du premier fichier lu (colonne ajoutée, ligne de titre au-dessus du tableau). Harmonisez les modèles ou traitez-les à part.
• La macro ne se lance pas : Excel bloque les macros d'un fichier reçu par Internet ou par messagerie. Faites un clic droit sur le fichier, ouvrez ses Propriétés et cochez « Débloquer », ou cliquez sur « Activer le contenu » dans la barre d'avertissement.
• Erreur à l'ouverture d'un fichier : un fichier protégé par mot de passe ou endommagé peut interrompre la macro, qui affiche alors l'erreur d'Excel et referme proprement le classeur ouvert. Retirez ce fichier et relancez.
• Des dates ou des nombres qui changent d'aspect : les valeurs sont recopiées, pas les formats. Reformatez les colonnes de l'onglet Consolidation.

Pour aller plus loin

Une fois les données au même endroit, on veut souvent les comparer ou les exporter. Nos deux autres tutoriels s'enchaînent avec celui-ci : une macro pour comparer deux onglets et repérer les écarts, et une macro pour exporter une feuille Excel en fichier PDF.

Quand faire appel à un développeur ?

Cette macro fonctionne tant que vos fichiers respectent le même modèle. Dès que les modèles varient, ou qu'il faut nettoyer, dédoublonner, contrôler ou calculer pendant la consolidation, le code devient vite plus long que celui présenté ici, et plus difficile à maintenir. Une macro est aussi liée à un poste de travail et à une version d'Excel : quand la personne qui l'a écrite part, elle devient une boîte noire.

Choisir entre une macro, un script ou une petite application, c'est justement ce que nous cadrons avec vous. Nous développons des macros Excel et applications VBA sur mesure, et nous vous dirons aussi honnêtement quand un autre outil est plus adapté. Vue d'ensemble : l'automatisation sur mesure.