Macro pour récupérer les infos de plusieurs classeurs

[m@tix]

Bonsoir,

Je cherche à obtenir une macro relativement simple qui me permettrait de récupérer les infos présentes dans plusieurs classeurs.

Voici le contexte : périodiquement, je récupère un certain nombre de classeurs venant de plusieurs utilisateurs, dont la structure est identique (cf. pièce jointe tableau.xlsx). Seuls leurs noms diffèrent, mais la syntaxe est du même type. Par exemple, pour une personne nommée Jean Dupont, son classeur sera DUPJE.xlsx.

Mon but est de récupérer l'ensemble des infos importantes présentes dans ces différents classeurs au sein d'un autre classeur (celui dans lequel serait présente la macro permettant de récupérer ces infos) (cf. pièce jointe recapitulatif.xlsx). J'ai joint à ce mail un exemple, basé sur un (et un seul) classeur type d'un utilisateur, accompagné du classeur censé récupérer les données que je souhaite exploiter. A priori, les classeurs des utilisateurs se trouveront tous dans un même dossier.

Une précision importante, qui pourrait causer une difficulté : comme vous le constaterez, dans les classeurs des utilisateurs apparaissent une catégorie 1 et une catégorie 2. Il faut savoir que sur une même ligne, lorsque Cat. 1 est renseignée, alors Cat. 2 reste vide, et inversement. Mais dans le fichier récapitulatif, une seule colonne est dédiée aux catégorie, quelle qu'elle soit (c'est important pour moi de conserver cela..). Ainsi, que ce soit Cat. 1 ou Cat. 2 de remplie dans les classeurs, je souhaiterais toujours obtenir l'information dans la même cellule du récapitulatif.

Pourriez-vous svp me proposer une macro capable de faire tout cela ?
Il est très probable que j'ai oublié de vous donner quelques infos essentielles, n'hésitez pas à me demander quoi que ce soit.

Merci d'avance, et bonne soirée à tous.

 

[job75]

Re : Macro pour récupérer les infos de plusieurs classeurs

Bonjour m@tix,

Téléchargez les 2 fichiers joints dans un même dossier (le bureau par exemple).

La macro MAJ est dans Module1 du fichier recapitulatif(1).

Elle se lance à l'ouverture du fichier (macro Open dans ThisWorkbook) ou par Ctrl+M.

Tout ça est finalement très classique.

Edit : je mets quand même ici la macro :

Sub MAJ()
'Ctrl+M lance aussi la macro
Dim F As Worksheet, chemin$, nomfich$, lig&, o As Boolean, r As Range, n&, tablo()
Set F = Feuil1 'codeName de la feuille du récapitulatif
chemin = ThisWorkbook.Path & "\" 'à adapter
nomfich = Dir(chemin & "*.xls*") '1er fichier du dossier
Application.ScreenUpdating = False
On Error Resume Next
F.[A2:I65536].ClearContents 'RAZ
lig = 2 '1ère ligne restituée
While nomfich <> ""
  If nomfich <> ThisWorkbook.Name Then
    o = False
    If IsError(Workbooks(nomfich).Name) Then 'si le fichier n'est pas déjà ouvert, on l'ouvre
      Workbooks.Open chemin & nomfich
      o = True
    End If
    With Workbooks(nomfich).ActiveSheet
      If .[B5] Like "Name*" Then 'critère...
        Set r = Nothing: n = 0
        Set r = .[E12:I500].SpecialCells(xlCellTypeConstants, 1)
        If Not r Is Nothing Then
          ReDim tablo(1 To r.Count, 1 To 9)
          For Each r In r
            n = n + 1
            tablo(n, 1) = .Cells(r.Row, 4) 'ID
            tablo(n, 2) = .Cells(r.Row, 2) & .Cells(r.Row, 3) 'Cat
            tablo(n, 3) = .[C5] 'Name
            tablo(n, 4) = CDate(.Cells(11, r.Column)) 'Day
            tablo(n, 5) = .[K5] 'Year
            tablo(n, 6) = Month(tablo(n, 4)) 'Month
            tablo(n, 7) = .[K6] 'Week
            tablo(n, 8) = r 'Hours
            tablo(n, 9) = .Cells(r.Row, 13) 'Comments
          Next
          F.Cells(lig, 1).Resize(n, 9) = tablo
          lig = lig + n
        End If
      End If
      If o Then .Parent.Close False 'si le fichier a été ouvert, on le referme
    End With
  End If
  nomfich = Dir 'fichier suivant du dossier
Wend
Application.Goto F.[A1], True
End Sub

A+

 

[youky(BJ)]

Re : Macro pour récupérer les infos de plusieurs classeurs

Bonjour m@tix,
Bonjour aussi à Job75,
Puisque le sujet m'intéressait et que je viens de finir la macro je l'envoye tout de même pour le fun.
La macro est a mettre dans un module et a exécuter.
Bruno

