We help IT Professionals succeed at work.

How can I get the File Owner information in this VBA Script?

I have the following VBA code which I am running in Excel to find all files with the specified extension and return the filename, file location, date modified and size details.

The script works great but I would like it to also return the File Owner information in the results. My own attempts so far have failed :-(

The code I am using is below and any amendments to return the File Owner information also will be greatly appreciate! :-)

Option Explicit
Sub SrchForFiles()
     ' Searches the selected folders and sub folders for files with the specified
     'extension.  .xls, .doc, .ppt, etc.
     'A new worksheet is produced called "File Search Results".  You can click on the link and go directly
     'to the file you need.
    Dim i As Long, z As Long, Rw As Long
    Dim ws As Worksheet
    Dim y As Variant
    Dim fLdr As String, Fil As String, FPath As String
     
    y = Application.InputBox("Please Enter File Extension", "Info Request")
    If y = False And Not TypeName(y) = "String" Then Exit Sub
    Application.ScreenUpdating = False
     '**********************************************************************
     'fLdr = BrowseForFolderShell
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        fLdr = .SelectedItems(1)
    End With
     '**********************************************************************
    With Application.FileSearch
        .NewSearch
        .LookIn = fLdr
        .SearchSubFolders = True
        .Filename = y
        Set ws = ThisWorkbook.Worksheets.Add(Sheets(1))
        On Error GoTo 1
2:              ws.Name = "FileSearch Results"
        On Error GoTo 0
        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                Fil = .FoundFiles(i)
                 'Get file path from file name
                FPath = Left(Fil, Len(Fil) - Len(Split(Fil, "\")(UBound(Split(Fil, "\")))) - 1)
                If Left$(Fil, 1) = Left$(fLdr, 1) Then
                    If CBool(Len(Dir(Fil))) Then
                        z = z + 1
                        ws.Cells(z + 1, 1).Resize(, 4) = _
                        Array(Dir(Fil), _
                        FileLen(Fil) / 1000, _
                        FileDateTime(Fil), _
                        FPath)
                        ws.Hyperlinks.Add Anchor:=ws.Cells(z + 1, 1), _
                        Address:=.FoundFiles(i)
                    End If
                End If
            Next i
        End If
    End With
     
    ActiveWindow.DisplayHeadings = False
     
    With ws
        Rw = .Cells.Rows.Count
        With .[A1:D1]
            .Value = [{"Full Name","Kilobytes","Last Modified", "Path"}]
            .Font.Underline = xlUnderlineStyleSingle
            .EntireColumn.AutoFit
            .HorizontalAlignment = xlCenter
        End With
        .[E1:IV1 ].EntireColumn.Hidden = True
        On Error Resume Next
        Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden = True
        Range(.[A2 ], Cells(Rw, "C")).Sort [A2 ], xlAscending, Header:=xlNo
    End With
     
    Application.ScreenUpdating = True
    Exit Sub
1:      Application.DisplayAlerts = False
    Worksheets("FileSearch Results").Delete
    Application.DisplayAlerts = True
    GoTo 2
End Sub
Comment
Watch Question

Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:

Author

Commented:
Thank you. That information helped me work out how to get the owner information in my Report.

Author

Commented:
Links were provided to example code which was helpful but I had to work out on my own how to use the code in the script I originally posted.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Fair enough on grade, glad those were of some help to you, was a bit pressed for time this morning so didn't try to sew something into your code.

~bp

Author

Commented:
Thanks once again for you help it's very much appreciated