Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loging users who open a file
I like to log the users who open spreadsheets that I have developed. What I
have done in the past is to run the code below to record the information that I need. Dim Log As Range 'Set the range for the user log Set Log = Sheet6.Range("A2") 'Find the first empty cell Do Until IsEmpty(Log) Set Log = Log.Offset(1, 0) Loop 'Record user name and time opened ActiveCell.Value = fOSUserName() ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() 'Save the changes ActiveWorkbook.Save The workbooks I have typically developed are summary in nature and do not take up much more than half a meg. This process would run and there would a very small delay and the user didn't know there was anything happening. (The fact that I capture this data is common knowlege though.) We just upgraded to Excel 2007 and I have started to develop larger workbooks that I want to track. The lag is enough that it will start to cause problems. Is there a better way to do this? Can I write to an un-opened book or a text-file? I am open to any suggestions. Thanks! PJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loging users who open a file
i've used something like this: Sub test() Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet3") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row With ws .Range("A" & lastrow).Offset(1).Value = Environ("Username") .Range("B" & lastrow).Offset(1).Value = Date End With End Sub -- Gary "PJFry" wrote in message ... I like to log the users who open spreadsheets that I have developed. What I have done in the past is to run the code below to record the information that I need. Dim Log As Range 'Set the range for the user log Set Log = Sheet6.Range("A2") 'Find the first empty cell Do Until IsEmpty(Log) Set Log = Log.Offset(1, 0) Loop 'Record user name and time opened ActiveCell.Value = fOSUserName() ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() 'Save the changes ActiveWorkbook.Save The workbooks I have typically developed are summary in nature and do not take up much more than half a meg. This process would run and there would a very small delay and the user didn't know there was anything happening. (The fact that I capture this data is common knowlege though.) We just upgraded to Excel 2007 and I have started to develop larger workbooks that I want to track. The lag is enough that it will start to cause problems. Is there a better way to do this? Can I write to an un-opened book or a text-file? I am open to any suggestions. Thanks! PJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loging users who open a file
A simple text file should be OK, call this in the open event
Sub test() Dim sFile As String Dim sText Dim ff As Long sFile = Application.DefaultFilePath ' or maybe 'sFile = ThisWorkbook.Path If Right$(sFile, 1) < "\" Then sFile = sFile & "\" sFile = sFile & "logTest.txt" sText = "ABC" & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss") ff = FreeFile Open sFile For Append As #ff Print #ff, sText Close #ff End Sub I don't know what fOSUserName() is but replace the "ABC" with it. Regards, Peter T "PJFry" wrote in message ... I like to log the users who open spreadsheets that I have developed. What I have done in the past is to run the code below to record the information that I need. Dim Log As Range 'Set the range for the user log Set Log = Sheet6.Range("A2") 'Find the first empty cell Do Until IsEmpty(Log) Set Log = Log.Offset(1, 0) Loop 'Record user name and time opened ActiveCell.Value = fOSUserName() ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() 'Save the changes ActiveWorkbook.Save The workbooks I have typically developed are summary in nature and do not take up much more than half a meg. This process would run and there would a very small delay and the user didn't know there was anything happening. (The fact that I capture this data is common knowlege though.) We just upgraded to Excel 2007 and I have started to develop larger workbooks that I want to track. The lag is enough that it will start to cause problems. Is there a better way to do this? Can I write to an un-opened book or a text-file? I am open to any suggestions. Thanks! PJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loging users who open a file
Peter,
That worked great! The fOSUserName() is a function that captures that persons log in name. It is very handy for executing certain actions when a user opens a workbook. For example, I have some workbooks set to only allow printing if one user opens it, but full access if my boss opens it. Here is the code: Private Declare Function apiGetUserName _ Lib "advapi32.dll" Alias "GetUserNameA" _ (ByVal lpBuffer As String, nSize As Long) As Long Function fOSUserName() As String On Error GoTo fOSUserName_Err 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 fOSUserName_Exit: Exit Function fOSUserName_Err: MsgBox Error$ Resume fOSUserName_Exit End Function Thanks again! PJ "Peter T" wrote: A simple text file should be OK, call this in the open event Sub test() Dim sFile As String Dim sText Dim ff As Long sFile = Application.DefaultFilePath ' or maybe 'sFile = ThisWorkbook.Path If Right$(sFile, 1) < "\" Then sFile = sFile & "\" sFile = sFile & "logTest.txt" sText = "ABC" & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss") ff = FreeFile Open sFile For Append As #ff Print #ff, sText Close #ff End Sub I don't know what fOSUserName() is but replace the "ABC" with it. Regards, Peter T "PJFry" wrote in message ... I like to log the users who open spreadsheets that I have developed. What I have done in the past is to run the code below to record the information that I need. Dim Log As Range 'Set the range for the user log Set Log = Sheet6.Range("A2") 'Find the first empty cell Do Until IsEmpty(Log) Set Log = Log.Offset(1, 0) Loop 'Record user name and time opened ActiveCell.Value = fOSUserName() ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() 'Save the changes ActiveWorkbook.Save The workbooks I have typically developed are summary in nature and do not take up much more than half a meg. This process would run and there would a very small delay and the user didn't know there was anything happening. (The fact that I capture this data is common knowlege though.) We just upgraded to Excel 2007 and I have started to develop larger workbooks that I want to track. The lag is enough that it will start to cause problems. Is there a better way to do this? Can I write to an un-opened book or a text-file? I am open to any suggestions. Thanks! PJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open a file by multiple users | Excel Discussion (Misc queries) | |||
2 users open same file and both can edit/change the file | Excel Discussion (Misc queries) | |||
Possible for users to open file at the same time? | Excel Programming | |||
Help - Automating a file.. Adding Users , Deleting users, Changing | Excel Worksheet Functions | |||
Multiple excel users can open the same file and edit at the same . | Excel Discussion (Misc queries) |