Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

[erics83]

Bonjour,

J'ai de nombreux classeurs, qui contiennent environ 20 feuilles, pour l'instant j'ouvrais chaque classeur et mettais mon code : je boucle sur les feuilles, je compile les feuilles (elles ont les mêmes colonnes) en créant dans chaque classeur une feuille "Compilation" et en faisant :

Sub Compilation()


Dim DerligR1 As Long
Dim DerligR2 As Long
Dim MaPlage As Range
Dim DerniereLigne As Integer

Dim nb As Integer
Dim j As Integer
Dim i As Integer


nb = ActiveWorkbook.Worksheets.Count


For j = 1 To nb

 ActiveWorkbook.Worksheets(j).Tab.ColorIndex = 1
 Next

For j = 1 To nb

If Left(ActiveWorkbook.Worksheets(j).Name, 1) = "P" Then

    ActiveWorkbook.Worksheets(j).Select
    ActiveWorkbook.Worksheets(j).Tab.ColorIndex = 3
    
      
    With ActiveWorkbook.Worksheets(j)
    DerligR1 = .Range("a" & .Rows.Count).End(xlUp).Row
    Rows("2:" & DerligR1).Select
    Selection.Copy
    End With

    ActiveWorkbook.Sheets("Compilation").Select
    DerligR2 = Range("A300000").End(xlUp).Row + 1
    
    
    Range("A" & DerligR2).Select
 
    ActiveWorkbook.ActiveSheet.Paste

End If

Next j



MsgBox ("Fini")




End Sub

et ensuite, je copie colle les valeurs de "compilation" dans un classeur "Synthèse".

En fait, je cherche pour mon classeur "synthèse" : qu'il boucle sur tous les classeurs (contenus dans le même dossier, donc thisworkbook.path), qu'il fasse la macro ci dessus (=boucle sur les feuilles et compilation des données) et colle dans "synthèse".....


Un petite aide ?

En vous remerciant,

 

[néné06]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Re,
je pense que les erreurs proviennent de l'architecture de tes feuilles ?
Changer:
numcol = Sheets(fe).Range("A1").End(xlToRight).Column 'rechercher la derniere colonne écrite en ligne 1"
Par:
numcol = Sheets(fe).UsedRange.Columns.Count ' calcul le nombre de colonnes utilisées dans la plage de la feuille

A+

René

 

[erics83]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Merci René,

J'ai fait la modification que tu m'as dit (post16), j'ai créé un dossier dans lequel j'ai mis les fichiers joints de mon précédent post (j'ai gardé l'extension .xlsx, et n'ai pas modifié dans synthèse), j'ai changé d'ordi, et.....cela ne fonctionne pas.... : je lance le code et excel "s'éteint" (tous les classeurs sont fermés...).

j'ai aussi essayé ton code avec un de mes classeurs (~28 feuilles par classeur), en fait, il ne prend que 3 feuilles, alors que lorsque j'ai mis un stop après "nb", il me comptait bien 28 feuilles...., donc.....je ne comprends plus rien ....lol

Merci pour ton aide,
Eric

 

[néné06]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Re,

Tu as essayé du pas à pas et regarder la progression ?
Je ne sais pas ce qui cloche!

Un appel aux autres membres du Forum, pour tester les programmes déposés dans le post #14
D'avance, merci pour votre contribution!

René

 

[erics83]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Merci René,

Premièrement, j'ai honte....en effet, dans les fichiers du post 14, j'avais mis "synthèse-1", et donc dans ton code, il fait référence à "Synthèse", donc excel se coupait......, donc ton code fonctionne bien, mais......

Il y a quand même un problème avec le nombre de feuilles : le code s'arrête à 3 feuilles (alors que nb compte le bon nombre...). car après la correction de mon erreur avec le nom du classeur, j'ai refait un test avec mon classeur, et il s'arrête à la 3ème feuille....

J'ai donc rajouté une feuille supplémentaire dans les "classeurs tests", et......idem, le code s'arrête à la 3ème feuille, en fait il s'arrête même au classeur1....

Merci pour ton aide, merci au forum aussi (car je viens de voir que tu avais lancé un appel...)

Merci,

 

[néné06]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Re,

J'ai trouvé les erreurs, mais il se fait tard et je les corrigerais demain!
Pas d'inquiétude!

A+

René

 

[erics83]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Merci René,

J'ai mis les "classeurs tests" à jour car comme je l'ai dit, toutes les feuilles ont la même structure (même nombre de colonne), et là, quand j'avais rajouté des feuilles, j'avais aussi rajouté trop de colonnes....


J'ai aussi mis dans "synthèse" (en G:I pour l'exemple) l'idéal que je souhaitais aussi avoir => par ligne : le nom du classeur et nom de la feuille....


