XL 2016 CONSOLIDER plusieurs fichiers excel dans un seul

[Bauer95]

Bonjour,

Besoin d'une petite aide !

Je souhaite consolider les données de plusieurs fichiers excel 1 à 30 en fonction des mois.
Les fichiers ont le même nombre de colonne ainsi que les mêmes intitulés de titre par colonne.
Il y'a que le nombre de ligne qui peut varier d'un fichier à un autre en fonction des données.

Dans la mesure du possible je souhaite avoir une macro dans un fichier conso qui me récupérera les données de tous les fichiers et les coller les uns suivant les autres.

Les fichiers à consolider et le ficher de récap peuvent être dans un dossier précis pour que la macro s’exécute sans ouvrir nécessairement les fichiers.

Je vous joint 2 fichiers exemples à compiler en un.


Merci par avance.

 

[chris]

Bonjour

Avec Excel 2016, pas besoin de coder : une requête PowerQuery consolide en quelques clics : Nouvelle Requête à partir d'un dossier...

J'ai juste ajouté le chemin au choix : tu changes puis Données, Actualiser Tout

 

[job75]

Bonjour Bauer95, chris,

Sinon il y a de nombreux exemples de solutions VBA sur le forum, voyez le fichier .xlsm joint et cette macro :

Sub MAJ()
Dim chemin$, fichier$, feuille$, ncol%, lig&, f$, derlig As Variant, ad$
chemin = ThisWorkbook.Path & "\" 'à adapter événtuellement
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
feuille = "Feuil1" 'nom de la feuille à copier dans les fichiers
Application.ScreenUpdating = False
With Feuil1 'CodeName de la feuille des résultats, à adapter
    .Rows("2:" & Rows.Count).Delete xlUp 'RAZ
    ncol = .[A1].CurrentRegion.Columns.Count
    lig = 2
    While fichier <> ""
        f = "'" & chemin & "[" & fichier & "]" & feuille & "'!"
        derlig = ExecuteExcel4Macro("MATCH(9^99," & f & "R1C1:R1048576C1)")
        If IsNumeric(derlig) Then
            If derlig > 1 Then
                ad = .Cells(2, 1).Resize(derlig - 1, ncol).Address
                With .Cells(lig, 1).Resize(derlig - 1, ncol)
                    .FormulaArray = "=" & f & ad 'formule de liaison matricielle
                    .Value = .Value 'supprime la formule
                End With
                lig = lig + derlig - 1
            End If
        End If
        fichier = Dir 'fichier suivant
    Wend
    .UsedRange.Replace 0, "", xlWhole 'supprime les zéros
End With
End Sub

L'utilisation des formules de liaison évite d'ouvrir les fichiers.

A+

 

[Bauer95]

Bonjour Chris,

Merci pour ta solution.

Cependant, je n'arrive pas à modifier le chemin que tu as crée et quand j'essaye de créer une nouvelle requête comme tu l'a dit en quelques clics, j'ai un espace de ligne vide qui se crée entre la fusion des données des différents fichier.

Ci-joint mon essai.

AU pire tu peux m'indiquer comment tu as créer le chemin.

Merci d'avance

 

[Bauer95]

Merci Job75 pour ta solution via les codes.
Elle fonctionne bien.

C'est juste cette partie que j'ai pas bien saisie : Workbook.Path à adapter éventuellement,
Pourras-tu m'expliquer ça en quelques mots?

Merci d'avance.

 

[job75]

ThisWorkbook.Path & "\" c'est le chemin d'accès (dossier) du fichier de la macro où doivent se trouver les fichiers à copier.

Mais si ces fichiers se trouvent dans un sous-dossier nommé "Fichiers à copier" on écrira :

chemin = ThisWorkbook.Path & "\Fichiers à copier\"

 

[chris]

RE

Au temps pour moi, j'ai fait vite avant de partir pour un RV et n'ai intégré le chemin qu'à une endroit au lieu de deux.

Tes fichiers sources contiennent des choses sur les lignes vides (formats ou caractères parasites) qui du coup ne sont pas considérées comme vides

J'ai donc filtré les dates d'entrée vides dans l'exemple que j'ai transmis (dans la requête, on déroule en haut de la colonne comme dans un filtre Excel)

 

[Bauer95]

Bonjour Chris,
Merci pour ton retour.

Je ne suis pas trop doué avec ces requêtes, peux-tu m'indiquer en quelques lignes comment changer le chemin au choix pour qu'en faisant Données, Actualiser Tout le fichier se met à jour.

Est ce qu'en rajoutant d'autres fichiers du même modèle dans le dossier l’actualisation prendra en compte tous les nouveaux fichiers?

Merci par avance,

 

[chris]

bonjour

Dans Excel, la cellule qui contient le chemin est nommée Dossier

Quand dans PowerQuery on utilise la commande Nouvelle Requête à partir d'un dossier, PowerQuery crée pas mal de choses (on peut simplifier par la suite, quand on connait) et notamment
Exemple de fichier et la requête portant le nom de dossier qui toutes deux contiennent le chemin

On peut éditer ces deux requêtes dans l'éditeur avancé (onglet Accueil) et remplacer la ligne Source=....
par ces deux lignes

    Dossier=Table.FirstValue(Excel.CurrentWorkbook(){[Name="Dossier"]}[Content]),
    Source = Folder.Files(Dossier),

Qui définissent la variable Dossier à partir de la cellule nommée du même nom et l'utilisent pour rédéfinir la source des requêtes