find the user of a Excel file using VBA 1

[gungfukid]

Can you find the author or the current user of an excel file using a VBA procedure/function?

If so, what is it?

Thanks!

 

[dsi]

Not quite sure what answer you want. For example, if the file is opened, do you want the user that is currently logged on? Or, if the file is already opened by another user, do you want to know who it is?

For the first case:

Option Explicit

Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, _
    nSize As Long) As Long

Sub Main()
    Call API_Method
    Call Env_Method
End Sub

Sub API_Method()
    Dim lpBuff As String * 25
    Dim ret As Long
    Dim sUser As String

    ret = GetUserName(lpBuff, 25)
    sUser = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
    sUser = UCase(sUser)

    MsgBox sUser
End Sub

Sub Env_Method()
    Dim sUser As String
    sUser = UCase(Environ("username"))
    MsgBox sUser
End Sub

Although grabbing the environment variable is easier, I would recommend using the API method. Not all OSs use the variable.

 

[bryanbayfield]

ThisWorkbook.WriteReservedBy

will return the name of teh person who has current write access to the file.

Might fall over if that's no-one, so check that

ThisWorkbook.WriteReserved = True

first.