VBA: suivre l’activité de l’utilisateur (log) 28


La combinaison de VBA et d'Excel permet d'enregistrer le suivi d'utilisation de vos fichiers dans un ou multiples fichiers journaux (fichiers texte). Ceci peut être intéressant de point de vue de sécurité, de statistique (par ex. des statistiques d'utilisation) ou de débogage. Cette fonctionnalité n'est pas, comme on pourrait le croire, réservée à des "vraies" langages de programmation comme Java ou .NET. On peut même parler d'un exemple typique de "sous-utilisation d'Excel/VBA par sa sous-estimation"… Et pourtant: VBA est capable de faire bien plus que ce que la plupart des utilisateurs imaginent!

Dans ce tutoriel, vous trouverez tout ce qui est nécessaire pour créer un fichier journal ("log file") pour suivre l'activité des utilisateurs.

Étape 1: Analyse

Avant de commencer, il est très important d'analyser la situation que nous voulons suivre. Comme c'est le cas pour la programmation en générale, plus l'analyse de départ est complète, plus rapide est le développement et plus sûre sera l'utilisation finale.

Avant de commencer une procédure qui permettra le suivi de l'utilisation, posez-vous les questions suivantes:

  • le fichier sera-t-il utilisé par plusieurs utilisateurs différents?
  • les utilisateurs ont-ils les mêmes droits?
  • quelles sont les actions que vous voulez enregistrer?
  • l'utilisateur est-il censé savoir que l'utilisation du fichier est suivie?
  • où sera/seront stocké(s) le(s) fichier(s) de suivi (les "logs")? S'agira-t-il d'un emplacement fixe ou une partie de chemin sera-t-elle variable?

Étape 2: Choix du type de fichier journal

Le fichier journal (log) se présente généralement sous forme d'un fichier texte. Ceci a plusieurs avantages comme une taille de fichier réduite et la rapidité de son utilisation. La création des fichiers journaux est, dans la plupart des cas une fonctionnalité "secondaire" et ne devrait donc pas ralentir la fonctionnalité principale du fichier Excel.

Un autre avantage de fichier texte est qu'il est possible de lui attribuer n'importe quelle suffixe sans que cela modifie son fonctionnement (à vous de choisir si vos fichiers porteront le suffixe habituel ".txt", plus explicite ".log" ou toute autre suffixe ".abc123").

Autre paramètre à définir est le caractère qui sera utilisé comme séparateur de différentes parties du fichier – vous pouvez opter pour la virgule (,), le point-virgule (;) ou pour toute autre signe qui ne risque pas d'apparaître dans l'information même qui sera inscrite dans le fichier journal.

Enfin, une information primordiale est la manière d'utiliser le fichier journal. Il y aura:

  • 1 seul fichier log pour toutes les actions et tous les utilisateurs?
  • 1 fichier journal par utilisateur?
  • 1 fichier journal pour chaque action (chaque "log" est un fichier séparé)?
  • 1 fichier journal par jour (la date fait partie du nom de fichier)?

