create log file

[sedgely]

does anyone know a way of creating a log file and writing to it each time an excel workbook is opened to record the time/date and the user name of the person opening it, also would it be possible to record whether the user made changes/saved the file.

Craig

 

[Bowers74]

I always like to keep the logs for my XL files within the file itself. This is what I do:

Add a new Worksheet, name it "Log" and change the Code Name of the Sheet to shLog, then I put this code into the Workbook Object Code Module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
LastRow = shLog.[A65536].End(xlUp).Row
If LastRow = 1 Then
    shLog.Cells(LastRow, 1) = "User"
    shLog.Cells(LastRow, 2) = "Log On Date/Time"
    shLog.Cells(LastRow, 3) = "Log Off Date/Time"
    shLog.[A1:C1].Font.Bold = True
    shLog.Cells(LastRow + 1, 1) = Application.UserName
    shLog.Cells(LastRow + 1, 2) = Format(Now, "mmmm d, yyyy h:mm AM/PM")
    shLog.Cells(LastRow + 1, 3) = Format(Now, "mmmm d, yyyy h:mm AM/PM")
Else
    shLog.Cells(LastRow, 3) = Format(Now, "mmmm d, yyyy h:mm AM/PM")
End If
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim NextRow As Long
NextRow = shLog.[A65536].End(xlUp).Offset(1, 0).Row
If NextRow = 1 Then
    shLog.Cells(NextRow, 1) = "User"
    shLog.Cells(NextRow, 2) = "Log On Date/Time"
    shLog.Cells(NextRow, 3) = "Log Off Date/Time"
    shLog.[A1:C1].Font.Bold = True
    shLog.Cells(NextRow + 1, 1) = Application.UserName
    shLog.Cells(NextRow + 1, 2) = Format(Now, "mmmm d, yyyy h:mm AM/PM")
Else
    shLog.Cells(NextRow, 1) = Application.UserName
    shLog.Cells(NextRow, 2) = Format(Now, "mmmm d, yyyy h:mm AM/PM")
End If
End Sub

Set shLog's .Visible property to xlSheetVeryHidden and there you go.

This is a simple version of a Workbook Log "File", and it can definately be expanded to do whatever you need, but it should get you started!

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sedgely]

Cheers Mike
Will 'play' with that this mornin and let you know

Craig