Folder Details

[tsheffer]

Using Power Query From folder to return the folder details. The folder I am working with also shows the "owner". That information doesn't automatically pull into Power Query. Is there a way to add a column for that? Ultimately, we want to be able to filter by modified date and owner.

Thanks for any suggestions!

 

[ranman256]

you can get Owner using this function:

Sub Macro1()
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim vFolderName As String, sLongFilename As String
Dim xFile

vFolderName = "\\server\folder\Lockout\"

Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(vFolderName)
For Each xFile In xFolder.Files
    MsgBox GetFileOwner(vFolderName, xFile.Name), , xFile.Name
Next
End Sub

Function GetFileOwner(fileDir As String, fileName As String) As String
    Dim securityUtility As Object
    Dim securityDescriptor As Object
    
    Set securityUtility = CreateObject("ADsSecurityUtility")
    Set securityDescriptor = securityUtility.GetSecurityDescriptor(fileDir & fileName, 1, 1)
    GetFileOwner = securityDescriptor.Owner
End Function