Ceci va déterminer de quel code VBA nous aurons besoin. De manière générale, nous aurons besoin du code générique pour manipuler les fichiers texte (création d'un fichier texte, ajout des données au fichier existant) et du code spécifique pour déterminer quelle information sera stockée.

Le plus simple est de créer une fonction séparée à laquelle on fera appel dans le code principal de votre fichier. Cela évitera de répéter inutilement le même code. Cette fonction peut avoir comme argument le contenu du log ainsi que (en cas des multiples fichiers log) le chemin vers le fichier journal.

Étape 3: le code VBA à utiliser pour loguer les actions

Pour créer la procédure du suivi d'utilisation (log), nous aurons besoin des codes VBA suivants:

Code VBA pour créer un fichier texte

Cette fonction qui crée un nouveau fichier texte utilise 2 arguments – le chemin & le nom du fichier et le contenu du fichier. Ces deux arguments sont de type String.

 

Code VBA pour ajouter du texte à un fichier existant

La fonction suivante ajoute une ligne de texte à un fichier texte existant. Ce code sera nécessaire si l'on opte pour la possibilité d'un fichier journal commun. Egalement cette fonction utilise deux arguments – le chemin & nom du fichier auquel on doit ajouter du contenu ainsi que le contenu à ajouter. Les deux arguments sont de type String.

 

Code de la fonction VBA à utiliser pour enregistrer une action dans le fichier journal

 Le code avec le chemin et nom du fichier journal fixes:

 

Le code avec le chemin/nom du fichier journal variables qui est utilisé en tant qu'argument de la fonction:

 

Étape 4: des exemples d'utilisation du fichier journal

Voici, par exemple, comment enregistrer les informations sur l'utilisateur qui a consulté le fichier Excel que vous suivez. Copiez ce code à des endroits spécifiés de votre Projet VBA, modifiez le nom du fichier journal (à la ligne indiquée de la fonction EnregistrerAction) et la procédure va fonctionner sans aucune modification nécessaire.

Code VBA à copier dans "ThisWorkbook"

 

Code VBA à copier dans un Module

 

Étape 5: le fichier démo (fichier Excel contenant le code VBA et les explications)

Ce fichier démo contient la mise en pratique de tout ce que vous auriez pu trouver dans ce tutoriel – c'est-à-dire la création et l'utilisation des fichiers journaux. Le code VBA est ouvert et vous servira de guide pour vos propres créations.

La démo crée un dossier ("Excel-Malin") sur votre bureau et les fichiers journaux sont stockés dans ce dossier. Quand vous ne voudrez plus utiliser cette démo, il suffit simplement d'effacer ce dossier.

 

Téléchargé: 577x Taille de fichier: 36,3 KB

 

33 0

Pourrait vous intéresser

Partagez cette page...
Share on FacebookShare on Google+Share on LinkedInTweet about this on TwitterShare on RedditShare on TumblrDigg thisEmail this to someone

Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.

28 commentaires sur “VBA: suivre l’activité de l’utilisateur (log)

  • ArnaudL

    super bien merci.
    Par contre j'essaie de mettre une instruction avant la fermeture du classeur pour faire une copie du fichier créé vers un autre répertoire, sans succès hélas. Je dois faire des erreurs dans la syntaxe.
    help please.

    • excel-malin.com Auteur du billet

      Bonjour Arnaud,
      vous devez utiliser l'événement Workbook_BeforeClose (à placer dans l'objet "ThisWorkbook").
      Pour ce qui est de faire la copie du fichier, vous trouverez plus d'info ici: Manipulation basique des fichiers en VBA

      Donc dans votre cas, cela devrais donner quelque chose comme ceci:

      Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Dim FichierOriginal As String
      Dim FichierCopie As String

      FichierOriginal = "C:\Test\MonFichier.txt"
      FichierCopie = "C:\Archive\MonFichier_archive.txt"

      FileCopy FichierOriginal, FichierCopie
      End Sub

      Vous devez évidemment modifier les noms de fichiers.
      Ce code est à placer dans l'objet "ThisWorkbook" du fichier qui va contenir la macro.
      J'espère que j'ai répondu à votre question.
      Bien à vous, Martin

      • ArnaudL

        Bonjour Martin,
        J'avais vu le sujet sur la manipulation des fichiers, et j'essayais de l'adapter dans le prolongement du code LOG(user) dans le fichier démo, c'est dire en me servant des variables existantes :
        filecopy CheminCompletFichierJournal , "\\bidule\truc\" & nomfichierjournal
        Mais ça ne marche pas.
        Bonne journée.

        • excel-malin.com Auteur du billet

          En fait, dans la démo, la variable CheminCompletFichierJournal ne fonctionne qu'à l'intérieur de la fonction EnregistrerAction car ce n'est pas une variable globale.
          Vous avez donc deux possibilités: 1) soit créer une fonction séparée qui va renvoyer le même résultat que CheminCompletFichierJournal ou 2) définir cette fonction à l'intérieur de l'événement "BeforeClose"

          Donc solution 1: vous gardez votre code et vous copiez la fonction suivante dans un Module

          Public Function CheminCompletFichierJournal()
          NomFichierJournal = "FichierJournal-test.txt" 'à modifier d'après vos besoins
          CheminCompletFichierJournal = ObtenirCheminBureau & "\" & "Excel-Malin" & "\" & NomFichierJournal 'à modifier d'après vos besoins
          End Function

          Solution 2: vous mettez ce code dans l'objet "ThisWorkbook"

          Private Sub Workbook_BeforeClose(Cancel As Boolean)
          NomFichierJournal = "FichierJournal-test.txt" 'à modifier selon besoin
          CheminCompletFichierJournal = ObtenirCheminBureau & "\" & "Excel-Malin" & "\" & NomFichierJournal 'à modifier selon besoin

          FileCopy CheminCompletFichierJournal, "\\bidule\truc\" & NomFichierJournal

          End Sub

          Et normalement, cela devrait marcher…
          PS: attention aux guillemets (parfois, la copie à partir du site internet utilise autres guillemets – il suffit de les récrire dans l'éditeur VBA)

  • ArnaudL

    Hello,
    Bon c'est nikel, la solution 2 fonctionne bien (pas la solution 1), le seul soucis c'est que le FileCopy fonctionne très bien lorsque l'on reste sur des répertoires avec des chemins désignés par une lettre, en revanche lorsque l'on utilise "\\" ça ne marche plus (erreur76 chemin d'accès introuvable).
    Je cherche encore……

    • excel-malin.com Auteur du billet

      Normalement, FileCopy peut être utilisé avec des chemns UNC (chemins vers serveurs – commencent par "\\…").
      êtes-vous sûr d'avoir les droit d'accès (write) dans le dossier? Essayez d’accéder dans ce dossier là avec le Windows Explorer et d'y créer un fichier "manuellement". Est-ce possible?

  • ArnaudL

    Hello,
    Les droits sont normalement bons vu que les manip par windows (copier/coller/supprimer) fonctionnent.!!!
    Je vois pas…….

    • excel-malin.com Auteur du billet

      C'est alors que vient le pragmatisme du VBA:
      Vous pouvez tester si l'écriture d'un fichier fonctionne? Le code VBA: Sauvegarder texte comme un fichier

      Si cela marche, on n'a qu'à 1) lire le contenu du fichier log de base (code VBA: Lire un fichier texte en VBA), le placer dans une variable et 2) via cette variable le sauvegarder comme un nouveau fichier texte sur le serveur

      Si l'écriture marche mais vous ne vous en sortez pas pour combiner les deux opérations, faites moi signe et je vous le ferai…

  • ArnaudL

    Bon la fin de mon précédent message m'a mis sur la piste! : j'ai pris rdv chez mon ophtalmo; bref la syntaxe entre un o et 0. Du coup ça fonctionne maintenant, après une manip supplémentaire dans Outils->Références + cocher Microsoft Scripting Runtime
    Merci, @+

  • Ethan

    Bonjour,

    Votre code est parfait. Tout fonctionne correctement.

    J'ai essayé d'adapter ce code pour récupérer en plus de l'emplacement modifié, la valeur. En utilisant Target.Value ça fonctionne pour la modification d'une cellule, mais lorsque j'ai l'insertion d'une plage (copier coller d'une ligne) ça plante.

    1.Pouvez-vous m'aider à coder pour récupérer la valeur de la cellule avant et après modification, tout en gardant le code actuel (adresse des cellules modifiées) ?

    2. Autre point, j'aimerais tracer le déroulement (lancement, choix utilisateur sur les boites de dialogue/user form,…) des procédures est-ce réalisable ? C'est peut-être trop général pour que vous puissiez me répondre.

    Merci de votre aide.

    • excel-malin.com Auteur du billet

      Bonjour Ethan,
      ravi d'entendre que mon code est utile.
      Concernant vos questions:
      1A) Le code utilise l'événement Worksheet_Change qui se comporte de manière suivante:
      Target.Address est disponible pour une cellule unique ET pour une plage
      Target.Value n'est disponible QUE pour une cellule unique.
      Vous pouvez donc par exemple extraire l'information quelle ligne a été ajoutée/supprimée mais vous ne pouvez pas obtenir les valeurs directement. C'est éventuellement possible via un "loop" dans toutes les cellules de la plage modifiée.

      1B) Il n'existe pas d'événement "BeforeChange". Il est donc impossible de loguer la valeur initiale. C'est le changement même qui lance l'enregistrement de log et à ce moment-là, seule la nouvelle valeur est disponible.

      2) Ce que vous demandez est tout à fait réalisable.
      Vous pouvez utiliser tous les "events" qui sont disponibles dans VBA pour déclencher l'enregistrement dans le log.
      Vous trouverez l'exemple dans la démo pour le log de: ouverture, enregistrement, fermeture du classeur (cela se trouve dans l'objet ThisWorkbook).
      Pour les formulaires, voyez le fichier de la démo améliorée plus bas.

      Je vous ai ajouté quelques fonctionnalités dans le fichier démo:
      – il logue l'ajout et suppression des lignes/colonnes
      – il fait la distinction entre l'effacement et l'ajout/modification du contenu des cellules
      – j'ai ajouté un UserForm avec un menu déroulant qui logue la valeur que l'utilisateur choisi. Dans l'exemple, il logue directement dès que l'utilisateur fait son choix. Il est évidemment possible de déclencher le log à un autre moment (par exemple lors de la soumission du formulaire)

      Conclusion pour la question 2:
      1) le code reste toujours le même: utilisez la fonction "EnregistrerAction"
      2) tout dépend où sera placé l'appel à cette fonction. Cela peut être dans n'importe quel "événement" (Event) – que ce soit pour le classeur, la feuille, le formulaire, un élément du formulaire etc.

      Fichier démo avec les fonctionnalités supplémentaires

  • Jean-Sèb

    bonjour,
    j'ai copier les modules de code dans mon fichier.
    et j'ai l'erreur suivante
    Erreur de compilation
    Type défini par l'utilisateur non défini

    le déboggeur me renvoie sur

    Public Function AjouterAuFichierTexte(ContenuAAjouter As String, CheminFichier As String)
    et fait le focus sur

    Dim oFSO As FileSystemObject

    pouvez vous m'aider ?

    merci

  • Jérôme

    Bonjour,

    Super boulot !
    Cette fonction est-elle adaptable pour un classeur partagé en ligne sous google sheet ?
    Quelle stratégie envisager pour inscrire ce journal sous forme de feuille masquée dans le fichier excel lui-même ?

    Bien à vous !
    Jérôme

    • excel-malin.com Auteur du billet

      Bonjour et merci Jérôme!
      pour répondre à vos questions:
      1) Malheureusement, il n'est pas possible d'utiliser ce code dans Google Sheets. Cette application n'utilise pas VBA comme langage de script. Les applications Google (Sheets, Docs, Calendar, …) utilisent le "Google Apps Script" basé sur JavaScript – vous pouvez y jeter un coup d'œil ici.

      2) Si vous voulez utiliser le fichier Excel lui-même pour stocker les logs, cela simplifie grandement la chose car vous pouvez laisser tomber toute la partie qui gère les fichiers texte (création, modification etc.).
      A votre place, je m'y prendrais de manière suivante:

      1. être sûr que la feuille contenant le log est bien cachée à l'utilisateur (tutoriel ici)
      2. ensuite, il suffit juste de transférer le message du log à la bonne cellule sur la feuille cachée plutôt que de l'inscrire dans un fichier externe. Si vous vous y connaissez un peu en VBA, cela ne devrait pas vous poser problème.
      3. La seule difficulté peut être le fait de retrouver la bonne cellule (= la première vide) pour ne pas surécrire le log précédent. Et même pour cela, je peux vous proposer un petit tutoriel

      J'espère que cela répond à votre question et que cela vous aidera à faire ce dont vous avez besoin…
      Sinon, vous pouvez toujours poser d'autres questions et je vous aiderai dans la mesure de mon temps disponible.
      Bien à vous, Martin

      • Jérôme

        Bonjour et merci pour la réponse rapide !

        Pour le point 2 je ne suis pas vraiment certain d'y arriver mais je vais tenter la chose 🙂

        Jérôme

          • Jérôme Crop

            Bonjour,

            Merci beaucoup c'est vraiment cool ! Je n'en demandais pas autant ! Mais merci je vais lire et essayer de bien comprendre le code pour le réutiliser !

            Bonne journée…sous la neige…

            Jérôme

          • Phips

            Bonjour,

            Tout d’abord un grand merci pour votre travail. Il ma vraiment été utile sans vous je n'y serais jamais arrivé.

            Je suis tout neuf dans le monde de la VBA. pouriez-vous m'expliquer comment noté sur la feuille de log quel feuille à été modifier.

            avec le classeur Demo-Suivi_D_Utilisation_dans_le_meme_fichier.xlsm, in indique uniquement quel case à été modifier, hors dans mon fichier j'ai 4 feuille à surveillé.

            Merci pour votre retour.

          • excel-malin.com Auteur du billet

            Bonjour Phips,
            ce n'est pas difficile: il suffit de modifier le code qui se trouve dans la feuille "Suivi" et qui doit être copié dans chaque feuille que vous devez suivre.

            Le nouveau code qui indiquera dans le log la feuille:
            Private Sub Worksheet_Change(ByVal Target As Range)
            If Target.Cells.Count > 1 Then
            ' en cas de ajout de ligne ou colonne
            If Selection.Address = Selection.EntireRow.Address Then Call EnregistrerAction("Ligne ajoutée/supprimée [" & ActiveSheet.Name & "/" & Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) & "]")
            If Selection.Address = Selection.EntireColumn.Address Then Call EnregistrerAction("Colonne ajoutée/supprimée [" & ActiveSheet.Name & "/" & Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) & "]")

            ElseIf IsEmpty(Target) = True Then
            Call EnregistrerAction("Contenu effacé: Plage modifiée [" & ActiveSheet.Name & "/" & Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) & "]")
            Else
            Call EnregistrerAction("Contenu modifié: Plage modifiée [" & ActiveSheet.Name & "/" & Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) & "]; Nouvelle valeur [" & Target.Value & "]")
            End If

            End Sub

            Bien à vous,
            Martin

          • Phips

            Ha bein oui merci

            ça parait logique mais je ne connaissais pas la formule.

            Dans tout les cas merci.