Microsoft 365 Combinaison de plus de 200 classeurs dont la structure est identique

[Goffy]

Bonjour à toutes et tous,

Je cherche à regrouper mes 250 classeurs (1 classeur = 1 feuille) en un seul classeur. La structure est la même.

J'ai essayé via Power Query, mais il semblerait que je puisse combiner les jeux de données un par un seulement. Je n'ai pas trouvé comment pour combiner les 250 classeurs d'un seul coup.

J'ai également fouillé le forum dans lequel des solutions via VBA sont proposées, mais je ne m'y connais pas assez pour comprendre les codes...

Je vous joins deux exemples de classeurs en ma possession.

Je vous remercie par avance pour votre aide.

Bien à vous

 

[Staple1600]

Bonjour @Goffy, le fil

@Goffy
Utilise PowerQuery
Données/Obtenir les données/A partir du fichier/A partir du dossier

Voir tuto ici par exemple

 

[job75]

Bonsoir Goffy, JM,

Dans les 2 fichiers sources .xlsx j'ai converti les dates qui étaient des textes en vraies dates (nombres).

Ensuite j'ai créé le fichier pilote Grouper.xlsm avec cette macro :

Sub Grouper()
'se lance par le raccourci clavier Ctrl+G
Dim ncol%, chemin$, fichier$, feuille$, lig&, form$, h&
ncol = 16 'nombre de colonnes à copier
chemin = ThisWorkbook.Path & "\" 'dossier des classeurs
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
feuille = "Resultats"
lig = 2
Application.ScreenUpdating = False
Rows(lig & ":" & Rows.Count).ClearContents 'RAZ
While fichier <> ""
    form = "'" & chemin & "[" & fichier & "]" & feuille & "'!"
    h = ExecuteExcel4Macro("MATCH(""zzz""," & form & "C1)")
    If h > 1 Then
        With Cells(lig, 1).Resize(h - 1, ncol)
            .FormulaArray = "=" & form & "R2C1:R" & h & "C" & ncol
            .Value = .Value 'supprime les formules
        End With
        lig = lig + h - 1
    End If
    fichier = Dir 'fichier suivant
Wend
ActiveSheet.UsedRange.Replace 0, "", xlWhole 'efface les valeurs zéro
End Sub

Téléchargez les fichiers dans le même dossier et lancez la macro.

On notera que la macro n'ouvre pas les fichiers sources.

A+

 

[job75]

Une chose avant d'aller dormir.

Les dates des fichiers sources étaient des textes parce qu'il s'agit de données importées.

C'est au niveau de la macro d'importation qu'il faut agir pour transformer les textes des dates en nombres.

Bonne nuit.

 

[R@chid]

Bonjour @ tous,
Avec une fonction Power Query.
Ouvre le fichier ci-joint et suis les étapes sur la vidéo pour modifier la source des fichiers.
https://www.cjoint.com/c/MFex0QVZmzQ

Bien cordialement

 

[job75]

Bonjour R@chid, le forum,

Pour tester j'ai dupliqué 125 fois les 2 fichiers sources (250 fichiers).

Chez moi la macro du post #3 s'exécute en 12 secondes.

A+

 

[R@chid]

Bonjour @ tous,
Salut l'ami @job75
J'ai fait la même chose que toi, j'ai dupliqué 125 fois les deux fichiers de Goffy pour en fin obtenir 250 fichiers.
La requête Power Query est deux fois plus rapide que la macro, elle charge les 28000 lignes en à peine 6 secondes.

Excel pour Microsoft 365 / Windows 10 Pro
Core i5, 8Go de Ram 512Go Nvme.

Cordialement

 

[Cousinhub]

Bonjour,
Avec ce code PQ :

let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="CheminDossier"]}[Content]{0}[Column1]),
    Filtr = Table.SelectRows(Source, each Text.StartsWith([Name], "resul")),
    Transform = Table.TransformColumns(Filtr, { "Content", each Excel.Workbook(_, true )}),
    Expand1 = Table.ExpandTableColumn(Transform, "Content", {"Data", "Kind"}, {"Data", "Kind"}),
    Selections = Table.SelectRows(Expand1, each ([Kind] <> "DefinedName") and Text.Contains([Extension], "xls")) [[Name],[Data]],
    Expand2 = Table.ExpandTableColumn(Selections, "Data", Table.ColumnNames(Selections[Data]{0}), Table.ColumnNames(Selections[Data]{0})),
    ModType = Table.TransformColumnTypes(Expand2,{{"Date Compréhension Écrite", type date}, {"Date Compréhension Orale", type date}, {"Date Grammaire et Lexique", type date}})
in
    ModType

Les fichiers sont dans le répertoire indiqué dans la cellule nommé "CheminDossier"
250 fichiers, 28 770 lignes chargées, Modification du format des différentes colonnes "Date...."
Première connexion : 6,28 secondes
Actualisation une fois cette première connexion établie : 2,19 secondes
Bonne apm

 

[job75]

La comparaison des durées d'exécution est toujours instructive.

 

[R@chid]

La comparaison des durées d'exécution est toujours instructive.

Oui bien sûr, elle est très importante.

 

[R@chid]

J'ai refait un autre essai,
Première exécution de ma requête : 4,76s
Actualisation de la requête : 2,88s
28000 lignes après la suppression des lignes vides.

Cordialement

 

[Goffy]

Bonsoir,

Je tenais à vous remercier pour les différentes solutions proposées, qui ont toutes deux fonctionné.

Navré encore pour le délai de retour.

Bien à vous,
Goffy