You could use a User Defined Function (UDF) to give you the information:

Function Author()

Author = ThisWorkbook.BuiltinDocumentProperties("Author")

End Function

Then in a worksheet cell, enter:

=Author()

I've used Power Query to get a long list of Excel files and I wanted to add a custom column populating the related author for each row. Function Author() can get the author information from a single open book, right?

Hi Derek ,

I got the following VBA script the other day, but still had no way to retrieve the file author with the line I highlighted in bold font type. Could you please check it out and make necessary modification for me. I would appreciate it.

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Owner
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

You also opened a new topic with the same text, please don't open multiple threads with the same subject. One is enough, don't worry, we'll help you 🙂

Here is the link to your other post, where the answer is: vba-macros/get-metadata-of-files-in-folders

Sorry for that, I thought it should be under the topic of VAB & Macros. Besides, I tried your answered script but still failed to get the solution. Let's come back to the VBA & Macros forum for futher discussion. Sorry again for violating the rules..... 

No worries, let's continue on the other topic 🙂