I would like a macro that logs the username and date/time the file was opened
and sends it to a txt file that is on my C: drive. I can get it to work for
myself, but if another user opens the file, it doesn't log it. Is there a
way to run the macro as soon as the other user opens the file? I thought I
had it down pat because it works for myself, but discovered it was wrong when
a coworker opened and closed the file for me.
Here is the code I have so far:
Sub Auto_Open()
LogInformation ThisWorkbook.Name & " opened by " & _
Application.UserName & " " & Format(Date, "yyyy-mm-dd") & " " & _
Format(Time, "hh:mm")

End Sub

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\Data\MyLog.txt"
Dim FileNum As Integer
FileNum = FreeFile
' next file number
Open LogFileName For Append As #FileNum
' creates the file if it doesn't exist
Print #FileNum, LogMessage
' write information at the end of the text file
Close #FileNum ' close the file
End Sub

Thank you in advance for your assistance!

This is maybe too obvious, but since it's another user, is he on another
machine? If so, it's probably logging it just fine -- to C:\Data\MyLog.txt,
on his machine in other words.

--- "mrsviqt" wrote:
I would like a macro that logs the username and date/time the file was opened
and sends it to a txt file that is on my C: drive. I can get it to work for
myself, but if another user opens the file, it doesn't log it. Is there a
way to run the macro as soon as the other user opens the file? I thought I
had it down pat because it works for myself, but discovered it was wrong when
a coworker opened and closed the file for me.
Here is the code I have so far:
Sub Auto_Open()
LogInformation ThisWorkbook.Name & " opened by " & _
Application.UserName & " " & Format(Date, "yyyy-mm-dd") & " " & _
Format(Time, "hh:mm")
End Sub

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\Data\MyLog.txt"
Dim FileNum As Integer
FileNum = FreeFile
' next file number
Open LogFileName For Append As #FileNum
' creates the file if it doesn't exist
Print #FileNum, LogMessage
' write information at the end of the text file
Close #FileNum ' close the file
End Sub

Thank you in advance for your assistance!

its unlikely that another PC will have access to your C drive. If thats
possible, have a word with your netwirk admin team cos thats a BAD thing!

suggest that you create a folder on a shared drive that all users can write
to, but can't delete from.

"mrsviqt" wrote in message
...
I would like a macro that logs the username and date/time the file was
opened
and sends it to a txt file that is on my C: drive. I can get it to work
for
myself, but if another user opens the file, it doesn't log it. Is there a
way to run the macro as soon as the other user opens the file? I thought
I
had it down pat because it works for myself, but discovered it was wrong
when
a coworker opened and closed the file for me.
Here is the code I have so far:
Sub Auto_Open()
LogInformation ThisWorkbook.Name & " opened by " & _
Application.UserName & " " & Format(Date, "yyyy-mm-dd") & " " & _
Format(Time, "hh:mm")

End Sub

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\Data\MyLog.txt"
Dim FileNum As Integer
FileNum = FreeFile
' next file number
Open LogFileName For Append As #FileNum
' creates the file if it doesn't exist
Print #FileNum, LogMessage
' write information at the end of the text file
Close #FileNum ' close the file
End Sub

Thank you in advance for your assistance!