Creating a User login Log file for an Excel workbook

[Murphybp2]

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 worksheet 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]

If you put the log in a worksheet 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.

 

[Gabor]

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]

Add a line in the before_save event to call that subroutine:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call DoTheLog(myKey:="Saved")
End Sub

(This goes in the ThisWorkbook module.)

 

[Gerencsér Gábor]

Dave,
What is the relevance of placing the code in the ThisWorkbook module versus
a normal module ?
I have put your initial set of codes into a normal module, they work like a
charm.
The before_save stuff I put into the ThisWorkbook module as per your
instruction, no problem, the codes seem to work together fine.
Gabor

By the way, I modified one of the codes so that the name of the logfile will
indicate the original filename. Just in case on the drive there will be more
of these:

Sub DoTheLog(myKey As String)
On Error GoTo 44
Sheets("Log").Select
Range("A1").Select
Do Until ActiveCell = Empty
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.FormulaR1C1 = myKey
ActiveCell.Offset(0, 1).FormulaR1C1 = Application.UserName
ActiveCell.Offset(0, 2).FormulaR1C1 = fOSUserName
ActiveCell.Offset(0, 3).FormulaR1C1 = fOSMachineName
ActiveCell.Offset(0, 4).FormulaR1C1 = Date + Time 'Format(Now, "mmmm dd,
yyyy hh:mm:ss")

If myKey = "Logged out" Then
ActiveCell.Offset(0, 5).FormulaR1C1 = ActiveCell.Offset(0, 4) -
ActiveCell.Offset(-1, 4)
End If
Sheets(1).Select

44 Open ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & "_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

 

[Dave Peterson]

I think the name change is good.

And excel monitors for lots of things. In older versions, auto_open and
auto_close were procedures that were run each time the workbook opened or
closed.

With xl97, MS included a bunch more events--both at the worksheet level (looking
for typing changes or selection changes or lots more stuff) and at the workbook
level (beforesave, beforeprint, and lots more stuff).

But each of these event types have specific homes--behind individual worksheets
or behind the ThisWorkbook module. If they're not in the correct location, then
excel doesn't know that you've tried to tap into them with your code.

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

Dave,
What is the relevance of placing the code in the ThisWorkbook module versus
a normal module ?
I have put your initial set of codes into a normal module, they work like a
charm.
The before_save stuff I put into the ThisWorkbook module as per your
instruction, no problem, the codes seem to work together fine.
Gabor

By the way, I modified one of the codes so that the name of the logfile will
indicate the original filename. Just in case on the drive there will be more
of these:

Sub DoTheLog(myKey As String)
On Error GoTo 44
Sheets("Log").Select
Range("A1").Select
Do Until ActiveCell = Empty
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.FormulaR1C1 = myKey
ActiveCell.Offset(0, 1).FormulaR1C1 = Application.UserName
ActiveCell.Offset(0, 2).FormulaR1C1 = fOSUserName
ActiveCell.Offset(0, 3).FormulaR1C1 = fOSMachineName
ActiveCell.Offset(0, 4).FormulaR1C1 = Date + Time 'Format(Now, "mmmm dd,
yyyy hh:mm:ss")

If myKey = "Logged out" Then
ActiveCell.Offset(0, 5).FormulaR1C1 = ActiveCell.Offset(0, 4) -
ActiveCell.Offset(-1, 4)
End If
Sheets(1).Select

44 Open ThisWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & "_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

 

[Gerencsér Gábor]

Thank you.
I think I have to study this in more depth.
Gábor

 

[matpj]

this is an excellent bit of code.

I'm wondering how I can specify a path for the usage log - as I don't
want it stored where the worksheet is.

I've tried this:

Code:
--------------------
Open "Cs_Fs1\Prodvol\Techserv\PMR\Reporting\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & "_usage.log" For Append As #1
--------------------
but it doesn't work.

i've also tried putting '\\' before the path, as per my drive mappings
in windows explorer.

can anyone suggest how I can specify a path?

thanks,
Matt