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

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 statistiques (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 de la situation – le but du log

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 à suivre/surveiller 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? Ouverture / modification / … ?
• 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 qui en découle. 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 ou d'une application de manière générale.

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 comme par exemple ".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:

• un seul fichier log pour toutes les actions et tous les utilisateurs?
• un fichier journal par utilisateur?
• chaque action aura-t-elle un fichier journal (chaque "log" est un fichier séparé)?
• un fichier log 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 qui suivent. Attention, pour qu'ils fonctionnent correctement, vous devez activer la Référence Microsoft Scripting Runtime (dans l'éditeur VBA: Outils -> Références -> cocher "Microsoft Scripting Runtime").

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

Public Function SauvegarderChaineCommeFichierTexte(Contenu As String, CheminFichier As String)
'par Excel-Malin.com ( https://excel-malin.com )

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = FSO.CreateTextFile(CheminFichier)
oFile.WriteLine Contenu
oFile.Close
Set FSO = Nothing
Set oFile = Nothing

End Function

 

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

Public Function AjouterAuFichierTexte(ContenuAAjouter As String, CheminFichier As String)
'par Excel-Malin.com ( https://excel-malin.com )

Dim oFSO As FileSystemObject
Set oFSO = New FileSystemObject
Dim oFS As TextStream
Set oFS = oFSO.OpenTextFile(CheminFichier, ForAppending)
oFS.WriteLine ContenuAAjouter
oFS.Close
Set oFS = Nothing
Set oFSO = Nothing

End Function

 

c) 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 (et qui est donc inclus directement dans le code):

Function EnregistrerAction(ActionNom As String)
Dim CheminCompletFichierJournal As String
Dim ContenuEnregistrement As String

CheminCompletFichierJournal = "C:\DossierLog\FichierLog.txt"

'création du contenu à enregistrer
ContenuEnregistrement = ActionNom

'vérifier si le fichier journal existe déjà & enregistrer l'information
VerificationFichierJournal = Len(Dir(CheminCompletFichierJournal))
   If VerificationFichierJournal = 0 Then
    's'il n'existe pas encore -> créer un nouveau
    LogResult = SauvegarderChaineCommeFichierTexte(ContenuEnregistrement, CheminCompletFichierJournal)
  Else
    's'il existe -> ajouter la nouvelle ligne
    LogResult = AjouterAuFichierTexte(ContenuEnregistrement, CheminCompletFichierJournal)
  End If
End Function

 

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

Function EnregistrerAction(ActionNom As String, CheminCompletFichierJournal As String)
Dim ContenuEnregistrement As String

'création du contenu à enregistrer
ContenuEnregistrement = ActionNom

'vérifier si le fichier journal existe déjà & enregistrer l'information
VerificationFichierJournal = Len(Dir(CheminCompletFichierJournal))
   If VerificationFichierJournal = 0 Then
    's'il n'existe pas encore -> créer un nouveau
    LogResult = SauvegarderChaineCommeFichierTexte(ContenuEnregistrement, CheminCompletFichierJournal)
  Else
    's'il existe -> ajouter la nouvelle ligne
    LogResult = AjouterAuFichierTexte(ContenuEnregistrement, CheminCompletFichierJournal)
  End If
End Function

Ce code est donc tout à fait générique et vous pouvez l'utiliser dans toutes les situations et dans n'importe quel projet sans devoir le modifier.

Attention: vous aurez besoin, pour faire fonctionner le tout, d'inclure dans votre projet les trois codes VBA – le code "a)", le code "b)" et un des deux codes "c)".

Étape 4: des exemples pratiques 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 autre modification nécessaire.

Code VBA à copier dans "ThisWorkbook"

Private Sub Workbook_Open()
    Call EnregistrerAction("Ouverture de Classeur")
End Sub

 

Code VBA à copier dans un Module

Public Function AjouterAuFichierTexte(ContenuAAjouter As String, CheminFichier As String)
'par Excel-Malin.com ( https://excel-malin.com )

Dim oFSO As FileSystemObject
Set oFSO = New FileSystemObject
Dim oFS As TextStream
Set oFS = oFSO.OpenTextFile(CheminFichier, ForAppending)
oFS.WriteLine ContenuAAjouter
oFS.Close
Set oFS = Nothing
Set oFSO = Nothing

End Function

Public Function SauvegarderChaineCommeFichierTexte(Contenu As String, CheminFichier As String)
'par Excel-Malin.com ( https://excel-malin.com )

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = FSO.CreateTextFile(CheminFichier)
oFile.WriteLine Contenu
oFile.Close
Set FSO = Nothing
Set oFile = Nothing

End Function

Public Function EnregistrerAction(ActionNom As String)
'par Excel-Malin.com ( https://excel-malin.com )
Dim CheminCompletFichierJournal As String
Dim ContenuEnregistrement As String

CheminCompletFichierJournal = "C:\DossierLog\FichierLog.txt" '<-- changez le chemin et le nom du fichier journal

'création du contenu à enregistrer
' -> exemple: date, temps, nom d'utilisateur, action
ActionMoment = Now
ContenuEnregistrement = _
Year(ActionMoment) & Right("0" & Month(ActionMoment), 2) & Right("0" & Day(ActionMoment), 2) & ";" _
& Right("0" & Hour(ActionMoment), 2) & ":" & Right("0" & Minute(ActionMoment), 2) & ":" & Right("0" & Second(ActionMoment), 2) & ";" _
& Environ("UserName") & ";" _
& ActionNom

'vérifier si le fichier journal existe déjà & enregistrer l'information
VerificationFichierJournal = Len(Dir(CheminCompletFichierJournal))
   If VerificationFichierJournal = 0 Then
    's'il n'existe pas encore -> créer un nouveau
    LogResult = SauvegarderChaineCommeFichierTexte(ContenuEnregistrement, CheminCompletFichierJournal)
  Else
    's'il existe -> ajouter la nouvelle ligne
    LogResult = AjouterAuFichierTexte(ContenuEnregistrement, CheminCompletFichierJournal)
  End If
End Function

Comme vous voyez, avec ce code tout fait, le suivi devient presque un jeu d'enfant.

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

 

Pour aller plus loin en VBA…

Si ce tutoriel vous a été utile, n'hésitez pas à consulter d'autres nombreux articles sur Excel-Malin.com. Ils devraient rendre votre travail avec VBA plus agréable et plus efficace.

En voici quelques uns…

• Liste de toutes les fonctions disponibles en VBA
• Tutoriel sur la Manipulation des fichiers en VBA
• Envoyer un email (automatique) à partir d'Excel – oui, oui, c'est possible!
• Gestion des dates et du temps en VBA
• Comment utiliser la fonction RECHERCHEV en VBA – ce qui est bien plus rapide qu'une boucle!