suivre les modifications d'une feuille excel

**ver_for** · 30/11/2009, 18h20

Bonjour,
J'ai un utilisateur qui travaille sur un fichier excel, en fait une seule feuille qui contient une liste d'événements, un événement par ligne. L'utilisateur ajoute ou modifie des évènements mais n'en supprime pas.

Voici ce que je souhaite réaliser: lorsqu'il clique sur un bouton, je souhaite que soit envoyé par mail un fichier xls qui contienne simplement les lignes qui ont été modifiées.

Voici comment je pense procéder.
A l'ouverture du fichier, on crée une nouvelle feuille qui est une copie identique de la feuille utilisée.
Lorsque l'utilisateur clique sur le bouton, on crée une troisième feuille. On parcourt la première feuille et on compare ligne par ligne le contenu avec le contenu de la seconde et si le contenu est différent on l'insére sur la troisième.
On envoit ensuite le contenu de la troisième feuille par e-mail.

Avez-vous des idées plus simples?

Merci pour vos réponses.

**Krovax** · 30/11/2009, 20h48

Bonsoir
Une idée un peu plus simple mais de loin pas la meilleur. Tu utilises l'évènement worksheet_change voir ici
a chaque modif tu met un 1 sur la meme case d'un deuxième onglet masqué. Pour ta sauvegarde tu ne copie que les ligne qui corespondent a un 1 sur le deuxième onglet.

exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Private Sub Worksheet_Change(ByVal Target As Range)
dim cel as range
for each  cel in target
worksheets("deuxiemeOnglet").range(cel.address)=1
next cel
end sub

Je te laisse voir pour envoyer le mail

**Pierre Fauconnier** · 30/11/2009, 21h28

Salut.

Bonne idée de Krovax, que je simplifierais un petit peu.

Puisqu'il s'agit d'envoyer toute la ligne lorsque modification ou ajout, je ne mettrais des 1 que en colonne A quelque soit la colonne modifiée. Cela permettra de ne scanner qu'une seule colonne pour détecter les lignes modifiées ou ajoutées...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Private Sub Worksheet_Change(ByVal Target As Range)
dim cel as range
for each  cel in target
worksheets("deuxiemeOnglet").range(cel.row,1)=1
next cel
end sub

Il faut peut-être penser à remettre "deuxièmeonglet" à blanc à l'ouverture du classeur, ou à tout le moins avant de commencer à travailler...

**ver_for** · 30/11/2009, 22h25

Merci pour vos réponses, que je testerai.

**ucfoutu** · 01/12/2009, 08h27

Envoyé par ver_for

...... L'utilisateur ajoute ou modifie des évènements mais n'en supprime pas.

Voici ce que je souhaite réaliser: lorsqu'il clique sur un bouton, je souhaite que soit envoyé par mail un fichier xls qui contienne simplement les lignes qui ont été modifiées.

Bonjour,

Peux-tu nous préciser de manière un peu plus "serrée" ce que tu entends par "les lignes qui ont été modifiées", ver_for ?

