Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
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. wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
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" schrieb im Newsbeitrag ... 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. wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
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.) Gabor wrote: 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" schrieb im Newsbeitrag ... 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. wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
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 "Gerencsér Gábor" wrote: 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" az alábbiakat írta a következo hírüzenetben: ... 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.) Gabor wrote: 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" schrieb im Newsbeitrag ... 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
Thank you.
I think I have to study this in more depth. Gábor "Dave Peterson" az alábbiakat írta a következo hírüzenetben: ... 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 "Gerencsér Gábor" wrote: 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" az alábbiakat írta a következo hírüzenetben: ... 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.) Gabor wrote: 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" schrieb im Newsbeitrag ... 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User login Log file for an Excel workbook
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 -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=523717 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to automaticaly insert user name (login name) in excel? | Excel Worksheet Functions | |||
making NT user/login user appear in a cell | Excel Worksheet Functions | |||
Open Specific Form by User Login | Excel Discussion (Misc queries) | |||
How do I automatically insert the user (login name) in Excel? | Excel Discussion (Misc queries) | |||
User cannot save excel file in workbook | Excel Worksheet Functions |