excel workbook time log
Hi - I'm trying to create a workbook template that will log when a
user opens a workbook, and what time they close the document, to give folks an idea of how much time they spend in a given worksheet. Time management data, if you will. (I know the flaw in the logic is that having a workbook open doesn't mean they're working in it, necessarily, but it's a first step toward document management, anyway.) The idea is to use the NOW() and Auto_Open (or Workbook_Open) functions, and paste the value from the NOW() on a sheet upon open and exit, but not having a great deal of success figuring out how to create a timestamp or echo the username. Any assistance is appreciated. |
excel workbook time log
Use this function to get the username:
Public Declare Function GetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Public Function OSGetUserName2() As String On Error GoTo myend Dim strBuff As String * 25 Dim strName As String Dim lngX As Long lngX = GetUserName(strBuff, 25) OSGetUserName2 = strBuff If (Len(OSGetUserName2) = 0) Then OSGetUserName2 = "unknown" myend: End Function Then you just need to write to the sheet. Something like Sub foo() With Sheets("TimeSheet") .[a1].Value = OSGetUserName2 .[b1].Value = Now() End With End Sub Of course, this will need to be tweaked to get the result you are after, but it should get you headed in the right direction. HTH -Jeff- wrote: Hi - I'm trying to create a workbook template that will log when a user opens a workbook, and what time they close the document, to give folks an idea of how much time they spend in a given worksheet. Time management data, if you will. (I know the flaw in the logic is that having a workbook open doesn't mean they're working in it, necessarily, but it's a first step toward document management, anyway.) The idea is to use the NOW() and Auto_Open (or Workbook_Open) functions, and paste the value from the NOW() on a sheet upon open and exit, but not having a great deal of success figuring out how to create a timestamp or echo the username. Any assistance is appreciated. |
excel workbook time log
If the User Name from the Tools|Option dialog box is wanted (General
tab), then you could simply use: Application.UserName Only caveat is that some users only enter their initials, or something else, like "C3PO", if they are a Star Trek fan! (Grin!) -- Regards, Bill Renaud |
All times are GMT +1. The time now is 04:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com