regrouper des données de plusieurs fichiers (macros?)

[ZZoé]

Bonjour!

Je me suis engagée à faire une analyse statistique des "compétences managériales" dévelopées par les étudiants de mon école pendant leurs stages. Je maîtrise à peu près les logiciels statistiques comme Modalisa et Sphinx, mais pas tellement Excel...

Or les étudiants ont répondu aux questions sur des fichiers Excel. J'ai donc 421 fichiers Excel qui correspondent chacun à une personne. Et dans chaque fichier, il y a plusieurs feuilles, dont une qui m'intéresse particulièrement parce qu'il y a un tableau avec les données qu'il me faut.

Le tableau est de cette forme:
- en intitulé de colonne: le secteur et la fonction du stage, les différentes compétences
- deux lignes: une pour l'auto-évaluation de l'étudiant par rapport aux compétences (note de 1 à 10) et l'autre pour l'évaluation de l'entreprise.

J'aurais besoin d'avoir ces données (de tous les étudiants) sur une seule feuille et dans un seul tableau avec en intitulé de colonne: les compétences évaluées par l'étudiant et par l'entreprise, le secteur et la fonction du stage, pour pouvoir ensuite importer ce tableau dans le logiciel de statistiques Sphinx.

On m'a dit qu'il fallait utiliser des "macros"... mais je ne sais pas ce que ça veut dire et encore moins comment les utiliser! Je pourrais faire des copier-coller des 421 tableaux (ça je sais faire!), mais ça prendrait un temps fou!

Toute aide serait la TRES bienvenue...!

Merci d'avance,

Zoé

 

[skoobi]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonsoir Zoé, bienvenue sur XLD,

il faudrait nous en dire un peu plus: quel est le répertoire où ce trouve les fichiers. Il n'y a que ces fichiers dans ce répertoire? Si non, comment les identifier, par le nom de la personne?
De plus, un fichier zip avec par exemple le fichier résultat et 2 fichiers anonymes bien sûr montrant ce qu'il faut récupérer nous permettra de t'aider.

 

[ZZoé]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour Skoobi!

