Consolider plusieurs feuilles simultanément [Partie 2]

Résolu
hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   -  
hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   -
Bonjour tout le monde

Mon sujet de même titre n'ayant inspiré personne a été fermé, je n'ai donc pas trouvé un autre moyen que de recréer un autre poste qui sera la suite du premier.Voici le lien du poste pour rappeler la question.
http://www.commentcamarche.net/forum/affich-31911493-consolider-plusieurs-feuilles-simultanement#p32318161
J'ai voulu rouvrir le sujet car je suis arrivé à une solution qui n'est sans doute pas la meilleure ni la plus pratique, mais ça a l'air de faire l'affaire. S'il y en a qui peuvent proposer mieux je suis bien sur preneur (et je voudrais bien qu'il y en ait).
Comme passer par "Données\Consolider" était une impasse je suis passé aux macros en utilisant la méthode 
Range.Consolidate Sources:=Array(Source), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Voici le code que j'ai fait (bien sur j'ai enregistré une macro que j'ai modifié ensuite, je ne fais que mes débuts en VBA). 

Sub Consolide7Feuilles()
'
'
Feuil4.Activate
Range("A1").Select

Arg1 = Range("A13").Value
Arg2 = Range("A14").Value
Arg3 = Range("A15").Value
Arg4 = Range("A16").Value
Arg5 = Range("A17").Value
Arg6 = Range("A18").Value
Arg7 = Range("A19").Value
'
Sheets("Consolidation").Select
Range("A1").Select

   Selection.Consolidate Sources:=Array(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7), _
        Function:=xlSum, TopRow:=True, LeftColumn _
        :=True, CreateLinks:=False

End Sub


Ce n'est pas grand-chose mais ça marche pour me faire gagner du temps, sur tout que j'ai plusieurs fichiers de ~70 à ~200 feuilles chacun.

Je reste ouvert a toutes vos propositions d’amélioration

15 réponses

Résumé de la discussion

La problématique porte sur la consolidation de données de plusieurs classeurs Excel en une seule feuille via VBA, en utilisant Range.Consolidate et un tableau de sources. Les échanges mettent en avant l'emploi de Sources:=Array(...) avec des chaînes complètes de référence, et les erreurs fréquentes comme 1004 ou 9 liées au chemin, à la syntaxe et aux séparateurs. D'autres propositions montrent comment éviter la sélection active, construire un ContenuSrc ou des tableaux dynamiques, et donner des exemples concrets de formules utilisant D:\Docs de Travail\[DMR2325.xlsx]DDFR05!R12C13:R67C20. Les contributions indiquent aussi qu'un nombre d'arguments variable pour Array peut faciliter le traitement de plusieurs feuilles et s'adapter facilement à une consolidation incluant sept feuilles ou davantage.

Généré automatiquement par IA
sur la base des meilleures réponses
  1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   Ambassadeur 1 588
     
    bonsoir, je te suggère:
    1) ajouter option explicit en début de module, et donc déclarer toutes tes variables.
    2) ne pas utiliser activate et select, donc: 
    
Arg1 = Feuil4.Range("A13").Value
Arg2 = Feuil4.Range("A14").Value
Arg3 = Feuil4.Range("A15").Value
Arg4 = Feuil4.Range("A16").Value
Arg5 = Feuil4.Range("A17").Value
Arg6 = Feuil4.Range("A18").Value
Arg7 = Feuil4.Range("A19").Value
'
Sheets("Consolidation").Range("A1").Consolidate ' etc...
    0
    1. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
       
      Effectivement oui, je m'en vais modifier le code de suite.

      Merci de ta proposition.
      0
  2. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    J'ai encore une petite question.

    Est-ce qu'il y a un moyen pour que le nombre d'arguments de: 

    Sources:=Array(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7)

    soit variable ? C'est a dire que je pourrai choisir le nombre de feuilles a consolider, (dans ce cas 7 feuilles sont consolidées)
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      par exemple: 
      dim src as variant
redim src (1 to 7)
src(1)=Feuil4.Range("A13").Value
'...
src(7)=Feuil4.Range("A19").Value
'...
Sources:=src
      0
    2. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23 > yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention  
       
      Merci yg_be pour ta reponse.

      Est-ce que je peux faire quelque chose comme ca : 

      dim src as variant
dim Nb as integer
Nb=Feuil4.Range("B1").Value ' Attribuer a Nb le nombre max de feuilles a consolider
redim src (1 to Nb)
Dim t as Integer
    For t =1 to Nb
    src(t)=Feuil4.cells(Nb.14).Value ' Avec les noms des feuilles listés dans la colonne N1:NX ou X est variable 
    Next i
