Determine who last modified an Excel file????
We work in a networked environment and there are only
2 users who have access to our Excel file. These 2 users never work the same shift, so therefore only one user will be modifying this file at any time. My question is, is there any VBA code to determine who was the last user to modify/save an Excel 2007 file?? Sometimes I would like to see who was the last user to edit this file. Thank you! |
Determine who last modified an Excel file????
i usually just create a hidden sheet. then in the thisworkbook before close
module, add worksheets("Hidden Sheet name").range("A1").value =environ("Username") -- Gary Keramidas Excel 2003 "Robert Crandal" wrote in message ... We work in a networked environment and there are only 2 users who have access to our Excel file. These 2 users never work the same shift, so therefore only one user will be modifying this file at any time. My question is, is there any VBA code to determine who was the last user to modify/save an Excel 2007 file?? Sometimes I would like to see who was the last user to edit this file. Thank you! |
Determine who last modified an Excel file????
sorry, meant before save.
-- Gary Keramidas Excel 2003 "Robert Crandal" wrote in message ... We work in a networked environment and there are only 2 users who have access to our Excel file. These 2 users never work the same shift, so therefore only one user will be modifying this file at any time. My question is, is there any VBA code to determine who was the last user to modify/save an Excel 2007 file?? Sometimes I would like to see who was the last user to edit this file. Thank you! |
Determine who last modified an Excel file????
robert,
you could create a logfile using notepad application. See if following is of any help: '***********Standard MODULE(S)*********** Option Explicit Public Function LogUserData(LogUser As String) Dim FilePath As String Dim FolderName As String Dim FileName As String On Error GoTo MakeFolder AddData: With ThisWorkbook FilePath = .Path FileName = Left(.Name, Len(.Name) - 4) End With FolderName = "UserLog" Open FilePath & "\" & FileName & _ " UserLog.Log" For Append As #1 Print #1, LogUser Close #1 Exit Function MakeFolder: MsgBox (Error(Err)) 'folder missing so create it MkDir ThisWorkbook.Path & "\UserLog" Resume AddData End Function Sub atest() LogUserData "Tested by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub Sub OpenNotepad() Dim FileToOpen As Variant FileToOpen = Application _ .GetOpenFilename("Text Files (*.log), *.log", Title:="Open UserLog File") If FileToOpen < False Then Shell "notepad.exe " & FileToOpen, vbNormalFocus End If End Sub '*****THISWORKBOOK MODULE**** Private Sub Workbook_Open() LogUserData "Opened by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) LogUserData "Saved by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) LogUserData "Closed by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub -- jb "Robert Crandal" wrote: We work in a networked environment and there are only 2 users who have access to our Excel file. These 2 users never work the same shift, so therefore only one user will be modifying this file at any time. My question is, is there any VBA code to determine who was the last user to modify/save an Excel 2007 file?? Sometimes I would like to see who was the last user to edit this file. Thank you! . |
Determine who last modified an Excel file????
wb.BuiltinDocumentProperties("Last author")
where wb refers to the workbook Regards, Peter T "Robert Crandal" wrote in message ... We work in a networked environment and there are only 2 users who have access to our Excel file. These 2 users never work the same shift, so therefore only one user will be modifying this file at any time. My question is, is there any VBA code to determine who was the last user to modify/save an Excel 2007 file?? Sometimes I would like to see who was the last user to edit this file. Thank you! |
Determine who last modified an Excel file????
Thank you very much! It is much appreciated!
"john" wrote in message ... robert, you could create a logfile using notepad application. See if following is of any help: '***********Standard MODULE(S)*********** Option Explicit Public Function LogUserData(LogUser As String) Dim FilePath As String Dim FolderName As String Dim FileName As String On Error GoTo MakeFolder AddData: With ThisWorkbook FilePath = .Path FileName = Left(.Name, Len(.Name) - 4) End With FolderName = "UserLog" Open FilePath & "\" & FileName & _ " UserLog.Log" For Append As #1 Print #1, LogUser Close #1 Exit Function MakeFolder: MsgBox (Error(Err)) 'folder missing so create it MkDir ThisWorkbook.Path & "\UserLog" Resume AddData End Function Sub atest() LogUserData "Tested by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub Sub OpenNotepad() Dim FileToOpen As Variant FileToOpen = Application _ .GetOpenFilename("Text Files (*.log), *.log", Title:="Open UserLog File") If FileToOpen < False Then Shell "notepad.exe " & FileToOpen, vbNormalFocus End If End Sub '*****THISWORKBOOK MODULE**** Private Sub Workbook_Open() LogUserData "Opened by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) LogUserData "Saved by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) LogUserData "Closed by " & Application.UserName & _ " " & Format(Now, "dd mmm yyyy hh:mm:ss") End Sub -- jb |
All times are GMT +1. The time now is 12:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com