Fichier LOG pour tracer modifs

Bonjour,

J'expérimente une version béton de fichier LOG afin de tracer qui et quand un fichier est ouvert et modifié + moment de sa fermeture. (en fait j'ai fait une autre version encore plus béton qui trace quelle cellule a été modifiée par qui avec valeur avant et après modif... mais ici c'est overkill pour mon projet)

J'ai implémenté plusieurs moutures sans réelle satisfaction. Ma dernière itération ci-dessous semble correspondre à mon besoin mais je n'arrive pas à obtenir de bons résultats en ce qui concerne "date-temps au moment de la fermeture". J'en viens donc à solliciter votre aide. Je suis certain qu'encore une fois, j'apprendrai quelque chose d'intéressant ici... bien entendu, si le fichier est simplement consulté sans y apporter de modifs, j'ai pas besoin de Log.

Merci

PS: bien entendu ce code est écrit dans ThisWorkbook

PPS: pour des raisons de continuité de service au boulot, je fais tout en anglais dans mes fichiers

PPPS: ah oui, il y a aussi un code pour du fullscreen, c'est normal ;-)

Option Explicit
Private saveTime As Date

Private Sub Workbook_Open()

' PURPOSE: Log username and date-time in new row each time the workbook is opened

    ' Declare a variable to store the name of the initially active worksheet
    Dim strOngletInitial As String
    Application.ScreenUpdating = False  ' Temporarily turn off screen updating to improve performance
    strOngletInitial = "Résumé Budget"  ' Store the name of the currently active sheet

    ' Select the worksheet named "(Log)"
    Worksheets("(Log)").Select

    ' Unprotect the "(Log)" sheet with the specified password
    ThisWorkbook.Worksheets("(Log)").Unprotect password:="bla"

    ' Find the next empty row in column A
    Dim nextRow As Long
    nextRow = ThisWorkbook.Worksheets("(Log)").Cells(ThisWorkbook.Worksheets("(Log)").Rows.Count, 1).End(xlUp).Row + 1

    ' Log the username in column A and the current date-time in column B
    ThisWorkbook.Worksheets("(Log)").Cells(nextRow, 1).Value = Application.UserName
    ThisWorkbook.Worksheets("(Log)").Cells(nextRow, 2).Value = Now()

    ' Protect the "(Log)" sheet again with the specified password and allow sorting and filtering
    ThisWorkbook.Worksheets("(Log)").Protect password:="bla", _
        AllowSorting:=True, _
        AllowFiltering:=True

    ' Re-select the initially active worksheet
    Worksheets(strOngletInitial).Select

    Application.ScreenUpdating = True  ' Turn screen updating back on

Sheets("Résumé Budget").Select
    On Error Resume Next
    With Application
        .DisplayFormulaBar = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"", false)"
        .WindowState = xlMaximized
        ActiveWindow.DisplayHeadings = True
    End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ' Store the current date and time in saveTime variable
    saveTime = Now()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Log the save time into "(Log)" worksheet when workbook is closed after being saved

    Dim wsLog As Worksheet
    Dim nextRow As Long

    ' Handle errors gracefully
    On Error Resume Next

    ' Access the "(Log)" worksheet
    Set wsLog = ThisWorkbook.Worksheets("(Log)")

    ' If error occurred or worksheet not found, exit sub
    If wsLog Is Nothing Then Exit Sub

    ' Unprotect the "(Log)" sheet with the specified password
    wsLog.Unprotect password:="bla"

    ' Find the next empty row in column C of "(Log)" worksheet
    nextRow = wsLog.Cells(wsLog.Rows.Count, 3).End(xlUp).Row + 1

    ' Log the save time into column C of next empty row
    wsLog.Cells(nextRow, 3).Value = saveTime

    ' Protect the "(Log)" sheet again with the specified password and allow sorting and filtering
    wsLog.Protect password:="bla", AllowSorting:=True, AllowFiltering:=True
Sheets("Résumé Budget").Select
On Error Resume Next
    With Application
        .DisplayFormulaBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"", true)"
        .WindowState = xlMaximized
        ActiveWindow.DisplayHeadings = True
    End With
End Sub