voici en pièce jointe le dossier zip avec deux fichiers-réponses (les fichiers portent le nom de l'étudiant) et le tableau type avec les données particulières dont j'aurais besoin. J'ai ajouté aussi le dernier stage effectué par l'étudiant et son lieu qui ne sont pas forcément sur les mêmes cases d'un étudiant à un autre... si ça complique trop, ce n'est pas grave, on peut l'enlever!

Tous les fichiers sont dans le même dossier qui s'appelle "données excel". Un dossier, c'est bien la même chose qu'un répertoire?

en tout cas, merci beaucoup pour ton aide!

bonne journée,

Zoé

 

[PMO2]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour,

Voici un code à copier dans un module standard

Pour ce faire, faites Alt+F11 pour ouvrir l'éditeur de Visual Basic.
Dans l'éditeur, faites menu Insertion/Module et dans la fenêtre qui
est créée copiez le code ci-dessous

****************
Private Function ChoisirDossier() As String
Dim objShell
Dim objFolder
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
    (&H0&, "Sélectionnez un Dossier", &H1&)
On Error GoTo Erreur
ChoisirDossier = objFolder.ParentFolder _
    .ParseName(objFolder.Title).Path & ""
Exit Function
Erreur:
ChoisirDossier = ""
End Function

Sub GrouperDataFichiers()

Dim FSO 'As Scripting.FileSystemObject
Dim SourceFolder 'As Scripting.Folder
Dim FileItem 'As Scripting.File
Dim chemin$
Dim T()
Dim cpt&
Dim g&
Dim i&
Dim j&
Dim Lig&
Dim var
Dim WB As Workbook
Dim S As Worksheet
Dim DEST As Worksheet
Dim Info(1 To 1, 1 To 26)
'------------
chemin$ = ChoisirDossier
If chemin$ = "" Then Exit Sub
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(chemin$)
If SourceFolder.Files.Count = 0 Then Exit Sub
For Each FileItem In SourceFolder.Files
  If LCase(Right(FileItem.Name, 4)) = ".xls" Then
    cpt& = cpt& + 1
    ReDim Preserve T(1 To cpt&)
    T(cpt&) = chemin$ & "\" & FileItem.Name
  End If
Next FileItem
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
'------------
Application.ScreenUpdating = False
Set DEST = Sheets.Add
Lig& = 1
For g& = 1 To UBound(T)
  Set WB = GetObject(T(g&))
  Set S = WB.Sheets("IDENTIFICATION")
  Info(1, 1) = S.Range("c4")
  Info(1, 2) = S.Range("h12")
  var = Array("", "b", "c", "f")
  For j& = 1 To 3
    For i& = 20 To 18 Step -1
      If S.Range(var(j&) & i&) <> "" Then
        Info(1, 2 + j&) = S.Range(var(j&) & i&)
        Exit For
      End If
    Next i&
  Next j&
  Set S = WB.Sheets("COMPETENCES MANAGERIALES")
  Info(1, 6) = S.Range("D10")
  For i& = 5 To 14
    Info(1, 2 + i&) = S.Range(S.Cells(10, i&), _
          S.Cells(10, i&))
    Info(1, 12 + i&) = S.Range(S.Cells(11, i&), _
          S.Cells(11, i&))
  Next i&
  WB.Close
  Set WB = Nothing
  Lig& = Lig& + 1
  DEST.Range(DEST.Cells(Lig&, 1), _
        DEST.Cells(Lig&, UBound(Info, 2))) = Info
  Erase Info
Next g&
var = Array("Nom/prénom", "choix 3e année", "lieu dernier stage" _
  , "entreprise dernier stage", "fonction", "secteur", "Leadership" _
  , "Teamwork", "Interpersonal Awareness / People skills" _
  , "Communication skills", "Technical & Business Knowledge" _
  , "Analytical skills", "Results orientation & Drive" _
  , "Self awareness / Personnal effectiveness", "Ability to Learn & Grow" _
  , "Creativity & Innovation", "Leadership", "Teamwork" _
  , "Interpersonal Awareness / People skills", "Communication skills" _
  , "Technical & Business Knowledge", "Analytical skills" _
  , "Results orientation & Drive", "Self awareness / Personnal effectiveness" _
  , "Ability to Learn & Grow", "Creativity & Innovation")
With DEST
  .Range(.Cells(1, 1), .Cells(1, UBound(var) + 1)) = var
  .Range("a1").Interior.ColorIndex = 6
  .Range("b1:f1").Interior.ColorIndex = 45
  .Range("g1:p1").Interior.ColorIndex = 3
  .Range("q1:z1").Interior.ColorIndex = 39
End With
Application.ScreenUpdating = False
Exit Sub
Erreur:
Application.ScreenUpdating = False
MsgBox "Erreur " & Err.Number & vbCrLf & Err.Description
End Sub
****************

PREPARATION
Créez un dossier et renommez le (par ex. "Dossier Etudiants"). Mettez y
les 421 classeurs (ou plus) les concernant. Mettez le Dossier Etudiants
dans le disque C (ou D ou autre mais par sur le Bureau pour raisons techniques).

FONCTIONNEMENT
Dans le classeur où vous avez copié le code et dans la fenêtre d'Excel faites Alt+F8
Une boîte de macro apparaît et la macro "GrouperDataFichiers" doit y figurer.
Lancez cette macro et les infos s'inscriront dans une nouvelle feuille.

Cordialement.

PMO
Patrick Morange

 

[ZZoé]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonsoir,

j'ai suivi vos indications, j'ai donc obtenu un tableau avec les bonnes informations mais incomplet: un peu moins de la moitié des étudiants n'ont pas été pris en compte. Et un message d'erreur est apparu: "indice en dehors de la plage (erreur 9)". Quand je clique sur "débogage", ça surligne en jaune la ligne "Set S= WB.Sheets("COMPETENCES MANAGERIALES)"

que dois-je faire?

bonne soirée et encore merci,

Zoé

 

[PMO2]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonsoir Zoé,

A première vue, il doit s'agir d'un classeur dont la feuille
"COMPETENCES MANAGERIALES" est introuvable soit qu'elle a été renommée,
soit elle est mal orthographiée ou un espace y a été introduit.

Pour définir le classeur pollué, faites tourner la macro et lorsqu'elle plante
à la ligne
Set S = WB.Sheets("COMPETENCES MANAGERIALES")
positionnez la souris sur la ligne qui se trouve plus haut
Set WB = GetObject(T(g&)) et en particulier sur le T de T(g&)
Vous devriez voir apparaître le nom du classeur et son chemin.

Une fois identifié vous pouvez ouvrir ce classeur pour voir ce qu'il contient.