'...
Sources:=src
      0
    3. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588 > hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention  
       
      plutôt, sans doute: 
      src(t)=Feuil4.cells(t,"N").Value
Next t
      0
    4. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23 > yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention  
       
      ok je teste ça et je reviens vers vous
      0
    5. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23 > yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention  
       
      j'ai testé avec 3 feuilles seulement et j’obtiens le message suivant:

      Erreur d’exécution '1004'

      La méthode Consolidate de la classe Range a échoué.

      avec : 
              Selection.Consolidate Sources:=src, Function:=xlSum, TopRow:=True, LeftColumn _
        :=True, CreateLinks:=False

      en surbrillance.


      Voici la modification effectuée 

      Sub Consolide7Feuilles()
'
RecupNomFeuilles
'
'
Feuil4.Activate
Range("A1").Select

Plage = Range("D5").Value
Set mc = Range(Plage)
Range("E5").Value = mc.Address(ReferenceStyle:=xlR1C1)

Dim src As Variant
Dim Nb As Integer
Nb = Feuil4.Range("B1").Value ' Attribuer a Nb le nombre max de feuilles a consolider
ReDim src(1 To Nb)
Dim t As Integer
    For t = 1 To Nb
    src(t) = Feuil4.Cells(t, 14).Value ' Avec les noms des feuilles listés dans la colonne N1:NX ou X est variable
    Next t
'...
        Selection.Consolidate Sources:=src, Function:=xlSum, TopRow:=True, LeftColumn _
        :=True, CreateLinks:=False

' Sources:=src
End Sub

      et; 

      Sub RecupNomFeuilles()
' Recuperer les noms de 7 feilles du classeur dans des cellules
Dim i As Integer
Dim FileDMR As String
Dim FileCon As String
Dim Sh As String

Workbooks("Consolidation-Test.xlsm").Activate
Feuil4.Select
FileCon = Range("A1").Value
FileDMR = Range("B4").Value
Sh = Range("B11").Value


'Workbooks.Open (FileDMR)
Workbooks(FileDMR).Activate
Sheets(Sh).Select
'Workbooks(FileCon).Activate


F = Excel.ActiveSheet.Index

    j = 5
       For i = F - 6 To F

          Workbooks(FileDMR).Activate
          Nom = Sheets(i).Name

         Workbooks("Consolidation-Test.xlsm").Activate
          Feuil4.Select
          Cells(j, 2).Value = Nom
      j = j + 1
      'Next j
      Next i

End Sub


      Le 2eme code ne récupère que les noms de 7 feuilles, il sera modifié de sorte qu'il récupère les noms de toutes les feuilles a consolider du 2eme classeur et qui seront mis dans une liste de choix.

      Je sais ça se complique un peu, mais c'est ce que je voudrais bien obtenir
      0
  3. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   Ambassadeur 1 588
     
    essayons d'éliminer selection: 
    Sheets("Consolidation").Range("A1").Consolidate

    et c'était bien cstr: 
    ContenuSrc = ContenuSrc & "|" & cstr(t) & ":" & src(t)
    0
  4. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Bonjour,

    Voici la modification effectuée 

        For t = 1 To Nb
    src(t) = Feuil4.Cells(t, 14).Value ' Avec les noms des feuilles listés dans la colonne N1:NX ou X est variable
    ContenuSrc = ContenuSrc & "|" & CStr(t) & ":" & src(t)
    Next t