Bonjour TQM,

Si j'ai bien compris :

Dans "Private Sub Workbook_BeforeClose(Cancel As Boolean)"

Je changerais "saveTime" pour "Now"

    ' Log the save time into column C of next empty row
    wsLog.Cells(nextRow, 3).Value = Now  ''' saveTime

Bizz

Bonjour,

Dans votre sub before_close je ne vois pas de vérification effectuée sur l'événement "des modifications ont-elles été apportées au document ?"

De plus un autre problème, disons de programmation ou de "flow" est que vous faites dans ce sub un appel à votre variable globale saveTime sans savoir/vérifier si elle a été définie.

Il me semble, si j'ai bien compris, qu'elle est définie lors de la sauvegarde du fichier. Donc les modifications ne sont comptées que si le fichier est sauvegardé, et la date de dernière modification également... Selon moi c'est un peu limitant notamment pour la continuité de votre fichier.

Vous pouvez tester les modifications sur votre workbook directement via Workbook.Saved property (Excel) | Microsoft Learn

Cette propriété renvoie False si des modifications ont été apportées au fichier depuis sa dernière sauvegarde.

En l'utilisant, en combinaison au conseil de Bizzare relatif au fait que probablement votre variable saveTime n'est pas toujours bien initialisée, vous pourriez procéder ainsi :

Ne pas utiliser le Sub BeforeSave, ou alors le renvoyer vers un appel sur un nouveau sub d'enregistrement des logs. De meme faire un appel sur ce sub dans Before_Close sous condition :

La condition serait : If not thisworkbook.saved then ...

Une trame :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    registerModifications
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' si le fichier a été modifié, enregistrer un log avant la fermeture
    ' (mais du coup peut etre programmer un appel à la sauvegarde également ?)
    If Not ThisWorkbook.Saved Then registerModifications
End Sub

Private Sub registerModifications()
    ' Log the save time into "(Log)" worksheet when workbook is closed after being saved

    Dim wsLog As Worksheet
    Dim nextRow As Long

    ' Skipping errors ungracefully <3
    On Error Resume Next

    ' Access the "(Log)" worksheet
    Set wsLog = ThisWorkbook.Worksheets("(Log)")

    ' If error occurred or worksheet not found, exit sub
    If wsLog Is Nothing Then Exit Sub

    ' Unprotect the "(Log)" sheet with the specified password
    wsLog.Unprotect Password:="bla"

    ' Find the next empty row in column C of "(Log)" worksheet
    nextRow = wsLog.Cells(wsLog.Rows.Count, 3).End(xlUp).Row + 1

    ' Log the save time into column C of next empty row
    wsLog.Cells(nextRow, 3).Value = Now

    ' Protect the "(Log)" sheet again with the specified password and allow sorting and filtering
    wsLog.Protect Password:="bla", AllowSorting:=True, AllowFiltering:=True
    Sheets("Résumé Budget").Select
    ' not necessary, previous is still active: On Error Resume Next
    With Application
        .DisplayFormulaBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"", true)"
        .WindowState = xlMaximized
        ActiveWindow.DisplayHeadings = True
    End With
End Sub

Si je peux me permettre de vous donner trois conseils sur votre code :

Pour la gestion des Erreurs, On Error Resume Next est tout sauf ce qu'on pourrait considérer comme "gracieux", il est à utiliser avec parcimonie. (Error Handling In VBA (cpearson.com) , On Error Resume Next – Rubberduck News (rubberduckvba.blog)). Pour faire simple c'est un appel utile mais vous devriez le contenir à l'unique/le groupe d'instruction(s) relatif à la levée possible d'une erreur. Et ensuite redéfinir On Error Goto 0.

En l'occurence pour votre code ce serait :

    ' trying to Access the "(Log)" worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("(Log)")
    On Error Goto 0
    If wsLog Is Nothing Then Exit Sub

Ensuite je vois que vous utilisez .Select sur une feuille. C'est possible bien sur mais si vous souhaitez simplement déplacer la vue, .Activate serait plus approprié (Difference Between Select and Activate Methods in VBA - Excel Campus)

' Re-select the initially active worksheet
    Worksheets(strOngletInitial).Select
' plutot
   Worksheets(strOngletInitial).Activate

Enfin, pour la robustesse, considérez l'appel systématique à ThisWorkbook. devant vos feuilles. Je vois qu'il est souvent présent c'est très bien, mais dans ce cas autant le mettre partout.

Un exemple :

    ' Select the worksheet named "(Log)"
    Worksheets("(Log)").Select

    ' Unprotect the "(Log)" sheet with the specified password
    ThisWorkbook.Worksheets("(Log)").Unprotect password:="bla"

C'est peu probable, mais en supposant que vous ayez un autre fichier ouvert, actif (en premier plan), avec une feuille nommée (Log), alors dans la première instruction vous sélectionnez cette feuille dans le classeur ACTIF (considérez activate si vous voulez changer la vue utilisateur, mais alors pourquoi le ramener sur la feuille initiale à la fin de votre sub ? vous pouvez écrire dans une feuille sans qu'elle soit active), puis dans la seconde instruction vous déverrouille la feuille (Log) du classeur THISWORKBOOK, celui de la macro, pas forcément le meme que le classeur actif.

Pour ce dernier point, une manière plus élégante, qui évite de longs thisworkbook.worksheets("nom affiché de la feuille") .... vous pouvez prendre avantage du nom de code de vos feuilles dans l'éditeur VBA. Sélectionnez une feuille dans la liste, et regardez ses propriétés, vous pouvez modifier le nom. Cela vous permet de remplacer :

ThisWorkbook.Worksheets("(Log)").Activate

Par

LogSheet.Activate

Trois avantages :

1. Plus facile à lire

2. Si le nom affiché de l'onglet peut changer/etre modifié par l'utilisateur, le nom de l'objet VBA lui ne change pas.

3. L'appel direct sur l'objet est plus rapide que la résolution de thisworkbook.worksheets("nom affiché de la feuille") qui doit checker toutes les feuilles du classeur et leurs noms au moment de l'exécution, pour espérer trouver la feuille en question.

Bonjour à tous,

Whaou ! merci pour vos réponses, votre temps et votre indulgence !

Je vais analyser tout cela immédiatement et vous reviens asap.

Encore merci pour votre aide et merci à Excel-Pratique pour le cadre.

Excellente fin de matinée à tous

Re,

Alors j'ai nettoyé le code et tenté de suivre vos conseils. Pour le moment, j'aimerais juste que cela fonctionne avant de verrouiller/déverrouiller.

Cependant, voici mon problème: à chaque fois que l'utilisateur sauve le fichier, une ligne est ajoutée. Alors que j'aimerais que juste la fermeture soit loggée.

voici le code modifié: (j'ai laissé volontairement vos commentaires)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ' Store the current date and time in saveTime variable
    registerModifications
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 ' si le fichier a été modifié, enregistrer un log avant la fermeture
    ' (mais du coup peut etre programmer un appel à la sauvegarde également ?)
    If Not ThisWorkbook.Saved Then registerModifications
End Sub
Private Sub registerModifications()

    ' Log the save time into "(Log)" worksheet when workbook is closed after being saved

    Dim wsLog As Worksheet
    Dim nextRow As Long

    ' Handle errors gracefully
    On Error Resume Next

    ' Access the "(Log)" worksheet
    Set wsLog = LogSheet

    ' If error occurred or worksheet not found, exit sub
    If wsLog Is Nothing Then Exit Sub

    ' Unprotect the "(Log)" sheet with the specified password
    'wsLog.Unprotect Password:="bla"

    ' Find the next empty row in column C of "(Log)" worksheet
    nextRow = wsLog.Cells(wsLog.Rows.Count, 3).End(xlUp).Row + 1

    ' Log the save time into column C of next empty row
    wsLog.Cells(nextRow, 3).Value = Now

    ' Protect the "(Log)" sheet again with the specified password and allow sorting and filtering
    'wsLog.Protect Password:="bla", AllowSorting:=True, AllowFiltering:=True
Sheets("HOME").Activate

    With Application
        .DisplayFormulaBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"", true)"
        .WindowState = xlMaximized
        ActiveWindow.DisplayHeadings = True
    End With
End Sub

Merci pour vos lumières

Re,

Le log lors de la sauvegarde vient de

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ' Store the current date and time in saveTime variable
    registerModifications
End Sub

Que j'avais gardé pour essayer de "mimer" le comportement initial.

Si vous ne souhaitez pas log les sauvegardes alors retirez ce sub tout simplement (ou en tout cas l'appel de registerModifications).

Merci pour vos précieux conseils. Après multiples itérations (je vous passe les détails), le code suivant semble donner satisfaction.

Seul bémol, si l'utilisateur effectue les modifications A dans le fichier, puis clique sur "Sauve" puis effectue les modifications B puis répond "non" à "voulez-vous enregistrer les modifications avant de fermer", le log n'est pas enregistré alors que des modifs ont été apportées !! (corollaire, je me retrouve avec la colonne A et B du Log (log ouverture du fichier) en avance sur la C (log fermeture de fichier)).

J'avoue tourner en rond...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Check if the workbook needs saving
    If Not ThisWorkbook.Saved Then
        ' Prompt the user to save changes
        Dim result As VbMsgBoxResult
        result = MsgBox("Do you want to save the changes you made to the workbook?", vbYesNoCancel)

        Select Case result
            Case vbYes
                ' If user chooses to save, save the workbook
                ThisWorkbook.Save
                ' Log the modification after saving
                registerModifications
            Case vbNo
                ' If user chooses not to save, proceed with closing without logging
                ' Do nothing here
            Case vbCancel
                ' If user cancels, abort the close operation
                Cancel = True
        End Select
    Else
        ' If the workbook is already saved, log the modification
        registerModifications
    End If
End Sub

Private Sub registerModifications()
    ' Log the save time into "(Log)" worksheet when workbook is closed after being saved

    Dim wsLog As Worksheet
    Dim nextRow As Long

    ' Handle errors gracefully
    On Error Resume Next

    ' Access the "(Log)" worksheet
    Set wsLog = ThisWorkbook.Sheets("(Log)")

    ' If error occurred or worksheet not found, exit sub
    If wsLog Is Nothing Then Exit Sub

    ' Find the next empty row in column C of "(Log)" worksheet
    nextRow = wsLog.Cells(wsLog.Rows.Count, 3).End(xlUp).Row + 1

    ' Log the save time into column C of next empty row
    wsLog.Cells(nextRow, 3).Value = Now

    Sheets("HOME").Activate

    With Application
        .DisplayFormulaBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"", true)"
        .WindowState = xlMaximized
        ActiveWindow.DisplayHeadings = True
    End With
End Sub

J'aimerais éviter l'aspect confusant du "j'ai déjà sauvé, j'ai pas apporté de modifs mais en fermant il me demande si je veux enregistrer les modifs avant fermeture".

Bref, merci pour votre patience.

Je dois bien avouer que j'ai un peu de mal à comprendre votre manière de gérer les logs…

En théorie le principe de ne pas sauvegarder c'est pour ne pas logger/enregistrer les modifications justement.

Si vous souhaitez logger systematiquement à la fermeture du fichier, alors sortez l'appel à la fonction registerModifications de votre Select Case.

En regardant l'attribut .Save de Me comme indiqué dans Workbook.BeforeClose event (Excel) | Microsoft Learn , peut etre que vous pouvez implémenter votre idée de manière plus claire pour l'utilisateur.

Pour moi le code actuel fait ce que vous lui demandez, si ca ne convient pas définissez d'abord l'ensemble des cas possibles de modifications/sauvegardes/fermetures, les logs correspondants que vous voulez sauvegardez ou non, puis implémentez-le dans le code. Vous y etes presque mais j'ai l'impression que la confusion vient d'une mauvaise définition de ce que vous attendez. Essayez de le "simplifier/clarifier".

Bonjour,

Merci beaucoup pour vos conseils et astuces. J'ai pas mal progressé. Mais j'ai décidé de maisser tomber le log à la fermeture.

Pour tout vous avouer, je suis submergé de tâches excel et celle-ci n'est pas prioritaire. J'y reviendrai un jour quand j'aurai le temps.

Encore merci