Excel VBA – Get Extended File Property – Author Modified Date Time

Get File Property Using Excel VBA

Try these code to get the property of a file in the computer folder. By file property, I mean the Username who created it, File size, Creation & Modified date, Computer name in which it was created etc.,

To get these file properties, decide which file your code is going to read. Is it a Excel workbook or a file of different format other than Excel. If it is Excel file, then there is easy method available with in VBA.

Both the methods are discussed in this & easy to use code is given.

Read Excel File Property using VBA code

If you have a Excel file in your system. You can read its content using the code given in this article. But, if you would like to read the file properties, then use this code.

'--------------------------------------------------------------------------------
'Code by author@officetricks.com (or) kumarapush777 (Fiverr)
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Public Sub Get_File_Property()
    Dim oSheet As Worksheet, iRow As Double, sFname As String
    Dim oWorkBook As Workbook, objDocProperty
    
    'Set File Name with Folder
    sFname = "D:\ExcelFileName.xlsx"
    Set oWorkBook = Workbooks.Open(sFname, ReadOnly:=True)
    
    'Get Perperty Value by its name
    MsgBox oWorkBook.BuiltinDocumentProperties("Title")
    
    'Get Each Property Name & Value
    On Error Resume Next
    iRow = 2
    For Each objDocProperty In oWorkBook.BuiltinDocumentProperties
        ActiveSheet.Range("A" & iRow) = objDocProperty.Name
        ActiveSheet.Range("B" & iRow) = objDocProperty.Value
        iRow = iRow + 1
    Next
    
    'Close File
    On Error GoTo 0
    oWorkBook.Close

    MsgBox "Process Completed"
End Sub

Change the input file path along with folder name, before executing this code. You can either get the file property by its name or loop through all the available properties.

If “On error Resume next” is not used, then this code will show error while retrieving few property values.

Get Extended File Property for any File

As you can see that there are only 34 file properties that can be read from above method. If you want to read all the extended properties of any file within your Windows computer folder, then use this code.

'--------------------------------------------------------------------------------
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Sub Get_Extended_File_Property()
    Dim sFile As Object, obja
    
    'Create Shell Object & NameSpace
    Set oShell = CreateObject("Shell.Application")
    Set oDir = oShell.Namespace("D:\F\Fiverr\tixops123 - Excel App")
    ActiveSheet.Cells.ClearContents
    
    'Loop thru each File/Folder inside Root Directory
    iRow = 1
    For Each sFile In oDir.Items
        iRow = iRow + 1
        
        'Loop thru Each Property
        For i = -1 To 350
            
            'Get File Property Name & Value
            obja = oDir.GetDetailsOf(sFile, i)
            If obja <> "" Then
                iRow = iRow + 1
                ActiveSheet.Range("A" & iRow) = i
                
                'Enter File Property to Sheet
                ActiveSheet.Range("B" & iRow) = oDir.GetDetailsOf(oDir, i)
                ActiveSheet.Range("C" & iRow) = obja
            End If
        Next
    Next
    
    MsgBox "Process Completed"
End Sub

Just remember to change the folder name before executing this code. There are about more than 300 extended file properties that can be read using the above code.

Here is the complete list of properties that can be retrieved:

This kind of code is much useful when we do a file management system kind of work with the Excel VBA in any of our projects.