Extraction de données à partir de plusieurs classeurs excel

[Elona]

Bonjour,

Dans le cadre de mon travail je dois analyser et traiter des résultats mais le travail est assez fastidieux. Je viens vers vous car je n'arrive pas à trouver ce que je veux sur les forums pour faciliter ce travail.

Je cherche à faire une extraction de données à partir de plusieurs fichiers excel pour les regrouper dans un seul.

En fait, je fais des expériences (j'en fais chaque semaine encore et encore), pour chaque expérience un fichier excel est généré. J'ai mis en pièce jointe un exemple : GM5_20c (le nom du fichier commence toujours par "GM"). Dans ces fichiers excel, il y a quatre feuilles qui m'intéressent (j'ai supprimer les autres) :
- "IntResults1" ===> elle correspond à "310 nm"
- "IntResults2" ===> elle correspond à "254 nm"
- "IntResults3" ===> elle correspond à "280 nm"
- "IntResults4" ===> elle correspond à "Fluo"

Dans ces feuilles excel, je veux extraire les données dans les colonnes E ; F ; G et H pour les transférer les unes à la suite des autres dans un fichier excel appelé "Récapitulatif" et plus précisément dans la feuille "Données brutes UV" de ce fichier.

Mais dans le fichier récapitulatif, j'aimerais que dans la colonne A s'affiche le nom du fichier excel source (à savoir GM5_20c dans mon exemple) sur chaque ligne et dans la colonne B s'il s'agit de 310 nm/254 nm/280 nm selon qu'il s'agit de la feuille IntResults1/IntResults2/IntResults3.

Pour les données issues de la feuille "IntResults4", j'aimerais qu'elles apparaissent dans une autre feuille appelée "Données brutes Fluo". Mais le principe est le même : indiquer le nom du fichier excel source dans la colonne A et "Fluo" dans la colonne B.

Remarques :
- je suis une cruche en ce qui concerne les macros sous excel (c'est pas faute d'avoir essayé de comprendre) ;
- les fichiers se trouvent/se trouveront tous dans le même dossier répertoire ;
- le nombre de données (nombre de ligne) varient d'un fichier excel à un autre (voire même d'une feuille à une autre) ;
- il est possible que je sois amenée à utiliser ce fichier sur une version plus récente d'excel (2010 voire 2013).

J'aimeras solliciter votre aide afin de pouvoir automatiser cette extraction de données.

Je vous en remercie d'avance

Cdt

 

[Elona]

Re : Extraction de données à partir de plusieurs classeurs excel

Bonjour,

Désolé de ne répondre que maintenant, j'ai un peu décroché hier soir.

Donc la macro marche super bien ... à deux petits détails près :

- Pour les "Données brutes UV", elle me marque le nom du classeur dans la colonne B au lieu de 310 nm/254 nm/280 nm. Pour les "Données brutes Fluo" par contre, aucun soucis ;

- Le fichier que j'ai mis en exemple avec les données à extraire (GM5_20c.xlsx) ne contient que 4 onglets mais en réalité mes fichiers peuvent contenir une vingtaine d'onglets (aucun n'est vide j'ai vérifié). Généralement, les 4 onglets qui m'intéressent sont vers la fin mais ils ne se suivent pas forcément. La macro fonctionne très bien sur le fichier exemple mais pas sur mes autres fichiers du coup ^^.

 

[Robert]

Re : Extraction de données à partir de plusieurs classeurs excel

Bonjour Elona, bonjour le forum,

Bon, un nouveau code qui ne tient plus compte de la position des onglets mais de leur noms. Le bug de la longueur d'onde a été réparé. J'espère que ça va tourner correctement...
Le code :

Sub Macro1()
Dim cr As Workbook 'déclare la variable cr (Classeur Resultat)
Dim r1 As Object 'déclare la variable r1 (onglet Réception 1)
Dim r2 As Object 'déclare la variable r2 (onglet Réception 2)
Dim paf As Range 'déclare la variable paf (Plage A Effacer)
Dim ch As String 'déclare la variable ch (CHemin)
Dim sf As Object 'déclare la variable sf (Système de Fichiers)
Dim d As Object 'déclare la variable d (Dossier)
Dim o As Object 'déclare la variable o (Onglets)
Dim fs As Object 'déclare la variable fs (FichierS)
Dim f As Object 'déclare la variable f (Fichier)
Dim cd As Workbook 'déclare la variable cd (Classeur de Données)
Dim dl As Integer 'déclare la variable dl (Dernière Ligne)
Dim i As Byte 'déclare la variable i (Incrément)
Dim lo As String 'déclare la variable lo (Longueur d'Onde)

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set cr = ThisWorkbook 'définit le classeur resultat cr
Set r1 = cr.Sheets("Données brutes UV") 'définit l'onglet de réception1  r1
Set r2 = cr.Sheets("Données brutes Fluo") 'définit l'onglet de réception2  r2
Set paf = r1.Range("A1").CurrentRegion 'définit la plage à effacer paf
If paf.Rows.Count > 1 Then 'si paf à plus d'une seule ligne
    Set paf = paf.Offset(1, 0).Resize(paf.Rows.Count - 1, paf.Columns.Count) 'redéfinit paf (sans la première line)
    paf.Clear 'supprime les anciennes données