MsgBox ContenuSrc
Range("A24") = ContenuSrc

        Sheets("consolidate").Range("A1").Consolidate Sources:=src, Function:=xlSum, _
        TopRow:=True, LeftColumn:=True, CreateLinks:=False


    Cette fois-ci la Msgbox me retourne ceci

    |1:DDFR01|2:DDFR02|3:DDFR03|4:DDFR04|5:DDFR05

    Mais ça débogue toujours sur la dernière ligne :(
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      quelle erreur sur la ligne consolidate?
      tout au début, quand tu faisais 
      Arg1 = Range("A13").Value
      , que contenait exactement A13?
      0

  5. Vous n’avez pas trouvé la réponse que vous recherchez ?

    Posez votre question
  6. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    C'est toujours Erreur 1004

    Arg1 contenait ceci

    Chemin\[Nom Du Fichier ou sont les données à consolider.Extension du fichier (xls ou xlsx)]Nom de La feuille t!PlageAConsolider
    Exemple:
    Arg1 contient D:\Docs de Travail\[DMR2325.xlsx]DDFR13!R12C13:R67C20

    Sur la feuille Feuil4 du fichier Consolidation-Test.xlsm
    • le chemin est saisie dans la cellule B3
    • le nom du fichier est saisie avec son extension dans la cellule B4
    • le nom de la feuille t où sont les données est saisie dans la cellule B5 à B11 (pour Arg1 à Arg7)
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      donc, quand tout fonctionnait, tu appelais "consolidate" avec "D:\Docs de Travail\[DMR2325.xlsx]DDFR13!R12C13:R67C20".
      et maintenant tu appelles avec simplement le nom de la feuille. cela me semble donc normal que cela ne fonctionne plus.
      peut-être ainsi, si jamais seul le nom de la feuille change pour chacun des arguments? 
      src(t) = "D:\Docs de Travail\[DMR2325.xlsx]" & Feuil4.Cells(t, 14).Value & "!R12C13:R67C20"
      0
  7. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Vraiment merci yg_be de ta patience.

    avec 
        Selection.Consolidate Sources:=Array(ContenuSrc), _
    Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

    ça donne Erreur 13

    et avec 
        Selection.Consolidate Sources:=Array(ContenuSrc), _
    Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

    ça donne Erreur 9
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      et avec ceci? 
      Sheets("consolidate").Range("A1").Consolidate Sources:=src, Function:=xlSum, _
        TopRow:=True, LeftColumn:=True, CreateLinks:=False

      ContenuSrc ne sert que pour faire le msgbox.
      0
  8. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Non plus

    A mon avis si on arrive a automatiser l'ajout des Arg1, Arg2, ... ,Argt on obtiendra un bon résultat.

    Qu'en penses-tu ?
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      "non plus"= quelle message d'erreur?
      que contient ContenuSrc?

      je pense aussi qu'on essaie d'automatiser l'ajout des Arg.
      0
  9. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Erreur 9
    ContenuSrc contient
    D:\Docs de Travail\[DMR2325.xlsx]DDFR05!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR04!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR03!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR02!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR01!R12C13:R67C20,
    Il y a une virgule et un espace de trop je pense.

    j'ai même essayé avec ça mais ça n'a rien donné 

    Sheets("consolidate").Range("A1").Consolidate Sources:=Array(D:\Docs de Travail\[DMR2325.xlsx]DDFR05!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR04!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR03!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR02!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR01!R12C13:R67C20), Function:=xlSum, _
        TopRow:=True, LeftColumn:=True, CreateLinks:=False


    Le message d'erreur dit :
    Erreur de compilation

    Attendu : séparateur de liste ou )

    et les ':' de 'D:\ .... ' sont en surbrillance

    NB : Toutes les lignes ici sauf la dernière sont sur la même ligne dans le code
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      je pense que tu as fais trop de changements dans le code: peux-tu montrer le code?
      bonne idée, mais: 
      Sheets("consolidate").Range("A1").Consolidate Sources:=Array("D:\Docs de Travail\[DMR2325.xlsx]DDFR05!R12C13:R67C20", "D:\Docs de Travail\[DMR2325.xlsx]DDFR04!R12C13:R67C20", "D:\Docs de Travail\[DMR2325.xlsx]DDFR03!R12C13:R67C20", "D:\Docs de Travail\[DMR2325.xlsx]DDFR02!R12C13:R67C20", "D:\Docs de Travail\[DMR2325.xlsx]DDFR01!R12C13:R67C20"), Function:=xlSum, _
        TopRow:=True, LeftColumn:=True, CreateLinks:=False
      0
  10. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    C'est justement ce que j'allais proposer :) 

    Option Explicit


Sub ConsolidetFeuilles()
'
'*******************************************************
'Declaration des variables
Dim Plage As String
Dim mc As Range
Dim src As Variant
Dim Nb As Integer
Dim t As Integer
Dim ContenuSrc As String
'*******************************************************
'Verifier si liste des noms sur la colonne N est rempli et evite l'execution de _la Sub "RecupNomFeuilles" si c'est le cas.
If Feuil4.Range("N1").Value <> "" Then GoTo 10
RecupNomFeuilles
10
'*******************************************************
'
'''''''Sheets("Feuil4").Range("A1").Select
Plage = Range("D5").Value
Set mc = Range(Plage)
Range("E5").Value = mc.Address(ReferenceStyle:=xlR1C1)
Nb = Feuil4.Range("B1").Value ' Attribuer a Nb le nombre max de feuilles a consolider
ReDim src(1 To Nb)
'*******************************************************
'Le code
    For t = 1 To Nb
        src(t) = "D:\Docs de Travail\[DMR2325.xlsx]" & Feuil4.Cells(t, 14).Value & "!R12C13:R67C20"
'     src(t) = Feuil4.Cells(t, 14).Value ' Avec les noms des feuilles listés dans la colonne N1:NX ou X est variable
'    ContenuSrc = ContenuSrc & "|" & CStr(t) & ":" & src(t)
    ContenuSrc = src(t) & ", " & ContenuSrc
    Next t