Sub regroupe()
Dim Wb As Workbook
chemin = ThisWorkbook.Path & "\" 'A revoir le chemin
fichier = "tableau.xlsx" ' ici aussi
Range("A2:I10000").ClearContents
Set Wb = GetObject(chemin & fichier)
With Wb.Sheets("Tableau")
lg = 2: jj = .[K7]
For lig = 12 To .[B:C].Find("*", , , , 1, 2).Row
For col = 5 To 9
If .Cells(lig, col) <> "" Then
Cells(lg, 1) = .Cells(lig, 4) 'ID
Cells(lg, 2) = IIf(.Cells(lig, 2) = "", .Cells(lig, 3), .Cells(lig, 2)) 'cat
Cells(lg, 3) = .[C5] 'nom
Cells(lg, 4) = jj: Cells(lg, 5) = Year(jj)
Cells(lg, 6) = Month(jj): Cells(lg, 7) = Day(jj)
Cells(lg, 8) = .Cells(lig, col)
Cells(lg, 9) = .Cells(lig, 13)
lg = lg + 1
End If
jj = jj + 1
Next
Next
End With
Wb.Close
End Sub

 

[job75]

Re : Macro pour récupérer les infos de plusieurs classeurs

Bonjour youky

Quelques remarques sur ta solution :

1) Set Wb = GetObject(chemin & fichier) ouvre le fichier comme le fait Workbooks.Open chemin & fichier

Je découvre que la fenêtre du fichier est ensuite masquée.

2) Il vaut mieux ne pas rouvrir le fichier s'il est déjà ouvert (d'où ma variable booléenne o).

3) Il faut une boucle pour étudier tous les fichiers du dossier.

4) Il vaut mieux stocker les valeurs dans un tableau (tablo) restitué en bloc, c'est plus rapide.

A+

 

[youky(BJ)]

Re : Macro pour récupérer les infos de plusieurs classeurs

Bonjour Job75,
Pour l'ouverture du fichier je préfère employer le GetObject plutot que workbooks.Open comme dans ce cas précis .
(pas d'écriture sur le fichier à ouvrir)
Exact il est masqué plus besoin du screenupdapting, et fichier ouvert ou fermé aucune importance je n'ai pas eu de soucis.
Je n'ai pas géré le nombre de fichiers à ouvrir car trop peu d'explication du demandeur.
La provenance des fichiers n'étant pas explicite,je pensais voir aprés
Pas sur que tous les fichiers à ouvrir soient dans le même dossier ni si tous les fichiers du dossier en question sont tous à ouvrir.
Pour les tableaux tu as parfaitement raison.
A+
Bruno

 

[m@tix]

Re : Macro pour récupérer les infos de plusieurs classeurs

Bonjour,

Tout d'abord, désolé pour ce retard dans ma réponse.
Un grand merci à vous deux, c'est précisément ce que je souhaitais.

Pour le moment je suis sur le code de job75. J'ai un petit souci avec les lignes :

Set r = Nothing: n = 0
Set r = .[E12:I500].SpecialCells(xlCellTypeConstants, 1)

Quel est le rôle de la seconde ligne en fait ? Je crois savoir que le "1" correspond à des entrées numériques dans les cellules. En fait, j'ai testé le code avec un autre fichier (rempli différemment, avec d'autres données), et r reste à Nothing, et du coup le reste de la macro ne s'exécute pas... De quoi cela peut-il venir ?

Merci d'avance.

 

[m@tix]

Re : Macro pour récupérer les infos de plusieurs classeurs

Re,

Après plusieurs tests, je sais d'où vient le problème ! Mais je ne sais pas le résoudre...
Pour deux fichiers de type "tableau", j'en ai un précisément comme celui de l'exemple (qui est bien pris en compte par la macro donc), et un autre sur lequel j'ai simplement rajouté deux boutons sur le haut de la feuille associés à une macro (et là, ce fichier n'est plus pris en compte par la macro de "recapitulatif"). Dès que je supprime les boutons via Suppr., ça refonctionne avec "recapitulatif" ! Curieux non ?

Au besoin je peux vous fournir les deux fichiers en question. Perso, je ne vois pas comment résoudre le problème.. Une idée ?

 

[job75]

Re : Macro pour récupérer les infos de plusieurs classeurs

Bonjour m@tix, le fil,

Set r = .[E12:I500].SpecialCells(xlCellTypeConstants, 1)

recherche en effet des constantes numériques (les heures).

Peut-être que parfois il s'agit de valeurs texte.

Vérifiez avec une formule =ESTNUM(cellule)

Essayez alors avec :

Set r = .[E12:I500].SpecialCells(xlCellTypeConstants)

Mais attention, les cellules contenant le texte vide "" seront prises en compte...

A+

 

[m@tix]

Re : Macro pour récupérer les infos de plusieurs classeurs

Merci pour ta réponse job75 !

Je pense avoir trouvé comment résoudre le problème... En fait, la feuille où l'on récupère les données était protégée. Lorsque je rajoute la ligne .Unprotect dans la macro du fichier "recapitulatif", ça fonctionne !

Surprenant... ou pas selon toi ?

 

[job75]

Re : Macro pour récupérer les infos de plusieurs classeurs

Re,

En fait, la feuille où l'on récupère les données était protégée.
(...) Surprenant... ou pas selon toi ?

SpecialCells ne fonctionne pas sur une feuille protégée. Même chose que la touche F5 => Cellules.

A+

 

[m@tix]

Re : Macro pour récupérer les infos de plusieurs classeurs

Ca répond donc à mon interrogation !

Une fois encore, merci beaucoup.