Considères-tu qu'une ligne a été modifiée si elle a subi des modifications, quels qu'en soient le nombre et le résultat final ... ou considères-tu qu'elle n'a été modifiée que si le résultat final des modifications est différent de la situation avant modifications ? (ce n'est pas la même chose).
Dans le second cas (celui de ne retenir comme modification que le résultat d'une situation finale différente de la situation originelle), il te faut en effet, selon moi, travailler comme tu l'avais prévu (sur deux feuilles), mais en privilégiant un mécanisme moins lourd que celui de la boucle, telle que tu l'as envsiagée..

Dans cette éventuelle optique : peux-tu nous préciser si les éventuels ajouts (pour ce qui est des ajouts) peuvent s'effectuer de manière intercalée (ce qui complique un peu) ou uniquement après la dernière ligne déjà remplie ?

**ver_for** · 04/12/2009, 11h19

Bonjour,
Effectivement ce qui m'intéresse comme modification est de savoir si le contenu final de la cellule est différent du contenu originale.

Il est également possible d'intercaler une ligne sur le fichier. Dans ce cas ça complique la comparaison. Peut-être pourrais-je, s' c'est possible procéder ainsi.

Si une ligne est insérée, on insère également une ligne vide sur la seconde feuille, de sorte qu'il soit possible de comparer les cellules.

Pour le "mécanisme moins lourd que celui de la boucle, telle que tu l'as envsiagée.." je suis preneur.

Merci encore pour vos réponses.

**ucfoutu** · 04/12/2009, 11h33

Dur dur dès lors que tu peux ajouter des lignes en "intercalé" (c'était bien là ma crainte et la raison de ma question). Intercaler dans le même temps sur l'autre feuille ne changerait hélàs rien au problème, à première vue et après "méditation" ....

On peut, si tu veux, tenter de procéder par étapes (la seconde sera probablement dure, et peut-être même impossible, sauf à aller vers une usine à gaz)

Si tu es prêt, (et prêt à me suivre) on peut commencer par l'étape la moins difficile, à savoir : aucun ajout par "intercalé" (uniquement en fin des rangs renseignés.
Cà te tente ? Si oui : je m'y mets lentement....

EDIT : mais voilà que me vient maintenant une autre idée qui pourrait être plus facile à réaliser et que je vais t'exposer.

- à chaque "modif" : comparaison avec la situation d'origine et ===>> si différent ===>> couleur du rang en rouge - si identique : restitution de la couleur d'origine
- à chaque rajout, y compris en "intercalé" ===>>> couleur du rang en rouge
MAIS ALORSACHTUNG ! ===>>> ne pas rendre possible la suppression, ensuite, du rang ajouté et ne pas rendre possible non plus (quelle gymnastique !) un ajout "intercalé" au dessus du dernier ajout déjà intercalé (impossible, sine qua non, de procéder à des comparaisons valables avec la feuille d'origine) !)
Si on retient cette démarche-là : on ajoute simplement un compteur des rajouts. Ce compteur servira à comparer les bonnes lignes des deux feuilles (tu suis bien le regard et les pensées du vieux que je suis ? J'espère que oui...

)
In fine : recensement des seules lignes restées en rouge ...

Voilà : tu as maintenant deux choix de stratégie ... (mais c'est un choix très cornélien, comme tu vois

)

EDIT 2 :
Le vieux fou continue à cogiter.
Autre solution :
- Ajout d'une colonne dans laquelle on "stocke" un numéro correspondant à la ligne où l'on veut insérer en "intercalé", mais : dès que l'utilisateur décide de cette insertion ===>> on fiche en réalité cette ligne en bas de tout (on verra ensuite comment se servir astucieusement du contenu de la colonne rajoutée

).
Si cette dernière stratégie te parait envisageable, on la conjugue avec la toute première et le tour est joué ...
Vl'a que le choix est maintenant plus grand, mais tout aussi cornélien..
Prends surtout tout ton temps pour décider, en fonction de ce que tu souhaites pour le confort de celui qui doit modifier la feuille... (fais-le en pensant à tout)

**Pierre Fauconnier** · 04/12/2009, 12h57

Ma réflexion:

1) Excel n'est pas un gestionnaire de données, et donc il est illusoire de vouloir, facilement, suivre des modifications de données avec Excel. A preuve, l'impossibilité de lever un événement sur l'insertion ou la suppression de lignes (là où un sgbd avec triggers le fait sans problèmes)

2) Même dans des sgbd performants permettant par exemple l'utilisation de triggers, le trigger est actionné à l'update de la ligne, indépendamment du fait que la valeur ait été réellement modifiée ou pas. Ainsi, si on modifie un champ, puis que l'on revient sur ses pas par une saisie qui annule la modification, l'événement est levé et la "modification" est détectée

3) Si l'on veut "singer" un sgbd avec Excel, alors, il faut accepter les règles et postulats des sgbd, notamment en sachant que normalement, l'ordre des lignes n'a aucune importance. Dès lors, vouloir insérer une ligne en Excel n'a aucun sens et il est judicieux d'ajouter les lignes en bas du tableau, quitte à les présenter dans un autre ordre par la suite.

4) Toute autre comparaison de données dans un SGBD amènera immanquablement à devoir boucler sur les enregistrements d'une table modifiée ET d'une table de référence, et à tester les valeurs lignes par ligne.

