Get the last user who edit the Excel through VBA?-CodePudding

I want to show the last editor's name in the Excel file, because there are some models that can be used by all department members.

Is it possible to get the last editor's name who edited the Excel through Excel VBA?

CodePudding user response：

You may benefit from the built-in property "last author" which gets refreshed with each saving and can be read by the following function:


Private Function LastAuthor() As String
    Dim prop As Object
    On Error Resume Next
    Set prop = ThisWorkbook.BuiltinDocumentProperties("last author")
    If Err.Number = 0 Then
        LastAuthor = prop.Value
    Else
        LastAuthor = "Not yet documented!"
    End If
End Function

Another built-in property of interest might be "Last save time".

CodePudding user response：

I think the easiest way to do this is to use this built-in function.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

User = Application.UserName

'Save the user and probably date time in wherever you want

End Sub

You need to insert the code inside 'ThisWorkbook' module:

CodePudding user response：

you can create a macros for the event Workbook_Open that writes a current username in some log file. On https://support.microsoft.com they have a sub to get the current username

' Makes sure all variables are dimensioned in each subroutine.     
Option Explicit     
' Access the GetUserNameA function in advapi32.dll and     ' call the function GetUserName.     
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long    
 ' Main routine to Dimension variables, retrieve user name     
' and display answer.     
Sub Get_User_Name()     
' Dimension variables    
 Dim lpBuff As String * 25     
Dim ret As Long, UserName As String     
' Get the user name minus any trailing spaces found in the name.    
 ret = GetUserName(lpBuff, 25)     
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)     
' Display the User Name     
MsgBox UserName     
End Sub