End If 'fin de la condition
Set paf = r2.Range("A1").CurrentRegion 'définit la plage à effacer paf
If paf.Rows.Count > 1 Then 'si paf à plus d'une seule ligne
    Set paf = paf.Offset(1, 0).Resize(paf.Rows.Count - 1, paf.Columns.Count) 'redéfinit paf (sans la première line)
    paf.Clear 'supprime les anciennes données
End If 'fin de la condition
ch = cr.Path 'définit la chemin
Set sf = CreateObject("Scripting.FileSystemObject") 'définit la variable sf
Set d = sf.getfolder(ch) 'définit la variable d
Set fs = d.Files 'définit la variable fs
For Each f In fs 'boucles 1 : sur touts les fichiers du dossier d
    If Right(f.Name, 5) = ".xlsx" And Left(f.Name, 2) = "GM" Then
        Workbooks.Open f 'ouvre le fichier
        Set cd = ActiveWorkbook 'définit le classeur de données cd
        For Each o In cd.Sheets 'boucle 2 : sur tous les onglets du classeur cd
            Select Case o.Name 'agit en fonction du nom de l'onglet
                Case "IntResults1", "IntResults2", "IntResults3" 'cas "IntResults1" à "IntResults3"
                    dl = o.Cells(Application.Rows.Count, 5).End(xlUp).Row 'définit la dernière ligne éditée dl de la colonne 5 (=E)de l'onglet o du classeur cd
                    If o.Cells(dl, 5).Value = "" Then GoTo suite 'si aucune cellule éditée en colonne E, va à l'étiquette "suite"
                    Set dest = r1.Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0) 'définit la cellule de destination dest (colonne C)
                    o.Range("E2:H" & dl).Copy dest 'copie la plage éditée et la colle dans dest
                    r1.Cells(dest.Row, 1).Resize(dl - 1, 1).Value = cd.Name 'place le nom du classeur
                    If o.Name = "IntResults1" Then lo = "310 nm" 'définit la longueur d'onde lo
                    If o.Name = "IntResults2" Then lo = "254 nm" 'définit la longueur d'onde lo
                    If o.Name = "IntResults1" Then lo = "280 nm" 'définit la longueur d'onde lo
                    r1.Cells(dest.Row, 2).Resize(dl - 1, 1).Value = lo 'place le nom du classeur
                Case "IntResults4" 'cas "IntResults4"
                    dl = o.Cells(Application.Rows.Count, 5).End(xlUp).Row 'définit la dernière ligne éditée dl de la colonne 5 (=E)de l'onglet 4 du classeur cd
                    If o.Cells(dl, 5).Value = "" Then GoTo suite 'si aucune cellule éditée en colonne E, va à l'étiquette "suite"
                    Set dest = r2.Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0) 'définit la cellule de destination dest (colonne C)
                    o.Range("E2:H" & dl).Copy dest 'copie la plage éditée et la colle dans dest
                    r2.Cells(dest.Row, 1).Resize(dl - 1, 1).Value = cd.Name 'place le nom du classeur
                    r2.Cells(dest.Row, 2).Resize(dl - 1, 1).Value = "Fluo" 'place la longueur d'onde lo
            End Select
suite:
        Next o
        cd.Close 'ferme le classeur de données
    End If
Next f
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
End Sub

 

[Elona]

Re : Extraction de données à partir de plusieurs classeurs excel

Mais ... mais ça marche du tonnerre de dieu

J'ai juste relevé une petite erreur que j'ai corrigée moi même (si si ^^).

Tu as mis :
If o.Name = "IntResults1" Then lo = "310 nm" 'définit la longueur d'onde lo
If o.Name = "IntResults2" Then lo = "254 nm" 'définit la longueur d'onde lo
If o.Name = "IntResults1" Then lo = "280 nm" 'définit la longueur d'onde lo

Quand j'exécute la macro, ça m'importe bien mes données mais ça me marque "280 nm" à la place de "310 nm" : le nom de l'onglet indiqué n'était pas le bon, j'ai donc corrigé par "IntResults3" et maintenant ça marche super bien.

Trop bien merci pour ton aide, cette macro va m'être d'une grande utilité

@+++

 

[Robert]

Re : Extraction de données à partir de plusieurs classeurs excel

Bonjour Elona, bonjour le forum,

Alleluia !!

 

[auverland]

Re : Extraction de données à partir de plusieurs classeurs excel

Bonjour,
J'essais d'adapter votre exemple a mon besoin

je cherche a copier par exemple la zone $A$1:$K$60 de l'ongler "IntResults1" et la coller non plus sur la premiere ligne vide de la colonne E mais sur la premiere colonne vide de la ligne A du fichier récapitulatif
Merci de votre aide