ATTENTION la reconnaissance de la feuille par le programme est sensible
à la casse ; c'est à dire qu'il ne confond pas les minuscules avec les majuscules.
Ainsi, "COMPETENCES MANAGERIALES" est différent de "Compétences MANAGÉRIALES".

J'espère avoir été clair et que la solution de votre problème est bien ce que je décris.

Meilleures salutations et bon courage.

PMO
Patrick Morange

 

[JNP]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour le forum ,
PMO2 : Félicitation : efficacité, clarté, pédagogie, politesse, ça fait plaisir de voir des gens comme toi rejoindre le forum (j'ai vu un certain nombre de tes posts).
Une alternative au problème de modification de l'onglet, attaquer la feuille par le CodeName au lieu du Name (il y a peu de chance qu'il ai été changé).

Set S = WB.Feuil5

Bonne journée

 

[PMO2]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour JNP,

Je vous remercie pour toutes ces appréciations élogieuses
qui me vont droit au coeur. A bientôt de se rencontrer sur le forum.

Cordialement.

PMO
Patrick Morange

 

[openapero]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour,

Tout d'abord merci pour la qualité de vos réponses.
J'essaye de me servir du code de PMO2, en l'adaptant à mon besoin, mais j'avoue buter sur certaines difficultés.
Pourriez vous me décrire le comportement de la variable info()?
Merci par avance pour vos précisions.

 

[PMO2]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour OpenApero,

La variable Info est de type Variant et peut être utilisée comme un tableau.
On dimensionne Info (1 To 1, 1 To 26) qui peut être interprété comme 1 ligne (1 To 1) et
26 colonnes (1 To 26) car on trouve au final 26 informations en colonnes Ax à Zx (x pour la ligne).
Cette technique permet le travail en mémoire et on balance toutes les valeurs en une seule fois.
Voir la ligne :
DEST.Range(DEST.Cells(Lig&, 1), _
DEST.Cells(Lig&, UBound(Info, 2))) = Info

Pour mieux vous rendre compte, reprenez le fichier "exemple.zip" de Zoé qui contient
2 classeurs étudiant. Faites la même démarche que celle décrite dans mon premier message
MAIS après avoir remplacé l'ancien code par celui ci-dessous qui est identique à l'ancien
hormis 3 nouvelles lignes (inutiles pour le fonctionnement) qui permettent de visualiser ce
qui se passe en mode pas à pas.

Private Function ChoisirDossier() As String
Dim objShell
Dim objFolder
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
    (&H0&, "Sélectionnez un Dossier", &H1&)
On Error GoTo Erreur
ChoisirDossier = objFolder.ParentFolder _
    .ParseName(objFolder.Title).Path & ""
Exit Function
Erreur:
ChoisirDossier = ""
End Function

Sub GrouperDataFichiers()
Dim FSO 'As Scripting.FileSystemObject
Dim SourceFolder 'As Scripting.Folder
Dim FileItem 'As Scripting.File
Dim chemin$
Dim T()
Dim cpt&
Dim g&
Dim i&
Dim j&
Dim Lig&
Dim var
Dim WB As Workbook
Dim S As Worksheet
Dim DEST As Worksheet
Dim Info(1 To 1, 1 To 26)
'------------
chemin$ = ChoisirDossier
If chemin$ = "" Then Exit Sub
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(chemin$)
If SourceFolder.Files.Count = 0 Then Exit Sub
For Each FileItem In SourceFolder.Files
  If LCase(Right(FileItem.Name, 4)) = ".xls" Then
    cpt& = cpt& + 1
    ReDim Preserve T(1 To cpt&)
    T(cpt&) = chemin$ & "\" & FileItem.Name
  End If
Next FileItem
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
'------------
Application.ScreenUpdating = False
Set DEST = Sheets.Add
Lig& = 1
For g& = 1 To UBound(T)
  Set WB = GetObject(T(g&))
  
'### pour visualiser en mode pas à pas (virer la ligne par la suite) ###
WB.Windows(1).Visible = True
'#######################################################################
  
  Set S = WB.Sheets("IDENTIFICATION")

