Modify Excel VBA Function for File Properties

Question

How can I modify this code to give details of each file in the object folder? Currently when I run it I just get the details of the folder and not the files in the folder. The specific details I need are the owner, author, date modified, and name. I don't know if this can be done within the function, but I would like to hyperlink to the name to the actual file so I would also need the name's path.

    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 
    Comments As String 
    Keywords 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
    strFileName = strFilePath 
    i = 1 
    Do Until i = 0 
        i = InStr(1, strFileName, "\", vbBinaryCompare) 
        strFileName = Mid(strFileName, i + 1) 
    Loop 
    strPath = Left(strFilePath, Len(strFilePath) - Len(strFileName) - 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, 8) 
            GetFileAttributes.Author = objFolder.GetDetailsOf(objFolderItem, 9) 
            GetFileAttributes.Title = objFolder.GetDetailsOf(objFolderItem, 10) 
            GetFileAttributes.Subject = objFolder.GetDetailsOf(objFolderItem, 11) 
            GetFileAttributes.Category = objFolder.GetDetailsOf(objFolderItem, 12) 
            GetFileAttributes.Comments = objFolder.GetDetailsOf(objFolderItem, 14) 
            GetFileAttributes.Keywords = objFolder.GetDetailsOf(objFolderItem, 40) 

        End If 

        Set objFolderItem = Nothing 

    End If 

    Set objFolder = Nothing 
    Set objShell = Nothing 

End Function

Look into the Microsoft Scripting Runtime (File Scripting Object (FSO)). It really good for folder and file navigation and manipulation. — Gary Evans, May 18 '16 at 11:23
@GaryEvans I think you meant File SYSTEM object https://msdn.microsoft.com/en-us/library/aa242706%28v=vs.60%29.aspx — litelite, May 18 '16 at 12:27
Yes sorry, File System Object (has features like FolderExists and FileExists that can help here). Although looking at the question, I think you will need to open each file, the Author is stored within the file as a proprietary property. The fact that you see it in Explorer properties window is more down to good integration. — Gary Evans, May 18 '16 at 13:27
Try this: http://stackoverflow.com/questions/5651890/using-vba-to-get-extended-file-attributes — Tim, May 18 '16 at 13:35
@Tim I get an "object does not support this method" error using the syntax objFile.BuiltinDocumentProperties("Owner") — Adavid02, May 23 '16 at 17:46
Owner is a filesystem property. Author is a document property. To find out the Owner you can query the filesystem: `objFolder.GetDetailsOf(objFolderItem, xx)`. To get Author, you have to open/read it from the document, and that will vary depending on file type. If it is an Office file and you don't want to actually open it, use the [Microsoft Developer Support OLE File Property Reader](https://www.microsoft.com/en-us/download/details.aspx?id=8422) — Tim, May 23 '16 at 18:28
@Tim Thanks for the resource. I got the Microsoft Developer Support OLE File Property Reader, but Im having trouble figuring out how to use it to get the owner property. That's my lack of knowledge so I'd say your comment suffices as the answer. — Adavid02, May 25 '16 at 11:04
The Scripting Guys have an excellent tutorial on the use of [DSOFile.dll](https://technet.microsoft.com/en-us/library/ee692828.aspx) — Tim, May 25 '16 at 15:06

Tim · Answer 1 · 2016-05-25T15:17:00.017

In fact, The Scripting Guys have exactly the code you are looking for:

Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open("C:\Scripts\New_users.xls")
Debug.Print "Author: " & objFile.SummaryProperties.Author

Even though this does not require adding a reference to DSOFile.dll, it does require that it be installed so your workbook is still not very portable. You could add a function that looks for DSOFile.dll and directs the user to the download page if it is not found.

I would still recommend late binding like this because you shouldn't run into any version dependencies this way. If you specifically add a reference to DSOFile.dll and a new version comes out, it may not have exactly the same name and then your code breaks.

Of course, I would recommend initially adding a reference when first writing the code so you can take advantage of Intellisense, but make sure to change it to late binding once your code is written.

Early binding:

Dim objFile As New DSOFile.OleDocumentProperties
objFile.Open("C:\Scripts\New_users.xls")

Then change it to Late binding:

Dim objFile As Object 'New DSOFile.OleDocumentProperties
Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open("C:\Scripts\New_users.xls")

Modify Excel VBA Function for File Properties

1 Answers1

Linked