Agrèger données de plusieurs classeurs avec VBA

MADO

XLDnaute Junior
Bonsoir les amis, i'm back !
J'aurais besoin de votre aide pour une macro, je ne maitrise pas VBA.
J'ai 5 dossiers :
- dossier 1 : 23 classeurs et 8 feuilles (formats et noms de feuilles identiques) ;
- dossier 2 : 23 classeurs et 11 feuilles (formats et noms de feuilles identiques) ;
- dossier 3 : 23 classeurs et 6 feuilles (formats et noms de feuilles identiques) ;
- dossier 4 : 23 classeurs et 6 feuilles (formats et noms de feuilles identiques) ;
- dossier 5 : 23 classeurs et 18 feuilles (formats et noms de feuilles identiques).

Objectif : Créer un dossier avec 5 classeurs et une macro qui :

- classeur 1 : agrège les données du dossier 1 suivant le même format et nombre de feuilles ;
- classeur 2 : agrège les données du dossier 2 suivant le même format nombre de feuilles;
- classeur 3 : agrège les données du dossier 2 suivant le même format nombre de feuilles;
- classeur 4 : agrège les données du dossier 4 suivant le même format nombre de feuilles ;
- classeur 5 : agrège les données du dossier 5 suivant le même format nombre de feuilles.

Je vous remercie d'avance et reste disponible pour d'éventuels compléments d'information.
 

Robert

XLDnaute Barbatruc
Repose en paix
Bonjour Mado, bonjour le forum,

Bon il est vrai que je n'avais pas ouvert tes fichiers et que je n'avais pas non plus compris le mot agréger. Alors pour éviter de nouvelles erreurs pourrais-tu me préciser avec moultes détails ce dont tu as besoin.
L'idéal, pour que mon petit cerveau comprenne, serait deux fichiers d'un même dossier et le fichier Agreg final avec des précisions.
On sait maintenant que l'ouverture , la récupération dans un fichier final, la fermeture des classeur par dossier fonctionne. Il ne reste plus qu'à finaliser...
 

MADO

XLDnaute Junior
Bonjour Robert
Agréger veut dire sommer, additionner etc.

Ex : Dans le dossier Agreg, j'ai crée un fichier Agreg_Dossier1 avec mon besoin dans les cellules B4 de chaque feuille.
La même opération devra être faite pour l'ensemble des cellules et des feuilles du Dossier1 et ainsi de suite pour les 4 autres dossiers.
Au finish, je dois avoir 4 fichiers (Agreg_Dossier1, Agreg_Dossier2, Agreg_Dossier3, Agreg_Dossier4 et Agreg_Dossier5) ayant respectivement le même format que celui contenu dans leurs dossiers d'origine et faisant la somme dans chaque feuille des mêmes cellules des fichiers du dossier concerné.
En bonus, s'il est possible d'avoir les chemins des cellules contenues dans la formule, je ne me plaindrais pas lol.
Passe une bonne journée.
 

Pièces jointes

  • Archive 2.zip
    4.5 MB · Affichages: 51
Dernière édition:

Robert

XLDnaute Barbatruc
Repose en paix
Bonjour Mado, bonjour le forum,

Désolé pour le retard mais beaucoup de boulot en ce moment... La plage, les apéros, et bientôt les J.O., bref, je ne sais plus où donner de l'athlète...

Le nouveau code que je te propose (non testé) implique que les différents fichiers d'un même dossier aient exactement la même structure...
Le code :

VB:
Sub Macro1()
Dim CO As Workbook 'décalre la variable CO (Classeur d'Origine)
Dim CH As String 'déclare la variable CH (CHemin d'accès)
Dim DS(1 To 5) As String 'déclare la variable DS (DossierS)
Dim D As Byte 'déclare la variable D (Dossier)
Dim NBO As Byte 'déclare la variable NBO (NomBre d'Onglets)
Dim CA As Workbook 'déclare la variable CA (Classeur Agrégation)
Dim F As String 'déclare la variable F (Fichier)
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim DEST As Range 'déclare la variable DEST (cellule de destination)
Dim TS As Variant 'déclare la variable TS (Tableau Source)
Dim TD As Variant 'déclare la variable TD (Tableau Destination)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Integer 'déclare la variable J (incrément)

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set CO = ThisWorkbook 'définit le classeur d'Origine
CH = CO.Path & "/" 'définit le chemin d'accès CH
DS(1) = "'/Users/Name/Desktop/Dossier1/" 'définit la dossier DS(1) [à adapter !]
DS(2) = "'/Users/Nameg/Desktop/Dossier2/" 'définit la dossier DS(2) [à adapter !]
DS(3) = "'/Users/Name/Desktop/Dossier3/" 'définit la dossier DS(3) [à adapter !]
DS(4) = "'/Users/Name/Desktop/Dossier4/" 'définit la dossier DS(4) [à adapter !]
DS(5) = "'/Users/Name/Desktop/Dossier5/" 'définit la dossier DS(5) [à adapter !]
For D = 1 To 5 'boucle sur les 5 dossiers D
Select Case D 'agit en fonction du dossier
    Case 1 'dossier 1
        NBO = 8 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
    Case 2 'dossier 2
        NBO = 11 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
    Case 3, 4 'dossier 3 et 4
        NBO = 6 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
    Case 5 'dossier 5
        NBO = 18 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