'### pour visualiser en mode pas à pas (virer la ligne par la suite) ###
S.Activate
'#######################################################################
   
  Info(1, 1) = S.Range("c4")
  Info(1, 2) = S.Range("h12")
  var = Array("", "b", "c", "f")
  For j& = 1 To 3
    For i& = 20 To 18 Step -1
      If S.Range(var(j&) & i&) <> "" Then
        Info(1, 2 + j&) = S.Range(var(j&) & i&)
        Exit For
      End If
    Next i&
  Next j&
  Set S = WB.Sheets("COMPETENCES MANAGERIALES")
  
'### pour visualiser en mode pas à pas (virer la ligne par la suite) ###
S.Activate
'#######################################################################

  Info(1, 6) = S.Range("D10")
  For i& = 5 To 14
    Info(1, 2 + i&) = S.Range(S.Cells(10, i&), _
          S.Cells(10, i&))
    Info(1, 12 + i&) = S.Range(S.Cells(11, i&), _
          S.Cells(11, i&))
  Next i&
  WB.Close
  Set WB = Nothing
  Lig& = Lig& + 1
  DEST.Range(DEST.Cells(Lig&, 1), _
        DEST.Cells(Lig&, UBound(Info, 2))) = Info
  Erase Info
Next g&
var = Array("Nom/prénom", "choix 3e année", "lieu dernier stage" _
  , "entreprise dernier stage", "fonction", "secteur", "Leadership" _
  , "Teamwork", "Interpersonal Awareness / People skills" _
  , "Communication skills", "Technical & Business Knowledge" _
  , "Analytical skills", "Results orientation & Drive" _
  , "Self awareness / Personnal effectiveness", "Ability to Learn & Grow" _
  , "Creativity & Innovation", "Leadership", "Teamwork" _
  , "Interpersonal Awareness / People skills", "Communication skills" _
  , "Technical & Business Knowledge", "Analytical skills" _
  , "Results orientation & Drive", "Self awareness / Personnal effectiveness" _
  , "Ability to Learn & Grow", "Creativity & Innovation")
With DEST
  .Range(.Cells(1, 1), .Cells(1, UBound(var) + 1)) = var
  .Range("a1").Interior.ColorIndex = 6
  .Range("b1:f1").Interior.ColorIndex = 45
  .Range("g1:p1").Interior.ColorIndex = 3
  .Range("q1:z1").Interior.ColorIndex = 39
End With
Application.ScreenUpdating = False
Exit Sub
Erreur:
Application.ScreenUpdating = False
MsgBox "Erreur " & Err.Number & vbCrLf & Err.Description
End Sub

Si vous éprouvez des difficultés faites moi signe.

Cordialement.

PMO
Patrick Morange

 

[ZZoé]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonsoir!

ça y est j'ai réussi à tout avoir dans un tableau, avec toutes les données qu'il me fallait! c'est parfait! merci beaucoup! Pour information, je devais cliquer sur ok pour enregistrer les modifications apportées pour tous les fichiers. Mais c'est peut-être moi qui n'ai pas bien fait qqc...

Dernière question (par pure curiosité): qui êtes-vous? avez-vous été embauchés par qqn (et qui?) pour répondre à ce type de questions ou "simplement" des passionnés d'informatique?

En tout cas, vous m'avez été d'une grande aide,

merci encore,

Bonne soirée,

Zoé

 

[JNP]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour le forum ,
On est tous des fanas qui passont un peu de temps à se creuser les méninges, suivant le précepte "Y en a plus dans 2 têtes que dans 1"... C'est agréable, quand tu coinces sur un problème, qu'un autre t'aide. Et du coup, c'est logique que quand un autre coince, tu essaie de l'aider... On appelle cela une communauté. Rejoins là pleinement (ne serait-ce qu'en devenant supporter par exemple), tu verras que c'est agréable...
Bonne journée

 

[PMO2]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour Zoé,

Je suis content que vous soyez arrivée au terme de votre réalisation.
Pour répondre à votre curiosité, je suis développeur (spécialisé en VBA) et, lorsque je n'ai pas de mission à effectuer, on me voit sur les forums et notamment sur celui-ci que je viens de découvrir dernièrement.

En plus de venir en aide aux gens, ceci m'apporte au moins deux choses :
- Parfaire l'analyse (essayer de comprendre au mieux ce qui est demandé)
- Faire le programme d'automatisation (très souvent sur des problèmes qui me sont nouveaux et qui me permettent de faire évoluer des techniques, des algorithmes)

Bonne continuation.

Et au passage mon bonjour à JNP.

PMO
Patrick Morange

 

