VBA and WMI

[bfreescott]

Hi guys

So, like many, my goal is to try and determine the name of a network user who has an excel file on a mapped network drive currently open.

I've chased many rabbits on this and have come to what I hope is a solution using the WMI library, but I'm getting an invalid path error with my function.

Has anyone had success with WMI?

Function GetFileOwner(strFileName)
'
    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

 

[Jaafar Tribak]

Are you sure you are passing the correct full path and name of the file ?

 

[bfreescott]

yes, in my rush I forgot to mention I am passing the full path including mapped drive and file extension.

 

[bfreescott]

And let me add since I've seen it as an issue with vbs that my path does contain single quotes. Not sure if that is causing the error.

 

[bfreescott]

That is indeed what the issue was, so in case anyone else has the same issue with Excel and WMI while trying to ID the user, make sure you pass the full path and use the following if your path contain single quotes:

Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting=""" & Replace(strFileName, "\", "\\") & """")

 

[froggy73]

I tried your code but no luck. Were you able to get this done with unc path? I was successful in getting it done with map drives but was looking to use unc path.

I have tried both of these:

'objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")

Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting=""" & Replace(strFileName, "") & """")


I was able to get it to work with map drive but was looking to do it unc.

Dim mypath As String


'testWorkbookLockFile = "L:\~$filename.xlsx"
mypath = "\\1.2.3.4\SharedFolder\~$filename.xlsx"

 

[bfreescott]

I tried your code but no luck. Were you able to get this done with unc path? I was successful in getting it done with map drives but was looking to use unc path.

I have tried both of these:

'objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")

Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting=""" & Replace(strFileName, "") & """")


I was able to get it to work with map drive but was looking to do it unc.

Dim mypath As String


'testWorkbookLockFile = "L:\~$filename.xlsx"
mypath = "\\1.2.3.4\SharedFolder\~$filename.xlsx"

If you look at my function, you'll notice I had to use a Replace function to change any single forward slash in the path string to a double forward slash.

 

[froggy73]

hmmm not sure what I'm missing..I think I've tried it with your function as well and says it cant be found but when you mouse over strFileName in debug it appears its getting the whole path. here is the code I'm using maybe you can see it better than me lol. Thanks for your help!

Sub Test()


Dim mypath As String


'testWorkbookLockFile = "L:\~$FileName.xlsx"
mypath = "\\IP\FolderShare\~$FileName.xlsx"
Set objFSO = CreateObject("Scripting.FileSystemObject")


If objFSO.FileExists(mypath) Then


Set TxtRng = ActiveWorkbook.Sheets("Sheet1").Cells(1, 1)

TxtRng.Value = "The file is locked by " & GetFileOwner(mypath)
Else
Set TxtRng = ActiveWorkbook.Sheets("Sheet1").Cells(1, 1)
TxtRng.Value = "The file is available"
End If
End Sub


Function GetFileOwner(strFileName)

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

Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting=""" & Replace(strFileName, "", "") & """")
intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)


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




End Function

 

[froggy73]

the one back slash and double backslash didnt show up in code but they are in the code.