Log users activity - open & close

[Steven101]

Hi, I currently have a workbook that when opened updates a Notepad with the users name, date & time opened. using the below code...

Private Sub Workbook_Open()
Sheets("Select Handler").Select
Open "myfilelcation.log" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Application.UserName, Now
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

The log file is not vey user friendly and was hoping to save an excel workbook rather than a notepad in the following format;

Column A Username
Column B Date opened
Column C Time opened
Column D Date Closed
Column E Time Closed

Any help would be appreciated.

 

[netusimcotudam]

Try this code,
you Will have to change name of the log sheet, and it should work

Private Sub Workbook_Open()Dim lastrow As Integer
Dim user As String


user = Environ$("username")
    lastrow = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Log").Range("A" & lastrow + 1).Value = user
Sheets("Log").Range("B" & lastrow + 1).Value = Format(Now(), "yyyy.mm.dd")
Sheets("Log").Range("C" & lastrow + 1).Value = Format(Now(), "hh:mm")
End Sub




Private Sub Workbook_BeforeClose(Cancel As Boolean)


lastrow = Sheets("Log").Range("D" & Rows.Count).End(xlUp).Row
Sheets("Log").Range("D" & lastrow + 1).Value = Format(Now(), "yyyy.mm.dd")
Sheets("Log").Range("E" & lastrow + 1).Value = Format(Now(), "hh:mm")
End Sub

 

[Steven101]

Thank for the response... I have amended slightly to get what I was looking for. My issue is I ant the Log Sheet (workbook) to be different workbook.

Say: C:\Users\Desktop\Log Test Input.xlsm

Private Sub Workbook_Open()

Dim lastrow As Integer
Dim user As String

user = Application.UserName
lastrow = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Log").Range("A" & lastrow + 1).Value = user
Sheets("Log").Range("B" & lastrow + 1).Value = Format(Now(), "dd/mm/yyyy")
Sheets("Log").Range("C" & lastrow + 1).Value = Format(Now(), "hh:mm:ss")
Sheets("Log").Range("D" & lastrow + 1).Value = "Open"
End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastrow As Integer
Dim user As String
user = Application.UserName
lastrow = Sheets("Log").Range("D" & Rows.Count).End(xlUp).Row

Location.Sheets("Log").Range("A" & lastrow + 1).Value = user
Location.Sheets("Log").Range("B" & lastrow + 1).Value = Format(Now(), "dd/mm/yyyy")
Location.Sheets("Log").Range("C" & lastrow + 1).Value = Format(Now(), "hh:mm:ss")
Location.Sheets("Log").Range("D" & lastrow + 1).Value = "Closed"
End Sub

Thanks

 

[GR00007]

You may want to keep a flat file for speed of appending. That file can be a .csv which will open in Excel with delimited columns.

Private Sub Workbook_Open()
Sheets("Select Handler").Select
vLog = Application.UserName & "," & Format$(Now, "m/d/yyyy") & "," & Format(Now, "hh:mm") & ",Open"
Open "C:\[COLOR=#574123]Users\Desktop\Log Test Input.[/COLOR]csv" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , vLog
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
vLog = Application.UserName & "," & Format$(Now, "m/d/yyyy") & "," & Format(Now, "hh:mm") & ",Close"
Open "C:\[COLOR=#574123]Users\Desktop\Log Test Input.[/COLOR]csv" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , vLog
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub