Je voudrais créer un fichier .log à l'ouverture et à la fermeture de mon classeur. Dans ce log je voudrais inscrire le nom de l'utilisateur (username) la date et l'heure

Dans le module ThisWorkbook de ton classeur:

Private Declare Function GetUserName Lib "advapi32.dll" _  
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long    

Private sub Workbook_BeforeClose(Cancel As Boolean)
 LogUserAction "Closed"
 end sub

Private sub Workbook_Open()
 LogUserAction "Opened"
 end sub

Function UserName()
 Dim S As String, n As Long, Res As Long
 S = String$(200, 0): n = 199: Res = GetUserName(S, n)
 UserName = UCase(Left(S, n - 1))
 End Function

sub LogUserAction(Action As String)
 Dim f As Integer, HistLog As String
 HistLog = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
 HistLog = ThisWorkbook.Path & "" & HistLog & ".txt"
 f = FreeFile
 Open HistLog For Append Shared As #f
 Write #f, Format(Now, "yyyy-mm-dd hh:mm:ss"), UserName, Action
 Close #f
 end sub