Le non-respect de ces points amènera inévitablement à l'usine à gaz qui, à mon sens, ne sera jamais fiable.

Toutefois, en tenant compte du fait qu tu veux uniquement les lignes pour lesquelles il y a des différences (<> modifiées si annulation d'une modif, car sinon, la solution proposée précédemment est largement suffisante)), qu'il n'y a pas d'insertion ni de suppression de lignes, mais uniquement des modifs sur les lignes ou des ajouts de lignes, on pourrait boucler sur les lignes, délimiter le nombre de colonnes qui sont à prendre en compte pour la comparaison, et injecter les lignes modifiées dans une table des modifications.

On aura donc besoin, avant d'apporter une modification dans une feuille, d'en réaliser une copie permettant la comparaison, le code ci-dessous bouclant sur les lignes de la feuille modifiée et comparant le contenu d'une ligne avec le contenu de la même ligne de la feuille de référence.

Pour ce faire, j'ai considéré que l'on pouvait concaténer les valeurs des différentes cellules d'une ligne.

Attention qu'une modification de format dans une cellule sera interprétée comme une modification SI l'aspect de la valeur change (date formatée en nombre). Normalement, il ne devrait pas y avoir de modifs de cette sorte.

Voici un code rapide qui fait cela (+/- 7 secondes pour 25000 lignes sur 16 colonnes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub ExtractionsModifications(FeuilleOriginale As Worksheet, feuilleModifiee As Worksheet, feuilleResultat As Worksheet)
    Dim DerniereColonne As Long
    Dim CelluleOrigine As Range
    Dim CelluleModifiee As Range
    Dim CelluleResultat As Range
 
    DerniereColonne = FeuilleOriginale.Cells(1, FeuilleOriginale.Columns.Count).End(xlToLeft).Column
 
    For Each CelluleModifiee In Range(feuilleModifiee.Range("a2"), feuilleModifiee.Cells(feuilleModifiee.Rows.Count, 1).End(xlUp))
        Set CelluleOrigine = FeuilleOriginale.Cells(CelluleModifiee.Row, CelluleModifiee.Column)
        If getChaineLigne(CelluleModifiee, DerniereColonne) <> getChaineLigne(CelluleOrigine, DerniereColonne) Then
            Range(CelluleModifiee, CelluleModifiee(1, DerniereColonne)).Copy Destination:= _
                feuilleResultat.Cells(feuilleResultat.Rows.Count, 1).End(xlUp)(2)
        End If
    Next CelluleModifiee
 
End Sub
 
Function getChaineLigne(CelluleA As Range, DerniereColonne As Long) As String
    Dim Cellule As Range
 
    For Each Cellule In Range(CelluleA, CelluleA(1, DerniereColonne))
        getChaineLigne = getChaineLigne & Cellule.Value
    Next Cellule
End Function

**ucfoutu** · 04/12/2009, 13h50

Bonjour, Pierre Fauconnier,

Le repérage (au moment-même où elle se produit) d'une insertion de ligne n'est pas un problème en soi....(il se fait bien sur ma machine avec EXCEL 2007)
(à moins d'avoir mal compris la formulation faite)

**Pierre Fauconnier** · 04/12/2009, 14h41

J'ai dit ceci:

A preuve, l'impossibilité de lever un événement sur l'insertion ou la suppression de lignes

Maintenant, il y a bien entendu moyen de le faire par code, mais au prix d'une exécution à chaque modification d'une feuille, ce qui est quand même bien lourd.

Mais pour ce qui est de

Envoyé par ucfoutu

Le repérage (au moment-même où elle se produit)

J'attends de voir...

**ucfoutu** · 04/12/2009, 15h14

Envoyé par Pierre Fauconnier

J'attends de voir...

Je n'ai pas Excel sous la main en cet instant pour vérifier que ma mémoire est bonne, mais essaye ceci, par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Private Sub Worksheet_Change(ByVal Target As Range)
 If Application.CountA(Rows(Target.Row)) = 0 Then
   MsgBox "une ligne a été ajoutée sous la ligne " & " " & Target.Row
 End If
End Sub

EDIT : bien évidemment, me même message serait diffusé si (cas vraiment peu probable), le "modificateur intervenant" s'amusait à effacer le contenu de la totalité des cellules. Mais cela ne me gênerait alors pas, puisque je traiterais illico-presto cette ligne comme à mettre "au bas de la pile", avec son rang dans la colonne "ajoutée" (voir plus haut)

**Pierre Fauconnier** · 04/12/2009, 15h36

Ce procédé n'est pas fiable.

1) Il ne fonctionne que pour l'insertion d'une ligne vierge, l'insertion d'une ligne copiée (clic droit sur une ligne => copier => clic droit => Insérer les cellules copiées) ne détecte pas l'insertion des lignes.

2) Comment tient-il compte du fait que l'on peut insérer plusieurs lignes vides, en prévision de plusieurs enregistrements à ajouter?

3) Si l'on efface toute une ligne (ou plusieurs lignes)(<> supprimer) ou si l'on efface la seule cellule non vide d'une ligne (ou de plusieurs lignes), ton code va considérer que l'on ajoute une ligne.