End Select 'fin de l'action en fonction du dossier
Workbooks.Add 'ajoute un nouveau classeur vierge
'enregistre le nouveau classeur dans le même dossier que le classeur d'origine CO avec
'comme nom ""Agreg_Dossier_D.xls"  ou "D" est le numéro de dossier de la boucle [extension à adapter !]
ActiveWorkbook.SaveAs (CH & "Agreg_Dossier_" & D & ".xlsx")
Set CA = ActiveWorkbook  'définit le classer des agrégations CA
F = Dir(DS(D) & "*.xlsx") 'définit le premier fichier du dossier DS(D) [extension a adapter !]
Do While F <> "" 'exécute en boucle tant qu'il existe des fichiers
    Workbooks.Open (DS(D) & F) 'ouvre le fichier
    Set CS = ActiveWorkbook 'définit le classeur source CS
    For O = 1 To NBO 'boucle 2 : sur tous les onglets O
        Set OS = CS.Sheets(O) 'définit l'onglet source OS du classeur source CS
        TS = OS.Range("A4").CurrentRegion 'définit le tableau TS
        Set OD = CA.Sheets(O) 'définit l'onglet destination OD du classeur des agrégations CA
        TD = OD.Range("A4").CurrentRegion 'définit le tableau TD
        For I = 1 To UBound(TD, 1) 'boucle 1 : sur toutes les lignes I du tableau destination TD
            For J = 2 To UBound(TD, 2) 'boucle 2 : sur toutes les colonnes J du tableau destination TD (en partant de la seconde)
                TD(I, J) = TD(I, J) + TS(I, J) 'définit la donnée ligne I colonne J de TD (ajoute la valeur de TS)
            Next J 'prochaine colonne de la boucle 2
        Next I 'prochaine ligne de la boucle 1
        OD.Range("A4").Resize(UBound(TD, 1), UBound(TD, 2)).Value = TD ' remplace les valeurs par le tableau destination TD
        Erase TS: Erase TD 'efface les tableaux TS et TD
    Next O 'prochain onglet de la boucle 2
    CS.Close False 'ferme le classeur source CS sans enregistrer les modifications
    F = Dir 'définit le prochain fichier du dossier DS(D)
Loop 'boucle
CA.Close True 'ferme le classeur des agrégation en enregistrant les modifications
Next D 'prochain dossier D de la boucle 1
CO.Close False 'ferme le classeur d'origine sans enregistrer
Application.SheetsInNewWorkbook = 3 'réinitialise le nombre d'onglets à la création d'un nouveau classeur (3 par défaut mais à adapter)
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
End Sub
 

MADO

XLDnaute Junior
Bonjour Robert
Je me suis dis que t'es busy !

Qu'est-ce que tu entend par même structure ?
- Est-ce que tous les fichiers (ainsi que les onglets) du dossier doivent avoir le même nombre de lignes et de colonnes ?
- Est-ce que toutes les cellules de tous les fichiers doivent contenir des chiffres ?

Parce que j'ai testé le code mais y a un débogage à ce niveau
For I = 1 To UBound(TD, 1)

Merci d'avoir trouvé du temps à me consacrer malgré ton calendrier très chargé.
 

Robert

XLDnaute Barbatruc
Repose en paix
Bonjour Mado, bonjour le forum,

Arf ! En effet la première fois TD est vide... D'où le bug...
Mais il faut que toutes les données dans tous les onglets de tous les fichiers commencent en A4 sinon ça ne marchera pas !
Essaie ce nouveau code :
VB:
Dim CO As Workbook 'décalre la variable CO (Classeur d'Origine)
Dim CH As String 'déclare la variable CH (CHemin d'accès)
Dim DS(1 To 5) As String 'déclare la variable DS (DossierS)
Dim D As Byte 'déclare la variable D (Dossier)
Dim NBO As Byte 'déclare la variable NBO (NomBre d'Onglets)
Dim CA As Workbook 'déclare la variable CA (Classeur Agrégation)
Dim F As String 'déclare la variable F (Fichier)
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim DEST As Range 'déclare la variable DEST (cellule de destination)
Dim TS As Variant 'déclare la variable TS (Tableau Source)
Dim TD As Variant 'déclare la variable TD (Tableau Destination)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Integer 'déclare la variable J (incrément)

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set CO = ThisWorkbook 'définit le classeur d'Origine
CH = CO.Path & "/" 'définit le chemin d'accès CH
DS(1) = "'/Users/Name/Desktop/Dossier1/" 'définit la dossier DS(1) [à adapter !]
DS(2) = "'/Users/Nameg/Desktop/Dossier2/" 'définit la dossier DS(2) [à adapter !]
DS(3) = "'/Users/Name/Desktop/Dossier3/" 'définit la dossier DS(3) [à adapter !]
DS(4) = "'/Users/Name/Desktop/Dossier4/" 'définit la dossier DS(4) [à adapter !]
DS(5) = "'/Users/Name/Desktop/Dossier5/" 'définit la dossier DS(5) [à adapter !]
For D = 1 To 5 'boucle sur les 5 dossiers D
Select Case D 'agit en fonction du dossier
    Case 1 'dossier 1
        NBO = 8 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
    Case 2 'dossier 2
            NBO = 11 'définit le nombre d'onglets
       Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
   Case 3, 4 'dossier 3 et 4
        NBO = 6 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
   Case 5 'dossier 5
        NBO = 18 'définit le nombre d'onglets
        Application.SheetsInNewWorkbook = NBO 'définit le nombre d'onglets à la création d'un nouveau classeur
