[EXCEL-2010] Redispatcher des lignes dans différents tableurs, dans différents onglets

Version imprimable

02/04/2015, 11h05
Anarhim

3 pièce(s) jointe(s)

[EXCEL-2010] Redispatcher des lignes dans différents tableurs, dans différents onglets

Bonjour,
Avant tous, je tiens à vous remercier pour l'aide que vous m'apporterez.

Etant utilisateur courant de macro mais ne sachant que les trouver et potentiellement les modifier pour qu'elles fassent ce que je souhaite, je suis aujourd'hui confronté à un problème plus complexe qu'a l'accoutumé.
Je met en place un système de gestion d'équipement sur mon lieu professionnel et pour cela, un opérateur est chargé de scanner les codes-barres des équipements dans une base de donnée et en fin de journée, une macro (activé par un bouton), s'occupera de dispatcher les informations scannées en fonction de plusieurs classeurs dans les différents onglets associés.
- Chaque classeur est nommé avec une suite de trois lettres, correspondant à la catégorie de l'équipement. Ces trois lettres sont les premières présentes sur chaque code-barre (Ex : "ARI", "MAS").
- Chaque classeur comporte plusieurs onglets chacun nommé avec la référence complète de l'équipement (Ex : "ARIAAA0001", "ARIAAA0002").

Ainsi, la macro doit comprendre une boucle permettant de :
- sélectionner le classeur (nommé dans la colonne D de la BDD)
- sélectionner l'onglet correspondant dans le classeur (nommé en colonne C de la BDD)
- insérer l'information en haut de tableur (pour créer une chronologie inversé)
- puis de refaire la manipulation jusqu'à ce que la dernière ligne possédant une information soit traiter dans la BDD
- Ensuite, la BDD doit être sauvegardé dans un nouveau classeur portant la date du jour (pour créer une archive ; je n'ai pas encore cherché cette partie car il s'agit d'un plus et non d'une obligation mais je pense que je devrais pouvoir la trouver le moment voulu)
- Pour finir, la BDD est vidé dans son intégralité, prête à être de nouveau rempli de lendemain (je possède déjà la macro pour cela).