**Jannus** · 04/12/2009, 15h51

sans perdre de vue le souhait du demandeur

Envoyé par ver_for

Avez-vous des idées plus simples?

**Pierre Fauconnier** · 04/12/2009, 15h52

Libre à chacun de développer l'usine à gaz qui pourra faillir à un moment ou à un autre car il sera malaisé d'envisager tous les cas, ou de faire preuve de pragmatisme en adoptant un code fiable déclenché sur demande, qu'il suffit d'adapter à son cas.

Pour ce qui me concerne, le choix de ma solution plutôt que la tienne est vite fait. Il reviendra à ver_for de faire son choix, au vu de tes arguments et des miens.

**Guardian** · 04/12/2009, 16h17

Chapeau bas

Envoyé par ucfoutu

Je n'ai pas Excel sous la main en cet instant pour vérifier que ma mémoire est bonne...

Tu vas jusqu'à saisir du code en différenciant majuscules et minuscules sans avoir accès à Excel... Fortiche de chez Fortiche!!!

**Didier Gonard** · 04/12/2009, 16h28

Bonjour,

Perso, mon approche serait un scénario de ce genre

:

1 – on utilise le vba de toutes façons
2 – Quand on met à libre disposition des feuilles-apllis de ce genre, les utilisateurs y mettent un jour ou l’autre le souk.
3 – On ne permet aucun accès direct à la feuille (ou aux feuilles voulues) à l’utilisateur
4 – on travaille tout via des interfaces utilisateurs, USF, extractions etc…
5- la feuille orientée base de donnée est protégée, sauf contre la sélection de cellule
6- l’utilisateur s’il veut modifier ou créer, double-clic sur une cellule
6B - Private Sub Worksheet_BeforeDoubleClick ouvre un USF
7- l’USF lui demande si c’est un modif ou une créa qu’il veut
8- cela ouvre un USF ou touts les champs de la ligne sont présents (les « réservés » étant non modifiables ou absents). Ils sont remplis pour modif ou vierges pour créa
9- le code connaît le N° de la ligne modifiée ou rajoutée (en fin de base) et une fois la chose validée inscrit en dur ce N° dans une feuille « Passage Données » et sauvegarde.
10-Quand l’utilisateur quitte l’USF principal, l’appli lit les N°s de lignes modifiées ou rajoutées et fait les opérations désirées, crée, copie sauvegarde et fait le ménage de sa feuille Passage données.

Note : "l'évènement" serait le bouton Valider de l'USF, s'il n'est pas validé, il y a un bouton Abandon ou la croix rouge qui n'entraîne aucune inscription dans Passage Données.

cordialement,

Didier

**Pierre Fauconnier** · 04/12/2009, 16h38

Salut Didier

,

A l'évidence, ta solution est une solution "pro", bien plus fiable et performante que le bidouillage "usine à gaz" destiné à tenter ^^ de capturer "l'insertion de ligne"...

Ma solution est moins lourde en termes de développement, mais si j'avais à le développer dans un contexte pro, soit je le ferais en Access, soit j'adopterais ta solution.

suivre les modifications d'une feuille excel

Macros et VBA Excel

Discussions similaires

Partager

Partager