Identify User, Copy/Paste Login Info. to File on C:\

[ryguy7272]

I originally posted this a few days ago and got only one response, which
although helpful, didn't allow me to do what I was hoping to do. I’m
wondering if there is a way to create a private sub to identify someone who
has opened a spreadsheet, by the windows login ID, and also get the time and
date that the spreadsheet was opened. I’d like to copy/paste this information
to a Word document (or a text file), which could be located on the C-drive.
Ideally, I’d like to create a running log of all instances of users opening
the spreadsheet, so the 2nd, 3rd, 4th, etc. users would have to be appended
to the Word document so that the original time stamps were not overwritten.

I have a time stamping macro, but everything is done right in Excel,
not outside of Excel.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$b$400")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
..Select
..Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = InputBox("You've made a change to the Rates tab.
Please enter your name here for historical purposes.")
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub

How can I send this data to a destination outside of Excel, and append data
as login information of users opening the file?


Regards,
Ryan--

 

[Dan R.]

Here's a function to get the username:

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" ( _
ByVal lpBuffer As String, nSize As Long) As Long

Public Function ReturnNTUserName() As String
Dim rString As String * 255, sLen As Long
Dim NWUserName As String, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
NWUserName = Left(Right(tString, _
Len(tString) - 1), Len(tString) - 2)
ReturnNTUserName = Left(tString, 1) _
+ NWUserName + Right(tString, 1)
End Function

 

[JP]

Ever since I saw it, I prefer this much simpler version:

Dim strUser As String
strUser = Environ("username")


--JP

 

[JP]

One way:

I have a routine that can write whatever you want to an external (csv)
file. Look for "Spreadsheet Logging to CSV file" here:

http://codeforexcelandoutlook.com/excel.html


HTH,
JP

 

[Dan R.]

Much simpler indeed... thanks JP.

--
Dan

Ever since I saw it, I prefer this much simpler version:

Dim strUser As String
strUser = Environ("username")

--JP





- Show quoted text -

 

[ryguy7272]

Very, very cool site. Thanks!!

I went with this, module code, not sheet code:
Private Sub Auto_Open()

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

End Sub

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\MyLog.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

Regards,
Ryan---

 

[JP]

Thanks Ryan!

FYI for anyone else reading, I did not write that code.


Thx,
JP

 

[SANDIND]

Hi ryguy

I am using the code below and pasting the same in one of the sheets module.
But the code is turning into RED. I am new to VB please guide me how to use
the same.

I also create one txt file in the C drive. do i need to do something else.

Thanks for your help in advance