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 http://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 http://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:

0 Name 100 Cell phone 200 Bcc addresses
1 Size 101 Nickname 201 Bcc
2 Item type 102 Office location 202 Cc addresses
3 Date modified 103 Other address 203 Cc
4 Date created 104 Other city 204 Conversation ID
5 Date accessed 105 Other country/region 205 Date received
6 Attributes 106 Other P.O. box 206 Date sent
7 Offline status 107 Other postal code 207 From addresses
8 Offline availability 108 Other state or province 208 From
9 Perceived type 109 Other street 209 Has attachments
10 Owner 110 Pager 210 Sender address
11 Kind 111 Personal title 211 Sender name
12 Date taken 112 City 212 Store
13 Contributing artists 113 Country/region 213 To addresses
14 Album 114 P.O. box 214 To do title
15 Year 115 Postal code 215 To
16 Genre 116 State or province 216 Mileage
17 Conductors 117 Street 217 Album artist
18 Tags 118 Primary e-mail 218 Album ID
19 Rating 119 Primary phone 219 Beats-per-minute
20 Authors 120 Profession 220 Composers
21 Title 121 Spouse/Partner 221 Initial key
22 Subject 122 Suffix 222 Part of a compilation
23 Categories 123 TTY/TTD phone 223 Mood
24 Comments 124 Telex 224 Part of set
25 Copyright 125 Webpage 225 Period
26 # 126 Content status 226 Color
27 Length 127 Content type 227 Parental rating
28 Bit rate 128 Date acquired 228 Parental rating reason
29 Protected 129 Date archived 229 Space used
30 Camera model 130 Date completed 230 EXIF version
31 Dimensions 131 Device category 231 Event
32 Camera maker 132 Connected 232 Exposure bias
33 Company 133 Discovery method 233 Exposure program
34 File description 134 Friendly name 234 Exposure time
35 Program name 135 Local computer 235 F-stop
36 Duration 136 Manufacturer 236 Flash mode
37 Is online 137 Model 237 Focal length
38 Is recurring 138 Paired 238 35mm focal length
39 Location 139 Classification 239 ISO speed
40 Optional attendee addresses 140 Status 240 Lens maker
41 Optional attendees 141 Client ID 241 Lens model
42 Organizer address 142 Contributors 242 Light source
43 Organizer name 143 Content created 243 Max aperture
44 Reminder time 144 Last printed 244 Metering mode
45 Required attendee addresses 145 Date last saved 245 Orientation
46 Required attendees 146 Division 246 People
47 Resources 147 Document ID 247 Program mode
48 Meeting status 148 Pages 248 Saturation
49 Free/busy status 149 Slides 249 Subject distance
50 Total size 150 Total editing time 250 White balance
51 Account name 151 Word count 251 Priority
52 Task status 152 Due date 252 Project
53 Computer 153 End date 253 Channel number
54 Anniversary 154 File count 254 Episode name
55 Assistant’s name 155 Filename 255 Closed captioning
56 Assistant’s phone 156 File version 256 Rerun
57 Birthday 157 Flag color 257 SAP
58 Business address 158 Flag status 258 Broadcast date
59 Business city 159 Space free 259 Program description
60 Business country/region 160 Bit depth 260 Recording time
61 Business P.O. box 161 Horizontal resolution 261 Station call sign
62 Business postal code 162 Width 262 Station name
63 Business state or province 163 Vertical resolution 263 Summary
64 Business street 164 Height 264 Snippets
65 Business fax 165 Importance 265 Auto summary
66 Business home page 166 Is attachment 266 Search ranking
67 Business phone 167 Is deleted 267 Sensitivity
68 Callback number 168 Encryption status 268 Shared with
69 Car phone 169 Has flag 269 Sharing status
70 Children 170 Is completed 270 Product name
71 Company main phone 171 Incomplete 271 Product version
72 Department 172 Read status 272 Support link
73 E-mail address 173 Shared 273 Source
74 E-mail2 174 Creators 274 Start date
75 E-mail3 175 Date 275 Billing information
76 E-mail list 176 Folder name 276 Complete
77 E-mail display name 177 Folder path 277 Task owner
78 File as 178 Folder 278 Total file size
79 First name 179 Participants 279 Legal trademarks
80 Full name 180 Path 280 Video compression
81 Gender 181 By location 281 Directors
82 Given name 182 Type 282 Data rate
83 Hobbies 183 Contact names 283 Frame height
84 Home address 184 Entry type 284 Frame rate
85 Home city 185 Language 285 Frame width
86 Home country/region 186 Date visited 286 Total bitrate
87 Home P.O. box 187 Description 291 Masters Keywords (debug)
88 Home postal code 188 Link status 292 Masters Keywords (debug)
89 Home state or province 189 Link target 293 Primary disk
90 Home street 190 URL 294 Memory
91 Home fax 191 Media created 295 Machine status
92 Home phone 192 Date released 296 Configuration file
93 IM addresses 193 Encoded by 297 Audio tracks
94 Initials 194 Producers 298 Bit depth
95 Job title 195 Publisher 299 Contains chapters
96 Label 196 Subtitle 300 Content compression
97 Last name 197 User web URL 301 Subtitles
98 Mailing address 198 Writers 302 Subtitle tracks
99 Middle name 199 Attachments 303 Video tracks

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.

Leave a Reply