Consolider plusieurs feuilles simultanément [Partie 2]

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention -
hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention - 18 oct. 2017 à 22:39

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

Afficher la suite

A voir également:

Consolider plusieurs feuilles simultanément [Partie 2]
Supercopier 2 - Télécharger - Gestion de fichiers
Consolider plusieurs feuilles excel - Guide
Comment faire un livret avec des feuilles a4 - Guide
2 ecran pc - Guide
Bruler des feuilles de laurier - Guide

15 réponses

Réponse 1 / 15

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention Ambassadeur 1 584

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...

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

Effectivement oui, je m'en vais modifier le code de suite.

Merci de ta proposition.

Réponse 2 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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)

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

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

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21 > yg_be Messages postés 23541 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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584 > hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention

plutôt, sans doute:

src(t)=Feuil4.cells(t,"N").Value
Next t

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21 > yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention

ok je teste ça et je reviens vers vous

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21 > yg_be Messages postés 23541 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

Réponse 3 / 15

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention Ambassadeur 1 584

essayons d'éliminer selection:

Sheets("Consolidation").Range("A1").Consolidate

et c'était bien cstr:

ContenuSrc = ContenuSrc & "|" & cstr(t) & ":" & src(t)

Réponse 4 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

quelle erreur sur la ligne consolidate?
tout au début, quand tu faisais

Arg1 = Range("A13").Value

, que contenait exactement A13?

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

Posez votre question

Réponse 5 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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)

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

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"

Réponse 6 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

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.

Réponse 7 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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 ?

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

"non plus"= quelle message d'erreur?
que contient ContenuSrc?

je pense aussi qu'on essaie d'automatiser l'ajout des Arg.

Réponse 8 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

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

Réponse 9 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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)

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

sans doute Sheets("Consolidation") et pas Sheets("Consolidate")

Réponse 10 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

que montre le msgbox?

Réponse 11 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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.

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

et ainsi?

src(t) = "'D:\Docs de Travail\[DMR2325.xlsx]" & Feuil4.Cells(t, 14).Value & "'!R12C13:R67C20"

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21 > yg_be Messages postés 23541 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,

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

je n'ai plus d'autres idées, :-(

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21 > yg_be Messages postés 23541 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.

Réponse 12 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

Ça n'inspire personne a ce que je vois :(.

Mon problème restera t-il sans solution ?

Réponse 13 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

Un petit UP car je coince toujours

Réponse 14 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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 ?

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

Array est une fonction qui crée et renvoie un tableau unidimensionnel.

Réponse 15 / 15

hmcirta Messages postés 239 Date d'inscription Statut Membre Dernière intervention 21

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

Discussions similaires

Excel formule : savoir si une cellule contient partie texte

Pokémon X et Y : comment effacer une partie ?

Paris multiple : explications de 2/3, 3/4, 2/4, 2/5, etc.

Voxbone ? qui est-ce ?

Votre réponse

Discussions similaires