none
VBA to Get Author of Directory File RRS feed

  • Question

  • Hello:

    I found some boiler plate VBA to generate a list of .xls files in a specified file directory. The boiler plate VBA works fine to generate a list containing:

    • File Name
    • File Path
    • Last Modified Date for each file.

    But, I can't figure out a way to extract the author of each file.

    The line used to extract date last modified follows:

    LastMod = file.DateLastModified

    However there is no file."author" option.

    Does anyone know how to extract the name of a file's author when looping through a directory?

    Thanks in Advance ...

    Monday, November 16, 2015 2:25 AM

Answers

  • Hello:

    I found some boiler plate VBA to generate a list of .xls files in a specified file directory. The boiler plate VBA works fine to generate a list containing:

    • File Name
    • File Path
    • Last Modified Date for each file.

    But, I can't figure out a way to extract the author of each file.

    The line used to extract date last modified follows:

    LastMod = file.DateLastModified

    However there is no file."author" option.

    Does anyone know how to extract the name of a file's author when looping through a directory?

    I didn't know, but a little web searching led me to this post: http://p2p.wrox.com/excel-vba/35766-file-information.html#post154696 ,  from which I adapted this code to be placed in a standard module:

    Option Compare Database
    Option Explicit
    
    Type FileAttributes
        Name As String
        Size As String
        FileType As String
        DateModified As Date
        DateCreated As Date
        DateAccessed As Date
        Attributes As String
        Status As String
        Owner As String
        Author As String
        Title As String
        Subject As String
        Category As String
    End Type
    
    Public Function GetFileAttributes(strFilePath As String) As FileAttributes
    
    ' Shell32 objects
    Dim objShell As Shell32.Shell
    Dim objFolder As Shell32.Folder
    Dim objFolderItem As Shell32.FolderItem
    
    ' Other objects
    Dim strPath As String
    Dim strFileName As String
    Dim i As Integer
    
        ' If the file does not exist then quit out
        If Dir(strFilePath) = "" Then Exit Function
    
        ' Parse the file name out from the folder path
        i = InStrRev(strFilePath, "\")
        strFileName = Mid$(strFilePath, i + 1)
        strPath = Left$(strFilePath, i - 1)
        
        ' Set up the shell32 Shell object
        Set objShell = New Shell
    
        ' Set the shell32 folder object
        Set objFolder = objShell.Namespace(strPath)
    
        ' If we can find the folder then ...
        If (Not objFolder Is Nothing) Then
    
            ' Set the shell32 file object
            Set objFolderItem = objFolder.ParseName(strFileName)
    
            ' If we can find the file then get the file attributes
            If (Not objFolderItem Is Nothing) Then
    
                GetFileAttributes.Name = objFolder.GetDetailsOf(objFolderItem, 0)
                GetFileAttributes.Size = objFolder.GetDetailsOf(objFolderItem, 1)
                GetFileAttributes.FileType = objFolder.GetDetailsOf(objFolderItem, 2)
                GetFileAttributes.DateModified = CDate(objFolder.GetDetailsOf(objFolderItem, 3))
                GetFileAttributes.DateCreated = CDate(objFolder.GetDetailsOf(objFolderItem, 4))
                GetFileAttributes.DateAccessed = CDate(objFolder.GetDetailsOf(objFolderItem, 5))
                GetFileAttributes.Attributes = objFolder.GetDetailsOf(objFolderItem, 6)
                GetFileAttributes.Status = objFolder.GetDetailsOf(objFolderItem, 7)
                GetFileAttributes.Owner = objFolder.GetDetailsOf(objFolderItem, 10)
                GetFileAttributes.Author = objFolder.GetDetailsOf(objFolderItem, 20)
                GetFileAttributes.Title = objFolder.GetDetailsOf(objFolderItem, 21)
                GetFileAttributes.Subject = objFolder.GetDetailsOf(objFolderItem, 22)
                GetFileAttributes.Category = objFolder.GetDetailsOf(objFolderItem, 23)
    
            End If
    
            Set objFolderItem = Nothing
    
        End If
    
        Set objFolder = Nothing
        Set objShell = Nothing
    
    End Function
    
    Function GetFileAuthor(strFilePath As String) As String
    
    Dim fa As FileAttributes
    
        fa = GetFileAttributes(strFilePath)
        
        GetFileAuthor = fa.Author
    
    End Function
    

    You'll need to set a reference to shell32.dll, known in the References dialog as "Microsoft Shell Controls And Automation".

    With the function in a standard module, you can call the function GetFileAuthor with the full path and filename of a file, and it ought to return the Author attribute, if it is available.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by David_JunFeng Tuesday, November 17, 2015 3:03 PM
    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:00 PM
    Tuesday, November 17, 2015 6:21 AM