A ce jour, je possède une macro me permettant de dispatcher les informations de la BDD dans différents onglets présent dans le même classeur mais je n'arrive pas à faire de même dans les onglets d'autres classeurs (je vous l'ai laissé dans le classeur BDD).

Le but final étant de possèder un historique complet de tout ce qui est fait avec un même équipement.

Je vous ai mis en pièce jointe la BDD et deux tableurs, représentant chacun une catégorie. Des informations ont été rentrées dans la BDD afin de vous donner une idée de ce à quoi le système ressemble.

Merci d'avance pour l'aide que vous m'apporterez.

Voilà, la problématique de l'archivage est quasiment réglée (reste le reste...). La macro est simple par contre je ne comprend pas pourquoi l'archive s'enregistre à côté de la BDD et pas au chemin indiqué.
Code:

1 2 3 4 5 6 7 Sub Achiver() 'copie sauvegarde classeur Dim nom As String Dim Chemin As String, Fichier As String Chemin = "C:\Users\XXXX\Desktop\Gestion\Archivage" nom = Year(Date) & "_" & Month(Date) & "_" & Day(Date) & "_" & Hour(Time) & "h" & "_" & Minute(Time) & "min" ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & nom & ".xlsm" End Sub
Une idée ?

Bonjour, et comme ça ?

Code:

1
2
 
ActiveWorkbook.SaveCopyAs chemin & "\" & nom & ".xlsm"

02/04/2015, 12h15
Anarhim

Merci beaucoup.

La fonction d'archivage est prête, celle pour remettre le tableau à zéro aussi, ne manque que le morceau très compliqué qui me dépasse amplement.

Bon, j'arrive à dispatcher les informations de ma base de donnée dans les onglets dont le nom est présent en colonne D mais je n'arrive pas à faire pareil vers d'autres classeurs dont le nom est présent en colonne et les feuilles présentes en colonne C.

Voici le code pour dispatcher les informations dans les différentes feuille du même classeur.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Sub Copier_dans_l_onglet()
Dim WsS As Worksheet, WsC As Worksheet
Dim FinalRow As Long
Dim FinalCol As Integer
Dim NomFeuille As String
    Set WsS = Worksheets("BDD")
    FinalRow = WsS.Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To FinalRow
        NomFeuille = WsS.Cells(x, 4).Value
        If FeuilleExiste(NomFeuille) Then
            Application.ScreenUpdating = False
            FinalCol = WsS.Cells(x, Columns.Count).End(xlToLeft).Column
            WsS.Range(Cells(x, 1), Cells(x, FinalCol)).Copy
            Set WsC = Worksheets(WsS.Cells(x, 4).Value)
            With WsC
                .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End With
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
        End If
    Next x
    Set WsC = Nothing: Set WsS = Nothing
End Sub
 
 
Function FeuilleExiste(NomFeuille As String) As Boolean
Dim Ws As Worksheet
   FeuilleExiste = False
   For Each Ws In ActiveWorkbook.Worksheets
      If Ws.Name = NomFeuille Then
         FeuilleExiste = True
      End If
   Next
End Function

D'un autre côté, j'arrive à créer une boucle pour ouvrir le classeur MAS.xlsm par exemple mais je n'arrive pas à insérer cette boucle dans la macro du dessus de manière à ce que la recherche de la feuille s'effectue dans le classeur souhaité et pas dans le classeur de la base de donnée.

Avez-vous une idée ?

02/04/2015, 16h26
Aposto

Bonjour,

Citation:

sélectionner le classeur (nommé dans la colonne D de la BDD)

Citation:

Bon, j'arrive à dispatcher les informations de ma base de donnée dans les onglets dont le nom est présent en colonne D mais je n'arrive pas à faire pareil vers d'autres classeurs dont le nom est présent en colonne et les feuilles présentes en colonne C.

j'ai pas compris un truc, dans ta BDD tu as dans ta colonne D, a la fois des noms d'onglets(feuilles) et des noms de classeurs ?
02/04/2015, 16h36
Anarhim

C'est vrai que c'est obscure.
Je m'explique. J'arrive à dispatcher des informations de la BDD vers d'autres onglets que je nomme comme mes classeurs (les onglets sont nommés ARI, MAS et les classeurs aussi). La phrase que vous avez cité était pour le test. Donc pour ce cas, effectivement le nom de mes onglets étaient les mêmes que ceux de mes classeurs et se trouve en colonne D. Mais j'aurais pu faire le même test avec la colonne C et nommés mes onglets comme les onglets de mes autres classeurs.
Ce qui importe c'est surtout que je peux récupérer l'information d'une colonne pour transférer la ligne dans un onglet nommé avec cette information.

Maintenant, je cherche à ajouter une étape entre les deux, permettant d'ouvrir un classeur nommé avec l'information de la colonne D, et transférer la ligne dans l'onglet nommé avec l'information de la colonne C.

Ai-je été plus clair ?
02/04/2015, 17h32
Invité

Bonjour,
si j'ai bien compris,le classeur BDD et utilisateurs.xlsm contient toute les données de la journée?
il est vidé de son contenue après traitement?
la chronologie es descendante de la première date à la dernière?
la première ligne contient MAS en D2 elle doit être sauvegardé en ligne 2 du classeur MAS.xlsm dans l'onglet MASAAA0001?
si l’onglet MASAAA0001 n'existe pas dans le classeur il est créé selon le modèle vierge?
02/04/2015, 17h56
Anarhim

Bonjour,

Citation:

si j'ai bien compris,le classeur BDD et utilisateurs.xlsm contient toute les données de la journée?

Alors, la feuille BDD contient effectivement toutes les données de la journée. Les autres onglets permettent uniquement d'intégrer les noms des gens etc. Ils n'interviennent pas dans la macro finale.

Citation:

il est vidé de son contenue après traitement?

J'avais effectivement en tête de le vider après traitement afin que la BDD soit vierge à chaque prise de poste. Les informations ayant été copier-coller dans les bons emplacements et supprimer de la BDD ou couper-coller directement mais dans ce cas, il faudra insérer de nouvelles lignes sur la base des autres afin que la BDD contiennent toujours un stocks de lignes permettant à l'opérateur de faire sa journée sans rencontrer de problématique lié à un manque de formule, de mise en forme etc. Il faut se dire que l'utilisateur sera incapable de gérer une quelconque problématique informatique. Il faut qu'il n'ait à réaliser que des étapes très simples.

Citation:

la chronologie es descendante de la première date à la dernière?

Dans la BDD, la chronologie est effectivement descendante de la première à la dernière ligne. Chaque opération permet l'enregistrement de la date et heure et minutes de l'opération pour conserver une traçabilité maximale. En revanche, j'aimerai que les lignes soient transféré sous forme de chronologie inversé dans les différents onglets des différents classeurs afin que le dernier événement associé à un équipement correspondent à la première ligne du tableau.

Citation:

la première ligne contient MAS en D2 elle doit être sauvegardé en ligne 2 du classeur MAS.xlsm dans l'onglet MASAAA0001?

Dans le cas de la première ligne, la cellule D2 contient MAS. Cette ligne doit donc être transféré dans le classeur MAS.xlsm. La feuille du classeur MAS.xlsm est défini par la cellule C2, soit MASAAA0001. Dans cette feuille, la première ligne du tableau commence en ligne 3. Dans cet exemple, il faut donc que la ligne 2 de la BDD soit transféré dans le classeur MAS, à la feuille MASAAA0001, en ligne 3. Et ainsi de suite jusqu'à ce que la BDD ait été traitée.

Citation:

si l’onglet MASAAA0001 n'existe pas dans le classeur il est créé selon le modèle vierge?

Dans l'immédiat, j'avais une macro pour créer une nouvelle feuille selon le modèle vierge et une autre permettant de renommer automatiquement le nom de la feuille selon la cellule A1 de cette dernière. Si vous trouvez une solution pour que les nouvelles feuilles soient insérer automatiquement en suivant la règle AAA0001, AAA0002, AAA0003 etc. Une fois atteint AAA9999, la feuille suivante doit se nommer AAB0001 etc etc (c'est un simmple code d'immatriculation en fait).
Dans tous les cas, l'équipement est censé posséder une fiche avant d’apparaître dans la BDD. Donc si tel n'était pas le cas, on peut imaginer que la feuille est créé mais qu'une msgbox prévient l'utilisateur qu'une nouvelle fiche a été créé car l'opérateur doit dans ce cas, inscrire la date de fabrication de l'équipement. Donc s'il n'est pas prévenu, il ne le fera pas.
Mais bon, tout cela c'est vraiment du plus ça ne correspond pas à la problématique principale.

Merci pour votre aide et si vous avez d'autres questions, n'hésitez pas.
02/04/2015, 18h10
Invité

Une dernière question ton traitement actuel sauvegarde dans des onglets de la BDD c'est en vue du dispatching ou pour archive, je veux dire tu veux le garder ou il peut sauté?
02/04/2015, 18h14
Anarhim

Le traitement actuel était un test de la macro à moitié fonctionnel. Vous pouvez l'enlevez il ne servira pas plus et dans tous les cas je possède des sauvegardes. D'ailleurs en parlant d'archivage, j'ai une macro qui permet de créer une archive de la BDD avant de lancer le dispatching. Si vous pouviez l'intégrer en début de macro d'ailleurs, ça permettrait de faire une sauvegarde automatique du document pour les archives (en cas de problème) avant d'effectuer le dispatching et la remise à zéro du classeur.

Et vous posez plusieurs questions, je suppose que c'est que vous estimez ma demande réalisable ? (que je sache si j'entretien l'espoir ou non ?)

1 pièce(s) jointe(s)

je te propose une première ébauche, ici il est seulement question de dispatcher les données dans les différents classeurs et de les enregistrer.

je n'ai pas géré le clear du fichier BDD n'y son archivage!

ici nous allons métamorphoser ta vision du développement VBA! je ne considère pas le traitement comme devant gérer plusieurs classeurs et onglets mais comme si j'en gérai qu'un! je vais établir une hiérarchie entre le classeur BDD et les classeurs cibles et les gérer en par leur nom!

je vais utiliser des collections et des modules de classe ainsi je vais créer une collection de classeur (MAS,ARI,etc...) que j'appel par leur nom (MAS,ARI,etc...)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub Test()
Dim Classeur As ClsClasseur
Dim DicoClasseur As Object
Dim R As Range
Dim i As Long
 
Set DicoClasseur = CreateObject("Scripting.dictionary")
Set R = ThisWorkbook.Sheets("BDD").UsedRange
For i = 2 To R.Rows.Count
    If Trim("" & R(i, 4).Value) <> "" Then
        If DicoClasseur.exists(R(i, 4).Value) = False Then
            Set Classeur = New ClsClasseur
            Classeur.Classeur = ThisWorkbook.Path & "\" & R(i, 4).Value & ".xlsm"
            DicoClasseur.Add R(i, 4).Value, Classeur
        End If
        DicoClasseur(R(i, 4).Value).ReseigneOnglet R.Range(R(i, 1), R(i, R.Columns.Count))
    End If
Next
Set DicoClasseur = Nothing
End Sub

je vais créer un module de classe qui ne vas gérer qu'un classeur c'est lui qui gèrera un collections d'onglets!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Private Wb As Workbook
Private DicoOnglet As Object
Private Onglet As ClsOnglet
Public Property Let Classeur(Value As String)
If Fichier_Exist(Value) = True Then Set Wb = Workbooks.Open(Value)
End Property
 
Private Function Fichier_Exist(Fichier As String)
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Fichier_Exist = Fso.FileExists(Fichier)
Set Fso = Nothing
End Function
Public Sub ReseigneOnglet(R As Range)
If DicoOnglet.exists(R(1, 3).Value) = False Then
    If feuilleExiste(R(1, 3).Value) = True Then
        Set Onglet = New ClsOnglet
        Onglet.Feuiile = Wb.Sheets(R(1, 3).Value)
        DicoOnglet.Add R(1, 3).Value, Onglet
        Set Onglet = Nothing
    End If
End If
If DicoOnglet.exists(R(1, 3).Value) = True Then
    DicoOnglet(R(1, 3).Value).ReseigneOnglet R
End If
End Sub
Private Function feuilleExiste(Feuille As String) As Boolean
Dim i As Long
For i = 1 To Wb.Sheets.Count
    If Wb.Sheets(i).Name = Feuille Then feuilleExiste = True: Exit Function
Next
End Function
Private Sub Class_Initialize()
Set DicoOnglet = CreateObject("Scripting.dictionary")
End Sub
 
Private Sub Class_Terminate()
Set DicoOnglet = Nothing
Wb.Save
Wb.Close False
End Sub

maintenant je vais créer un classe qui ne gère qu'un onglet

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Private Ws As Worksheet
Public Property Let Feuiile(Value As Worksheet)
Set Ws = Value
End Property
Public Sub ReseigneOnglet(R As Range)
Ws.Rows(3).Insert
R.Copy Ws.Range("A3")
End Sub
 
Private Sub Class_Terminate()
Set Ws = Nothing
End Sub

03/04/2015, 07h43
Anarhim

Je viens de l'essayer et je ne sais pas quoi dire. Je suis... transpercé. J'ai beau lire et relire les lignes, je ne comprends littéralement rien sur la façon dont se déroule le système, je n'ai jamais vu ni même créé ce genre de macro et ça me dépasse largement.
Dans tous les cas, cela fonctionne et je ne peux que vous en remercier.

Je vais essayer de découpé les lignes et mettre des annotations à côté de chaque pour comprendre plus largement ce que fait chaque commande.

Et il faut vraiment que je demande à mon entreprise de me payer cette formation VBA, son importance prend tout son sens aujourd'hui, l'apprentissage sur le tas n'est visiblement pas la meilleure des solutions.

Encore merci.
03/04/2015, 07h58
Invité

Bonjour,
Voila comment Excel établit une hiérarchie!
Workbooks("Classeur1.xlsm").Sheets("Feuil1").
Voila comment j'organise ma hiérarchie Classeurs("Classeur1").Onglet("Feuil1") c'est la même chose!

Sauf que dans le deuxième cas ces moi qui gère.

Si tu regardes la Sub test, tu te rends compte que je me fiche du classeur et de l'onglet, je travail sens en tenir compte, c'est le contenu de la BDD (grace aux collectios) qui fait le changement d'aiguillage !

Si tu regarde la classe clsOnglet tu vois quelle ne fait pas grand chose et j'imagine que tu vas comprendre facilement.
La classe clsClasseur ne s'occupe que d'ouvrir;enregistrer et la gestion des onglets.

Pris séparément chaque modules est intelligible, c'est dans leurs utilisations dynamique que ça devient obscur!

Il faut les comprendre individuellement et les voir comme des briques qui construisent ton Lego.
03/04/2015, 08h20
Anarhim

A oui, le R fait référence à la BDD dans le classeur actif, et la macro s’exécute en reprenant toujours l'information en 4ème colonne de chaque ligne. Après ça, le classeur correspondant est trouvé en prenant l'emplacement du classeur actif, en ajoutant la valeur présente en 4ème colonne de chaque ligne et en ajoutant l'extension.

Et je vois dans les différents modules de classe (je n'ai jamais utilisé ce genre d’élément d'ailleurs, à quoi servent-ils exactement ?) qu'il y a des macros pour définir la notion de "classeur" et la notion d' "onglet". Du coup vous faites appel aux différentes fonction dans la macro test pour la construire.

Je n'ai pas le cerveau formaté comme ça, du moins pas pour le moment. Vous avez un conseil pour que j'apprenne correctement à construire des macros (en allant du plus simple au plus complexe) ?

PS : une dernière idée pour la gestion des EPI, le fait d'archiver un classeur journalier permet de sauvegarder les données quotidiennement mais si en plus, il était possible de créer un classeur mensuel sur le principe de la BDD, ou annuel (mais je doute qu'Excel vive bien la présence de 60 000 lignes, à mon avis, il plantera sur nos PC avant qu'il ne se passe quoi que ce soit), cela nous permettrait d'extraire des statistiques sur les opérations effectuées durant le mois.

un module de classe est un programme à part entière (Modèle); il est utilisé comme une variable à la différence qu'il faut utiliser l’instruction New dim toto as new classe1 un module de classe peut contenir (intrinsèquement) des propriétés, des Sub , des fonction, etc... c'est une variable intéligente!
on peut déclarer des variable de classe (on parle d'objet) dynamiquement (instanciation)
Code:

1 2 3 dim TabeauClasse() as classe1 redim preserve TabeauClasse(0) redim preserve TabeauClasse(1)
il est également possible d'utiliser des collection, l’avantage c'est que l'on créé un tableau dynamiquement en donnant un nom à chaque items du tableau, pour cela on instancie une variable temporaire et on l'ajoute a la collection
Code:

1 2 3 4 5 6 7 8 set NewOnjet= new calsse1 CollectionObj.add "TOTO",NewOnjet set NewOnjet= nothing set NewOnjet= new calsse1 CollectionObj.add "TITI",NewOnjet set NewOnjet= nothing CollectionObj("TOTO").SubTest CollectionObj("TITI").SubTest
je reconnais que ce genre de programmation demande une autre approche intellectuelle!

mais en réalité nous l'avons tous dans la vie de tous les jours, mais la notion d'informatique bloc intellectuellement! un peut comme les non mathsteu se fige devant un SODOKU comme si il s’agissait de maths !

si tu manage une équipe tu t'adresses à des individu, pas à leurs système d'exploitation sens difficulté, la programmation objet c'est pareil sauf que c'est toi qui programme le système d'exploitation!

dès que tu as programmé le système d’exploration tu t’adresse aux individus!

Je ne sais pas si ma méthode t'as convaincu mais j'enfonce le clous avec l'année et le mois!
Bien sur il faut que les fichiers existent mais on pourra regarder cet aspect la!
Les modules de classe ne font pas de sectarisme!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
Sub Test()
Dim Classeur As ClsClasseur
Dim surlannee as new ClsClasseur
Dim surlemois as new ClsClasseur
surlannee.Classeur = ThisWorkbook.Path & "\annee_" & format(date,"yyyy") & ".xlsm"
surlemois.Classeur = ThisWorkbook.Path & "\mois_" & format(date,"yyyy-mm") & ".xlsm"
 
Dim DicoClasseur As Object
Dim R As Range
Dim i As Long
 
Set DicoClasseur = CreateObject("Scripting.dictionary")
Set R = ThisWorkbook.Sheets("BDD").UsedRange
For i = 2 To R.Rows.Count
    If Trim("" & R(i, 4).Value) <> "" Then
        If DicoClasseur.exists(R(i, 4).Value) = False Then
            Set Classeur = New ClsClasseur
            Classeur.Classeur = ThisWorkbook.Path & "\" & R(i, 4).Value & ".xlsm"
            DicoClasseur.Add R(i, 4).Value, Classeur
        End If
        DicoClasseur(R(i, 4).Value).ReseigneOnglet R.Range(R(i, 1), R(i, R.Columns.Count))
          surlannee.ReseigneOnglet R.Range(R(i, 1), R(i, R.Columns.Count))
          surlemois.ReseigneOnglet R.Range(R(i, 1), R(i, R.Columns.Count))
 
    End If
Next
Set DicoClasseur = Nothing
End Sub

...