Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
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. Im 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. Id like to copy/paste this information to a Word document (or a text file), which could be located on the C-drive. Ideally, Id 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-- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
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 -- Dan On Apr 1, 1:02 pm, ryguy7272 wrote: 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-- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
Ever since I saw it, I prefer this much simpler version:
Dim strUser As String strUser = Environ("username") --JP On Apr 1, 2:15*pm, "Dan R." wrote: 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 -- Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
One way:
I have a routine that can write whatever you want to an external (csv) file. Look for "Spreadsheet Logging to CSV file" he http://codeforexcelandoutlook.com/excel.html HTH, JP On Apr 1, 2:02 pm, ryguy7272 wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
Much simpler indeed... thanks JP.
-- Dan On Apr 1, 1:56*pm, JP wrote: Ever since I saw it, I prefer this much simpler version: Dim strUser As String strUser = Environ("username") --JP On Apr 1, 2:15*pm, "Dan R." wrote: 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 -- Dan- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
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--- -- RyGuy "JP" wrote: One way: I have a routine that can write whatever you want to an external (csv) file. Look for "Spreadsheet Logging to CSV file" he http://codeforexcelandoutlook.com/excel.html HTH, JP On Apr 1, 2:02 pm, ryguy7272 wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
Thanks Ryan!
FYI for anyone else reading, I did not write that code. Thx, JP On Apr 2, 2:36*pm, ryguy7272 wrote: 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--- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify User, Copy/Paste Login Info. to File on C:\
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 "ryguy7272" wrote: 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--- -- RyGuy "JP" wrote: One way: I have a routine that can write whatever you want to an external (csv) file. Look for "Spreadsheet Logging to CSV file" he http://codeforexcelandoutlook.com/excel.html HTH, JP On Apr 1, 2:02 pm, ryguy7272 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify User, Copy/Paste Login Info. to External File | Excel Programming | |||
Creating a User login Log file for an Excel workbook | Excel Programming | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Help! Find info then copy and paste | Excel Programming | |||
Complex identify values then cut/copy/paste query | Excel Programming |