[muirdhin]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour le forum ,
PMO2 : Félicitation : efficacité, clarté, pédagogie, politesse, ça fait plaisir de voir des gens comme toi rejoindre le forum (j'ai vu un certain nombre de tes posts).
Une alternative au problème de modification de l'onglet, attaquer la feuille par le CodeName au lieu du Name (il y a peu de chance qu'il ai été changé).

Set S = WB.Feuil5

Bonne journée

bonjour,

Tout d'abord merci pour l'explication précédente qui m'a permis de débuter et de comprendre un minimum le fonctionnement de la macro.
Je tombe sur 2 "hic" :
1/J'ai actuellement le même type de problème. Mais fichier on est onglet incrémentaux "Name 1","Name 2",... et la commande quoté ne veut pas passer
2/Je n'y ai pas encore reflechi mais chaque fichiers à un nombre variable de lignes et je souhaiterais qu'elles soient toutes intégrées

Je pense que la solution doit être dans le code en gras d'après ce que vous avez expliqué plus tôt

merci d'avance

Arnaud

voici une version "épurée"

Option Explicit

Private Function ChoisirDossier() As String
Dim objShell
Dim objFolder
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
(&H0&, "Sélectionnez un Dossier", &H1&)
On Error GoTo Erreur
ChoisirDossier = objFolder.ParentFolder _
.ParseName(objFolder.Title).Path & ""
Exit Function
Erreur:
ChoisirDossier = ""
End Function

Sub GrouperDataFichiers()

Dim FSO 'As Scripting.FileSystemObject
Dim SourceFolder 'As Scripting.Folder
Dim FileItem 'As Scripting.File
Dim chemin$
Dim T()
Dim cpt&
Dim g&
Dim i&
Dim j&
Dim Lig&
Dim var
Dim WB As Workbook
Dim S As Worksheet
Dim DEST As Worksheet
Dim Info(1 To 1, 1 To 26)
'------------
chemin$ = ChoisirDossier
If chemin$ = "" Then Exit Sub
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(chemin$)
If SourceFolder.Files.Count = 0 Then Exit Sub
For Each FileItem In SourceFolder.Files
If LCase(Right(FileItem.Name, 4)) = ".xls" Then
cpt& = cpt& + 1
ReDim Preserve T(1 To cpt&)
T(cpt&) = chemin$ & "\" & FileItem.Name
End If
Next FileItem
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
'------------
Application.ScreenUpdating = False
Set DEST = Sheets.Add
Lig& = 1
For g& = 1 To UBound(T)
Set WB = GetObject(T(g&))
Set S = WB.Sheets("COMPETENCES MANAGERIALES")
For i& = 0 To 10
Info(1, 1 + i&) = S.Range(S.Cells(10, 5 + i&), _
S.Cells(10, 5 + i&))
Next i&
WB.Close
Set WB = Nothing
Lig& = Lig& + 1
DEST.Range(DEST.Cells(Lig&, 1), _
DEST.Cells(Lig&, UBound(Info, 2))) = Info
Erase Info
Next g&
var = Array("ANNEE", "MOIS", "JOUR" _
, "DOMAINE", "ACHETEUR", "CL", "CODE_FNR" _
, "ARTICLE", "CLAS_ACHET" _
, "EN-CDE", "CLASSE_SYST")
With DEST
.Range(.Cells(1, 1), .Cells(1, UBound(var) + 1)) = var
End With
Application.ScreenUpdating = False
Exit Sub
Erreur:
Application.ScreenUpdating = False
MsgBox "Erreur " & Err.Number & vbCrLf & Err.Description
End Sub

 

[muirdhin]

Re : regrouper des données de plusieurs fichiers (macros?)

Bonjour Patrick,

Il sera en effet surement plus simple pour vous de reflechier sur un exemple comme vous me l'avez demandé. Putot que de partir d'une "bidouillle" de l'exemple précédent.Excusez-moi

Ci-joint 2 fichiers exemple que je cherche à intégrer dans le fichier cible

- le nombre de ligne est variable pour chaque fichier :
RUPT 1.xls = 413 lignes à recupérer
RUPT 2.xls = 392 lignes à récupérer

- Le libellé des onglets sont différents (100 fichiers) :
RUPT 1
RUPT 2
...
Je souhaiterais donc ne pas faire reférence au Name de l'onglet mais plutot au codeName comme proposé par JNP

Merci d'avance

Arnaud