User name with open Excel file - VBA

Question

Objectively:
How is it possible to know, with VBA, which name of the user has an excel file opened, that file is somewhere on a server and not on the local disk?

Context
I have searched for a function, in VBA, which let me know if an excel file is opened by a user and, if so, which is the name of the user.

I was able to find a routine that does this, but only if the open file is on the local disk, which is not my case, follows the function cited:

Function Excel_File_in_use_by(FilePath As String) As String
    Dim strTempFile As String
    Dim iPos As Integer, iRetVal As Integer
    Dim objFSO As Object, objWMIService As Object, objFileSecuritySettings As Object, objSD As Object
    iPos = InStrRev(FilePath, "\")
    strTempFile = left(FilePath, iPos - 1) & "\~$" & Mid(FilePath, iPos + 1)
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If objFSO.FileExists(strTempFile) Then
        Set objWMIService = GetObject("winmgmts:")
        Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strTempFile & "'")
        iRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)
        If iRetVal = 0 Then
            Excel_File_in_use_by = objSD.Owner.Name
        Else
            Excel_File_in_use_by = "unknown"
        End If
        Set objWMIService = Nothing
        Set objFileSecuritySettings = Nothing
        Set objSD = Nothing
    Else
        Excel_File_in_use_by = vbNullString
    End If
    Set objFSO = Nothing
    End Function

I needed a function to check if a specific file, in this case an excel365 file, placed on a specific server, is open by a user and, if so, what is the user’s name.

It is possible to do this in VBA?

How to implement this?

Answer 1

The Windows computer username can be viewed as follows:

Dim usuarioPC As String, usuario As String

usuario = Environ("username")
usuarioPC = Application.UserName

MsgBox "O usuário deste PC é o " & usuarioPC
MsgBox "O usuário é o " & usuario

In which the Application Username. or the Environ

Or use the following function with the reference wScript to obtain the name of the computer on which Excel was opened:

Public Function ComputerName() As String
'' Returns the host name

'' Uses late-binding: bad for performance and stability, useful for
'' code portability. The correct declaration is:
'' https://stackoverflow.com/a/10108951/7690982

'   Dim objNetwork  As IWshRuntimeLibrary.WshNetwork
'   Set objNetwork = New IWshRuntimeLibrary.WshNetwork

    Dim objNetwork As Object
    Set objNetwork = CreateObject("WScript.Network")

    ComputerName = objNetwork.ComputerName

    Set objNetwork = Nothing

End Function

For a test view function output in a Msgbox:

MsgBox ComputerName()

Or another function to get the Computer user:

Public Function UserName(Optional WithDomain As Boolean = False) As String
'' Returns the user's network name
'' https://stackoverflow.com/a/10108951/7690982

'' Uses late-binding: bad for performance and stability, useful for
'' code portability. The correct declaration is:

'   Dim objNetwork  As IWshRuntimeLibrary.WshNetwork
'   Set objNetwork = New IWshRuntimeLibrary.WshNetwork


    Dim objNetwork As Object
    Set objNetwork = CreateObject("WScript.Network")

    If WithDomain Then
        UserName = objNetwork.UserDomain & "\" & objNetwork.UserName
    Else
        UserName = objNetwork.UserName
    End If

    Set objNetwork = Nothing

End Function

Or the function Getusername from the "advapi32.dll library":

' 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 retrieve user name. 
Function GetLogonName() As String 

 ' Dimension variables 
 Dim lpBuff As String * 255 
 Dim ret As Long 

 ' Get the user name minus any trailing spaces found in the name. 
 ret = GetUserName(lpBuff, 255) 

 If ret > 0 Then 
 GetLogonName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) 
 Else 
 GetLogonName = vbNullString 
 End If 
End Function