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" <(E-Mail Removed)> wrote in message
news:73521595-E073-443E-BCC8-(E-Mail Removed)...
> 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!
>
>
>
>
>