Excel log

[mani06ah]

Hi all,

I was wondering if there is any way to create a simple log in excel, that logs the date and time of opening and closing of Office files? Do any of you guys have any idea on how to do that?

Thank you in advance

Best regards

 

[Scott T]

In Excel press Alt+F11 to open the VBA editor

copy the below code into the thisworkbook module of your workbook. the workbook must be saved as a file type that allows macros such as .xlsm

Note this code will save the file on close so that the date stamp is saved. If you want to close with out saving you will need to either stop macros from running or comment out the save command.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lr As Long
lr = Sheets("log").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("log").Cells(lr + 1, 1) = "closed"
Sheets("log").Cells(lr + 1, 2) = Now
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()

Dim lr As Long
lr = Sheets("log").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("log").Visible = xlVeryHidden 'assuming you do not want people to see the log
Sheets("log").Cells(lr + 1, 1) = "Opened"
Sheets("log").Cells(lr + 1, 2) = Now
End Sub