MsgBox ContenuSrc
Range("A24") = ContenuSrc

'       Selection.Consolidate Sources:=Array(ContenuSrc), _        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
       Sheets("consolidate").Range("A1").Consolidate Sources:=src, Function:=xlSum, _
TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
'       Sheets("consolidate").Range("A1").Consolidate Sources:=Array(D:\Docs de Travail\[DMR2325.xlsx]DDFR05!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR04!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR03!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR02!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR01!R12C13:R67C20), Function:=xlSum, _        TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub


    j'ai fait un peu de rangement X)
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      sans doute Sheets("Consolidation") et pas Sheets("Consolidate")
      0
  11. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Bonjour,

    En effet et apres correction j'ai 5 messages comme ceci

    Impossible d'ouvrir le fichier source de consolidation '[DMR2325.xlsx]DDFR01
    ...
    Impossible d'ouvrir le fichier source de consolidation '[DMR2325.xlsx]DDFR05
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      que montre le msgbox?
      0
  12. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    La même chose que précédemment

    D:\Docs de Travail\[DMR2325.xlsx]DDFR05!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR04!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR03!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR02!R12C13:R67C20, D:\Docs de Travail\[DMR2325.xlsx]DDFR01!R12C13:R67C20,

    NB : il y a un espace après la virgule.
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      et ainsi? 
      src(t) = "'D:\Docs de Travail\[DMR2325.xlsx]" & Feuil4.Cells(t, 14).Value & "'!R12C13:R67C20"
      0
      1. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23 > yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention  
         
        Toujours pareil avec une apostrophe au début


        'D:\Docs de Travail\[DMR2325.xlsx]DDFR05'!R12C13:R67C20, 'D:\Docs de Travail\[DMR2325.xlsx]DDFR04'!R12C13:R67C20, 'D:\Docs de Travail\[DMR2325.xlsx]DDFR03'!R12C13:R67C20, 'D:\Docs de Travail\[DMR2325.xlsx]DDFR02'!R12C13:R67C20, 'D:\Docs de Travail\[DMR2325.xlsx]DDFR01'!R12C13:R67C20,
        0
    2. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      je n'ai plus d'autres idées, :-(
      0
      1. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23 > yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention  
         
        Je pense qu'il n'existe pas de solution :'(

        J'ai essayé aussi pas mal de trucs sans succès

        Il ne me reste plus qu'a consolider par paquets de 7 feuilles et créer un autre fichier pour consolider 30 ou 31 feuilles.

        Un grand merci a toi yg_be pour le temps et la patience que tu m'as consacré.

        J'attendrai encore un peu s'il y aura quelqu'un d'autre pour m'aider, sinon un admin pourra fermer le sujet.
        0
  13. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Ça n'inspire personne a ce que je vois :(.

    Mon problème restera t-il sans solution ?
    0
  14. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Un petit UP car je coince toujours
    0
  15. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    Ce qui attire mon attention c'est que le "Array" de la ligne : 
    Selection.Consolidate Sources:=Array(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7)

    n'est pas déclare.

    Est-ce un tableau unidimensionnel ou est-ce autre chose ?
    0
    1. yg_be Messages postés 23437 Date d'inscription   Statut Contributeur Dernière intervention   1 588
       
      Array est une fonction qui crée et renvoie un tableau unidimensionnel.
      0
  16. hmcirta Messages postés 249 Date d'inscription   Statut Membre Dernière intervention   23
     
    je vais surement dire des bêtises, mais bon ...

    Est-ce que l'utilisation de ForEach peut résoudre le problème ? :P

    peut-on exploiter ce code ? 

    Private Sub Constant_demo_Click()
   Dim arr(5)
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript"    'String
   arr(2) = 100 		   'Number
   arr(3) = 2.45 		   'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
  
   msgbox("Value stored in Array index 0 : " & arr(0))
   msgbox("Value stored in Array index 1 : " & arr(1))
   msgbox("Value stored in Array index 2 : " & arr(2))
   msgbox("Value stored in Array index 3 : " & arr(3))
   msgbox("Value stored in Array index 4 : " & arr(4))
   msgbox("Value stored in Array index 5 : " & arr(5))
End Sub
    0

Discussions similaires

Excel formule : savoir si une cellule contient partie texte
plunderer007 -
francois -

14 réponses
Comment faire le signe au carré ² sur iPhone ?
Chloe0007 -
Jaks- -

5 réponses
Excel: Si contient *partie texte1* OU *partie de texte2 alors...
Fab -
Fab -

2 réponses
Formule Excel : "SI" une cellule contient un mot, alors ?
Tonio5933 -
Mike-31 -

9 réponses