Sub LogInformation(LogMessage As String)
Const LogFileName As String = "\\phx1ns01\sales\Brand\Log Texts\log.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

Then use the above like this:

Private Sub Workbook_Open()
LogInformation ThisWorkbook.Name & " opened by " & _
Application.UserName & " " & Format(Date, "yyyy-mm-dd hh:mm")
End Sub

The above code courtesy of www.exceltip.com


"Tony Bender" wrote in message
om...
I built a VB application allowing personnel in our company access to
numerous Excel files on our share drive. To get a sense as to which
users were using which reports I added the following macro to this
application which logs the user name and the Excel workbook they
retrieved. This worked fine for years until we moved to Excel 2000.
Now the user gets the "Run Time Error 75 Path/File Access Error", upon
clicking 'end' they still get the report, but it is an annoyance I'd
like to correct.

I went to Microsoft's Knowledge base regarding 'Run Time Error 75',
and it said there is problem with VBA when the Name statement is
involved. However I did not understand the language of the
'work-around'.

I greatly appreciate any ideas on how I can resolve this.

Thank you....


Option Explicit
Private Sub Workbook_Open()

Dim LogDir As String
Dim LogFile As String
Dim myFileNum As Long
Dim testDir As String

'**This is the name and location of the log on the shared

LogDir = "\\phx1ns01\sales\Brand\Log Texts\"
LogFile = LogDir & "\log.txt"

testDir = ""
On Error Resume Next

testDir = Dir(LogDir, vbDirectory)
On Error GoTo 0

If testDir = "" Then
'not connected or spelling error!
Exit Sub
End If

myFileNum = FreeFile()

Open LogFile For Append As #myFileNum
Print #myFileNum, ThisWorkbook.FullName & vbTab _
& Application.UserName & vbTab & Now
Close #myFileNum

End Sub