Re: Creating a User login Log file for an Excel workbook

Tech-Archive recommends: Speed Up your PC by fixing your registry

Dave,

The below procedure works fine with the usage.log.
How can I get the Save event recorded in the log file as well ?

Cheers, Gabor

"Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:441B1F00.62E16F1D@xxxxxxxxxxxxxxxxxxx
If you put the log in a work*** in the same workbook, then the user (or
your
code) will have to save their changes--so that the log is saved.

This could be a problem if the user opens the workbook, destroys it (by
accident) and wants to close without saving.

An alternative approach would be to a text file (maybe in the same folder
as the
workbook) that gets updated each time the workbook opens and each time the
workbook closes.


Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 255
strCompName = String$(lngLen - 1, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function
Sub auto_open()
Call DoTheLog(myKey:="Logged In")
End Sub
Sub auto_close()
Call DoTheLog(myKey:="Logged Out")
End Sub
Sub DoTheLog(myKey As String)
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, myKey & vbTab & Application.UserName _
& vbTab & fOSUserName _
& vbTab & fOSMachineName _
& vbTab & Format(Now, "mmmm dd, yyyy hh:mm:ss")
Close #1
End Sub


This may actually give you a false message when they close the file. If
they
get the "do you want to save" prompt and answer Cancel, then the "logged
out"
message has already been written.


Murphybp2@xxxxxxxxx wrote:

I would like to create a simple log file for a shared workbook I have.
Preferably I'd like the log to be on a hidden work*** within the
workbook. All I need to capture is the userID, Date/time the workbook
was opened, and the date/time the workbook was closed. Can some one
tell me how I would go about doing this. I imagine I would have to use
some VBA to accomplish this. Thanks.

--

Dave Peterson


.

Quantcast