Merci pour ton aide et à très bientôt,
Eric

 

[erics83]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Bonsoir René, Bonsoir le Forum

A force de chercher, je pense avoir trouvé une solution, pas très orthodoxe car en fait on créé un nombre assez important de feuilles dans le classeur, c'est long, mais.....avec mes faibles connaissance, je fais de mon mieux.....

C'est un code adapté d'un tuto de JB, mais il bloque à un moment : une feuille d'un de mes classeur porte le même nom qu'un feuille d'un autre classeur, et donc, j'ai un message bloquant....et le code s'arrête.....:

Sub consolider()
Dim DerniereLigne As Integer
Dim i As Integer
Dim DerligR1 As Long
Dim DerligR2 As Long
Dim MaPlage As Range


Dim nb As Integer
Dim j As Integer

Application.DisplayAlerts = False
  If Sheets.Count > 1 Then
    Sheets("Accueil").Move before:=Sheets(1)
    Sheets(2).Select
    For i = 2 To Sheets.Count
      ActiveSheet.Delete
    Next i
  End If

  ' Ce classeur et les classeurs à consolider sont dans le même répertoire
  répertoire = ThisWorkbook.Path
  Set classeurMaitre = ActiveWorkbook
  'supprimer
  compteur = 1
  nf = Dir(répertoire & "\*.xls") ' premier fichier
  'nff = répertoire & "\" & nf
  Do While nf <> ""
    If nf <> classeurMaitre.Name Then
    nff = répertoire & "\" & nf
      Workbooks.Open Filename:=nff
      For k = 1 To Sheets.Count
        numcol = Workbooks(nf).Sheets(k).UsedRange.Columns.Count
        numlign = Workbooks(nf).Sheets(k).UsedRange.Rows.Count
        For i = 2 To numlign
            Workbooks(nf).Sheets(k).Cells(i, numcol + 1) = Workbooks(nf).Name
            Workbooks(nf).Sheets(k).Cells(i, numcol + 2) = Workbooks(nf).Sheets(k).Name
        Next i
        
                Workbooks(nf).Sheets(k).Copy After:=classeurMaitre.Sheets(classeurMaitre.Sheets.Count)
                classeurMaitre.Sheets(classeurMaitre.Sheets.Count).Name = Workbooks(nf).Sheets(k).Name
        
        
        compteur = compteur + 1
      Next k
      Workbooks(nf).Close False
    End If
    nf = Dir
  Loop
 
nb = ActiveWorkbook.Worksheets.Count

For j = 1 To nb

 ActiveWorkbook.Worksheets(j).Tab.ColorIndex = 1
 Next

For j = 1 To nb

If Left(ActiveWorkbook.Worksheets(j).Name, 1) = "P" Then

    ActiveWorkbook.Worksheets(j).Select
    ActiveWorkbook.Worksheets(j).Tab.ColorIndex = 3
   
     
    With ActiveWorkbook.Worksheets(j)
    DerligR1 = .Range("a" & .Rows.Count).End(xlUp).Row
    Rows("2:" & DerligR1).Select
    Selection.Copy
    End With

    ActiveWorkbook.Sheets("Accueil").Select
    DerligR2 = Range("A300000").End(xlUp).Row + 1
   
   
    Range("A" & DerligR2).Select
 
    ActiveWorkbook.ActiveSheet.Paste

End If

Next j

Application.DisplayAlerts = False
  If Sheets.Count > 1 Then
    Sheets("Accueil").Move before:=Sheets(1)
    Sheets(2).Select
    For i = 2 To Sheets.Count
      ActiveSheet.Delete
    Next i
  End If

MsgBox ("Fini")

End Sub

Donc soit il y a une autre solution, soit il faudrait que je copie-colle les feuilles de chaque classeur, les compile dans "Accueil" ferme le classeur, supprimer les feuilles crées, bref, assez long et vraiment pas orthodoxe....

Donc, si vous avez une idée et/ou autre solution, suis preneur.....lol
A+
Eric

 

[néné06]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

Bonjour Eric, le Forum

Je pense que cette mouture te conviendra ?

Essayes et dis-nous

A+

René

 

[erics83]

Re : Boucle sur répertoire, exécution macro sur boucle sur feuille, récupérer données

SUPER !!!!! super merci René !!

Je viens de tester sur mon "gros" fichier, tout fonctionne parfaitement, et avec le résultat attendu !!!

Je vais prendre le temps d'étudier ton nouveau code, pour voir où tu as fait des modifications et/ou amélioration, mais déjà : MERCI !!!

Eric,