Loging users who open a file

[PJFry]

I like to log the users who open spreadsheets that I have developed. What I
have done in the past is to run the code below to record the information that
I need.

Dim Log As Range

'Set the range for the user log
Set Log = Sheet6.Range("A2")

'Find the first empty cell
Do Until IsEmpty(Log)
Set Log = Log.Offset(1, 0)
Loop

'Record user name and time opened
ActiveCell.Value = fOSUserName()
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()

'Save the changes
ActiveWorkbook.Save


The workbooks I have typically developed are summary in nature and do not
take up much more than half a meg. This process would run and there would a
very small delay and the user didn't know there was anything happening. (The
fact that I capture this data is common knowlege though.)

We just upgraded to Excel 2007 and I have started to develop larger
workbooks that I want to track. The lag is enough that it will start to
cause problems.

Is there a better way to do this? Can I write to an un-opened book or a
text-file?
I am open to any suggestions.

Thanks!
PJ

 

[Gary Keramidas]

i've used something like this:

Sub test()
Dim ws As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Sheet3")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
With ws
.Range("A" & lastrow).Offset(1).Value =
Environ("Username")
.Range("B" & lastrow).Offset(1).Value = Date
End With
End Sub

 

[Peter T]

A simple text file should be OK, call this in the open event

Sub test()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = "ABC" & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")

ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff

End Sub

I don't know what fOSUserName() is but replace the "ABC" with it.

Regards,
Peter T

 

[PJFry]

Peter,

That worked great!

The fOSUserName() is a function that captures that persons log in name. It
is very handy for executing certain actions when a user opens a workbook.
For example, I have some workbooks set to only allow printing if one user
opens it, but full access if my boss opens it.

Here is the code:
Private Declare Function apiGetUserName _
Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As
Long

Function fOSUserName() As String
On Error GoTo fOSUserName_Err

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


fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit
End Function

Thanks again!
PJ