Return the name of the user that currently has workbook open

Thanks for pointing this out to me +1

I have tidied the code (what is it with coders not declaring variables?) and made a few minor tweaks. But there are some unexpected issues. If anyone is interested in solving, please see code below...

Option Explicit

Private Const strLockedWB_DriveLetter               As String = "Z:"
Private Const strLockedWB_SharePath                 As String = "\\sharednetworkpath"

Private Const strLockedWB_FolderPath_NoDrive        As String = "\shared_folder\"
Private Const strLockedWB_FileNameOnly              As String = "my_test.xlsx"

' this code will not run on Mac due to use of WScript

Public Sub testifworkbookopen()
' should return if workbook is open by another user and the name of the user
'
' currently not working - issues with setting the drive name (for network drives)
' the constants in this module are for testing purposes only - replace with your own versions
' (in the finished product - I would probably have a file or folder picker and assign them to variable strings)

    Dim objFSO                      As Object
    Dim objNetwork                  As Object
    Dim WScript                     As Object
    Dim strLockedWBFullFilePath     As String

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set WScript = CreateObject("WScript.Shell")
    Set objNetwork = CreateObject("WScript.Network")

'   I get an error on the next line. I have checked the drive letter & shared path name. Both are correct...
    objNetwork.MapNetworkDrive "strLockedWB_DriveLetter", "strLockedWB_SharePath"

'   ideally the code should auto detect if the file is on a shared network drive and pick up the drive letter/shared path name by itself
'   I don't know much about WScript.Network but a quick browse of the locals of this leads me to believe that this would be possible.

    strLockedWBFullFilePath = strLockedWB_SharePath & strLockedWB_FolderPath_NoDrive & "~$" & strLockedWB_FileNameOnly

    If objFSO.FileExists(strLockedWBFullFilePath) Then
        WScript.Echo "The file is locked by " & GetFileOwner(strLockedWBFullFilePath)
    Else
        WScript.Echo "The file is available"
    End If
End Sub

Private Function GetFileOwner(ByRef strFileName As String)
'http://www.vbsedit.com/scripts/security/ownership/scr_1386.asp
    Dim objWMIService           As Object
    Dim objFileSecuritySettings As Object
    Dim objSD                   As Object
    Dim intRetVal               As Integer

    Set objWMIService = GetObject("winmgmts:")
    Set objFileSecuritySettings = _
    objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")
    intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)

    If intRetVal = 0 Then
       GetFileOwner = objSD.Owner.Name
    Else
       GetFileOwner = "Unknown"
    End If
End Function