End Select 'fin de l'action en fonction du dossier
Workbooks.Add 'ajoute un nouveau classeur vierge
'enregistre le nouveau classeur dans le même dossier que le classeur d'origine CO avec
'comme nom ""Agreg_Dossier_D.xls"  ou "D" est le numéro de dossier de la boucle [extension à adapter !]
ActiveWorkbook.SaveAs (CH & "Agreg_Dossier_" & D & ".xlsx")
Set CA = ActiveWorkbook  'définit le classer des agrégations CA
F = Dir(DS(D) & "*.xlsx") 'définit le premier fichier du dossier DS(D) [extension a adapter !]
Do While F <> "" 'exécute en boucle tant qu'il existe des fichiers
    Workbooks.Open (DS(D) & F) 'ouvre le fichier
    Set CS = ActiveWorkbook 'définit le classeur source CS
    For O = 1 To NBO 'boucle 2 : sur tous les onglets O
        Set OS = CS.Sheets(O) 'définit l'onglet source OS du classeur source CS
        TS = OS.Range("A4").CurrentRegion 'définit le tableau TS
        Set OD = CA.Sheets(O) 'définit l'onglet destination OD du classeur des agrégations CA
        If OD.Range("A4").Value = "" Then 'condition : si la cellule A4 de l'onglet destination est vide
            OD.Range("A4").Resize(UBound(TS, 1), UBound(TS, 2)).Value = TS 'renvoie dans A4 redimensionnée le tableau TS
        Else 'sinon
            TD = OD.Range("A4").CurrentRegion 'définit le tableau TD
            For I = 1 To UBound(TD, 1) 'boucle 1 : sur toutes les lignes I du tableau destination TD
                For J = 2 To UBound(TD, 2) 'boucle 2 : sur toutes les colonnes J du tableau destination TD (en partant de la seconde)
                    If IsNumeric(TS(I, J)) = True And IsNumeric(TD(I, J)) = True Then 'condition : si les deux données sont numériques
                        TD(I, J) = TD(I, J) + TS(I, J) 'définit la donnée ligne I colonne J de TD (ajoute la valeur de TS)
                    End If 'fin de la condition
                Next J 'prochaine colonne de la boucle 2
            Next I 'prochaine ligne de la boucle 1
            OD.Range("A4").Resize(UBound(TD, 1), UBound(TD, 2)).Value = TD ' remplace les valeurs par le tableau destination TD
        End If
        Erase TS: Erase TD 'efface les tableaux TS et TD
    Next O 'prochain onglet de la boucle 2
    CS.Close False 'ferme le classeur source CS sans enregistrer les modifications
    F = Dir 'définit le prochain fichier du dossier DS(D)
Loop 'boucle
CA.Close True 'ferme le classeur des agrégation en enregistrant les modifications
Next D 'prochain dossier D de la boucle 1
CO.Close False 'ferme le classeur d'origine sans enregistrer
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
Application.SheetsInNewWorkbook = 3 'réinitialise le nombre d'onglets à la création d'un nouveau classeur (3 par défaut mais à adapter)
End Sub

Après, si ça ne marche toujours pas, il me faudra deux vrais fichiers d'un même dossier. Même s'ils contiennent des données confidentielles !... Sinon on ne s'en sortira pas...
Bon, je retourne au boulot !...
 

MADO

XLDnaute Junior
Bonsoir Robert
J'ai testé le code mais il y avait un débogage au niveau de Erase TS: Erase TD 'efface les tableaux TS et TD.
Je l'ai ensuite modifié comme suit et ça a marché :
Erase TS : 'efface les tableaux TS
'Erase TD

Je te remercie du fonds du cœur de m'avoir aidée à améliorer mon travail et gagner du temps.
Merci encore une fois, take care.
 
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
312 859
Messages
2 092 934
Membres
105 568
dernier inscrit
florichou