Log Network ID

[Randall Roberts]

Ok here is what i need to do

We have a excel file on a shared drive
the spreadsheet has no password and can be opened by anyone

What i am hoping to do is use a macro that when it is opened will
record the date time and the network user id (login name)

of the person that opened it and save it to a sheet

is this possible

 

[Ryan Poth]

Randall,

I'm not sure how to get the network user name, but I can offer a suggestion
using the Excel user name. Also, I just put the messages into a text file,
rather than an excel file, to show a quick & dirty solution. Place the
following code in a Workbook event module, by right-clicking on the title bar
of your workbook and selecting "View code". Obviously, you should change the
file path/name and message text to suit your needs.

Private Sub Workbook_Open()
Open "c:\temp\mylog.txt" For Append As #1
Print #1, "Spreadsheet opened by " & Application.UserName & " on " & Now
Close #1
End Sub

 

[Dave Peterson]

One way to get the network id (in a General module):

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (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

Then this line:
Print #1, "Spreadsheet opened by " & Application.UserName & " on " & Now
becomes
Print #1, "Spreadsheet opened by " & fOSUserName & " on " & Now