Regroupement de fichiers excel + ajout d'une colonne référencant la source

[Jex94]

Bonjour,

J'ai 500 fichiers excel avec des données homogènes (4 colonnes identiques mais nombre de lignes variable d'un fichier à l'autre) et je souhaiterais pouvoir regrouper ces 500 fichier sur une seule feuille excel, non par fusion mais par regroupement de type copié collé, les uns en dessous des autres sans rupture, et en insérant une colonne afin de garder la référence source de chaque ligne, cette référence correspondant au nom du fichier source.

Pour illustrer ma demande, je mets en pièces jointes pour test 2 fichiers à regrouper, et le résultat attendu sur le 3ème fichier.

Connaissez-vous un moyen pour automatiser ce regroupement ?

Je vous remercie par avance pour votre aide.

Bien cordialement,
Jex94

 

[Grand Chaman Excel]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Bonjour Jex94,

Par macro, c'est possible de le faire. Voici une piste pour commencer, tu devras modifier le code selon ton cas précis.
N'hésite pas à revenir si tu as besoin d'aide :

Le Grand Chaman Excel: VBA | Récupérer les données de plusieurs fichiers
Le Grand Chaman Excel: VBA | Récupérer les données de plusieurs fichiers (2); Fonction DIR

A+

 

[Jex94]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Salut LegrandChaman,

Merci beaucoup pour ces éléments.

N'ayant jamais codé en VBA (jusqu'à présent, les fonctions basiques d'excel m'ont toujours suffit), je crois que je ne saurais pas par ou commencer..

-> Si tu as le temps, pourrais-tu m'aiguiller ?
Je dois copier coller ta macro dans mon fichier récap c'est ça ?
Ensuite je ne modifie que les données en vert ?

Merci par avance pour ton aide,
Jex

 

[Grand Chaman Excel]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Bonjour Jex94,

Oui, il faudrait que tu copie la macro dans un module de ton fichier Recap.
Les données en vert sont des commentaires, donc ça ne sert à rien de les modifier. Ce sont les autres lignes de code qu'il faut modifier mais ça risque d'être ardu si c'est la première fois que tu fais une macro. Je peux t'aider à le faire si tu veux.

Quelques questions pour éclairer :
- Dans quel répertoire se trouvent tes 500 fichiers?
- Est-ce que le nom des fichiers à récupérer ont tous la même "forme" ?
- Dans quel répertoire se trouvera ton fichier Recap?

A+

 

[Jex94]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Salut à toi Grand Chamane,

Merci beaucoup de te pencher sur mon problème ca fait vraiment plaisir !!

Pour répondre à tes questions :

Quelques questions pour éclairer :
- Dans quel répertoire se trouvent tes 500 fichiers?

-> Sur un serveur, dans un dossier ad'hoc

- Est-ce que le nom des fichiers à récupérer ont tous la même "forme" ?


-> Chaque fichier à un nom différent, (chaque fichier correspond à une société) et finit par "BAL"

Ex :
- "KANOE BAL".xls
- "JACK BAL".xls
- "KIWI BAL".xls

- Dans quel répertoire se trouvera ton fichier Recap?

-> Dans le même répertoire que celui des fichiers.

A ta dispo pour toute info complémentaire.
Jex

 

[Grand Chaman Excel]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Bonjour Jex94,

Voici ton fichier récapitulatif à essayer.
J'ai supposé que les données se trouvent toujours sur le premier onglet et commencent dans la cellule A1.
Les données sont recopiées dans une feuille nommée "Recap".
Il te suffit de cliquer sur le bouton et ensuite de sélectionner le répertoire contenant les fichiers.

A+

 

[Jex94]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Bonjour à toi Grand Chamane,

Un grand merci pour ton fichier qui m'a beaucoup aidé.
J'ai modifié la cellule de départ en A2 car je me suis aperçu que la première cellule était vide.
J'aurai encore une petite question à te demander: je ne comprends pas pourquoi il y a un décalage entre les fichiers.
Par exemple dans le fichier ANGE il apparaît les données suivantes:
07FERDIN
07MARIEC
07PANCAL
08FERDIN
alors que dans le fichier de regoupement ces données se retrouvent avec le dossier AMAZONITE.
Pourrais-tu STP m'indiquer la démarche à suivre pour ne plus qu'il y ait de décalage.

Merci beaucoup pour ton aide.

Jex94

 

[Jex94]

Re : Regroupement de fichiers excel + ajout d'une colonne référencant la source

Re-bonjour,

En fait, le décalage apparait à cause d'un problème sur la selection des données de chaque fichiers qui est non exhaustive : La selection s'arrete sur la derniere ligne ou la cellule A est remplie, alors qu'il y a encore des lignes à prendre en compte, bien que la cellule A soit vide.
-> Dans le fichier ALMANDIN par exemple, la selection s'arrête ligne 36, car les cellules A37 à A41 sont vides, mais j'ai bien des données à prendre en compte sur ces lignes 37 à 41.

En revanche, la macro reprend bien 41 lignes pour le fichier ALMANDIN et a bien rempli la colonne A en reprenant le nom du fichier ALMANDIN, mais, à partir de la ligne 37, elle a inséré les données issues du 2ème fichier AMAZONITE, alors qu'en colonne A, c'est le nom du fichier ALMANDIN qui est reporté.

C'est donc la formule de selection des données de chaque fichier qui n'est pas adapté à l'absence de données dans certaines cellules de la colonne A :

Je recopie la macro ci-dessous en surlignant le code qui serait à adapter à ce cas particulier :

Macro qui permet de compiler les informations contenues dans
' différents fichier pour les regrouper dans un fichier récapitulatif
' Sélection d'un répertoire contenant les fichiers
' Par : GCExcel
'-------------------------------------------------------------------------------
Sub Creer_Recapitulatif()
Dim sRep As String 'Répertoire ou filtre
Dim sFichier As String
Dim wb As Workbook, ws As Worksheet, rg As Range
Dim wbR As Workbook, wsR As Worksheet, rgC As Range
Dim tablo

Set wbR = ThisWorkbook 'fichier récapitulatif
Set wsR = wbR.Sheets("Recap") 'onglet récapitulatif

Application.ScreenUpdating = False
sRep = ChoisirRepertoire & "\"
'Boîte de dialogue pour choisir répertoire
sFichier = Dir(sRep)
Do While sFichier <> ""
If sFichier <> wbR.Name Then
Set wb = Workbooks.Open(sRep & sFichier) 'ouvrir le fichier
Set ws = wb.Sheets(1) 'les données se trouvent dans le 1er onglet
Set rg = ws.Range("A2").CurrentRegion 'sélection des données tablo = rg 'mettre les données dans un tablo pour copier ensuite

wsR.Range("A65000").End(xlUp).Offset(1, 0).Resize(rg.Rows.Count, 1) = wb.Name 'nom du fichier
wsR.Range("B65000").End(xlUp).Offset(1, 0).Resize(rg.Rows.Count, 4) = tablo 'données
wb.Close savechanges:=True
End If
sFichier = Dir 'trouve le prochain fichier
Loop
Application.ScreenUpdating = True
End Sub
Function ChoisirRepertoire() As String
Dim diaFolder As FileDialog
' Ouvrir la boîte de dialog
On Error Resume Next
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Show
ChoisirRepertoire = diaFolder.SelectedItems(1)
Set diaFolder = Nothing
End Function

Merci !
Jex94