Créer un fichier log sur l’utilisation d’un classeur Excel

Notions à retenir

• VBA permet de créer un fichier texte pour tracer automatiquement toutes les modifications apportées aux cellules Excel
• Le système utilise des procédures événementielles (Worksheet_Change et Worksheet_SelectionChange) couplées à une procédure d’écriture
• Cette solution nécessite la déclaration de variables publiques et l’utilisation des instructions Open, Print et Close pour la gestion du fichier

Créer un système de traçabilité des modifications Excel avec VBA

VBA offre la possibilité d’interagir avec d’autres fichiers et applications, notamment pour créer un fichier texte qui enregistre automatiquement toutes les modifications apportées à un classeur Excel. Cette fonctionnalité permet de tracer les changements, identifier l’utilisateur responsable et horodater chaque modification.

Déclaration des variables globales

La première étape consiste à déclarer les variables publiques dans un module VBA (ALT+F11). Ces variables stockeront les informations relatives à chaque modification :

Public reference As String
Public feuille As String
Public av As String ' ancienne valeur
Public nv As String ' nouvelle valeur
Public utilisateur As String
Public datemodif As String

Procédure d’écriture du fichier texte

La macro d’écriture utilise les instructions Open, Print et Close pour créer et alimenter le fichier de traçabilité. Le fichier sera automatiquement créé lors de la première utilisation :

Sub ecriture()
Open "c:excel-plus.txt" For Append As 1
Print #1, "— Modification du fichier —"
Print #1, "La cellule " & reference & " a été modifiée, dans la feuille " & feuille
Print #1, "Ancienne valeur : " & av
Print #1, "Nouvelle valeur : " & nv
Print #1, "Changée par : " & utilisateur
Print #1, "Modifiée le : " & datemodif
Close 1
End Sub

Procédures événementielles

Deux procédures événementielles doivent être intégrées dans chaque feuille de calcul concernée. La première capture les modifications apportées aux cellules :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
nv = Target.Value
Else
nv = "Valeur inconnue"
End If
utilisateur = Environ("Username")
datemodif = Now
reference = Target.Address
feuille = Target.Worksheet.Name
Call ecriture
End Sub

La seconde procédure enregistre la valeur de la cellule avant modification :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
av = Target.Value
Else
av = "Valeur inconnue"
End If
End Sub

Ce système fonctionne uniquement pour les sélections de cellules simples. Il est essentiel de vérifier les droits d’écriture sur l’emplacement du fichier, particulièrement en environnement réseau ou multi-utilisateurs.