I’ve been working on a personal project for a little while now and needed to retrieve various file properties. Now I’m not talking about the straightforward properties you can easily retrieve using the File System Object (Size, Date Created, Type, …). If you only need such information, then look over my article:
VBA - List of Files within a Folder and Get their Properties
I was trying to help mezentia on UtterAccess.com with getting a list of files contained within a folder with their properties (size, date created, date modified, last accessed, …) and finally created a small demo database to illustrate how it could all be pieced together in a seamless manner. As I have seen a number…
No, I’m interested in:
Image & media files
- Dimensions
- Camera model
- ISO settings
- Frame rate
- Orientation
- Duration
- and so much more!
Generally
- Item type
- Type
- Kind
- Program Name
- Subject
- and so much more!
the things that are not commonly accessible.
I already demonstrated how this could be done via PowerShell in my article
Get Image Exif Properties using VBA and PowerShell
In this article I thought I’d look at how we could retrieve Image Exif property values using VBA and PowerShell. EXIF What is contained in the Exif properties? Well EXIF contains all sorts of ‘extra’ information pertaining to a file. What exactly depends on the original source of the file, and whether it has been…
but in this scenario I decided to fall back on some older pure VBA code I already had and simply tweaked.
Returning All The Properties
Below if the function in question
'---------------------------------------------------------------------------------------
' Procedure : GetFileProperties
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Generate a Scripting Dictionary object of all the file properties
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' Dependencies : SortDictionaryByKey()
' https://excelmacromastery.com/vba-dictionary/#Sorting_the_Dictionary
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename of the file to get the properties of
' sSortDir : The order of the properties to sort on -> Asc/Desc
'
' Usage:
' ~~~~~~
' See Test_GetFileProperties()
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2009-07-26 Initial Release
' 2 2021-12-14 Initial Public Release
' Increased iteration limit to 320
' Added Sorting via the use of a dictionary object
'---------------------------------------------------------------------------------------
Function GetFileProperties(ByVal sFile As String, _
Optional sSortDir As String = "Asc") As Object
On Error GoTo Error_Handler
Dim oDic As Object 'Scripting.Dictionary
Dim oShell As Object 'Shell
Dim oFolder As Object 'Folder
Dim oFolderItem As Object 'FolderItem
Dim sFilePath As String
Dim sFileName As String
Dim i As Long
Dim vPropValue As Variant
sFilePath = Left(sFile, InStrRev(sFile, "\") - 1)
sFileName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
Set oDic = CreateObject("Scripting.Dictionary")
Set oShell = CreateObject("Shell.Application")
Set oFolder = oShell.NameSpace(CStr(sFilePath))
If (Not oFolder Is Nothing) Then
Set oFolderItem = oFolder.ParseName(sFileName)
For i = 0 To 320 'This could be bumped up in case MS increase the number again
vPropValue = oFolder.GetDetailsOf(oFolderItem, i)
If Trim(vPropValue & vbNullString) <> "" Then
vPropValue = Replace(Replace(Replace(Replace(vPropValue, ChrW(8236), ""), ChrW(8234), ""), ChrW(8207), ""), ChrW(8206), "")
oDic.Add oFolder.GetDetailsOf(oFolder.Items, i), vPropValue
End If
Next
End If
'Sort the dictionary entries asc
If sSortDir = "Asc" Then
Set oDic = SortDictionaryByKey(oDic, "xlAscending")
Else
Set oDic = SortDictionaryByKey(oDic, "xlDescending")
End If
Set GetFileProperties = oDic
Error_Handler_Exit:
On Error Resume Next
If Not oFolderItem Is Nothing Then Set oFolderItem = Nothing
If Not oFolder Is Nothing Then Set oFolder = Nothing
If Not oShell Is Nothing Then Set oShell = Nothing
If Not oDic Is Nothing Then Set oDic = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetFileProperties" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
You may be asking what the following line is all about in my code
vPropValue = Replace(Replace(Replace(Replace(vPropValue, ChrW(8236), ""), ChrW(8234), ""), ChrW(8207), ""), ChrW(8206), "")
Well, certain properties such as Dimensions, Width, Height, … return unicode characters that get displayed as ‘?’. To eliminate these, I use the above line of code. So it is just to cleanse the returned values so we don’t have any gibberish values. The list 8236, 8234, 8207 & 8206 are based on my testing, but there may be others that I haven’t encountered yet so if you see any ‘?’ returned that you can address them with the Replace() function.
Also note that you can simply comments out the line(s) in question to see what I’m referring to.
How To Use It
Below is an example of how it can be used:
'Test_GetFileProperties("C:\Test\DSC_0578.JPG")
'Test_GetFileProperties("C:\Temp\2.pdf")
'Test_GetFileProperties("C:\Temp\MonthlyStats.xls")
'Test_GetFileProperties("C:\Temp\List.txt")
'Test_GetFileProperties("C:\Temp\Graduation.mp4")
'Test_GetFileProperties("C:\Temp\Db_bak_20211201.zip")
'Test_GetFileProperties("C:\Temp\Office2013x32.iso")
'Test_GetFileProperties("C:\Temp\CARDA_installer.exe")
Public Sub Test_GetFileProperties(ByVal sFile As String)
Dim oDic As Object
Dim k As Variant
Set oDic = GetFileProperties(sFile)
' Set oDic = GetFileProperties(sFile, "Desc") 'Sort it in Descending Order
For Each k In oDic.Keys
Debug.Print k, oDic(k) 'Print to the VBA immediate window
'Me.ListboxName.AddItem k & ";" & oDic(k) & ";" 'Add items to a listbox
Next
If Not oDic Is Nothing Then Set oDic = Nothing
End Sub
Retrieving a Specific Property
I also wanted to explore how we could retrieve a single property.
The issue here is the fact that GetDetailsOf expect the numeric index of the property your are seeking. So they may work if you know that to retrieve the property “ISO speed” you need to use index number 264, but let’s be honest for a second that’s not very likely to be a for a flexible working solution.
Now, to solve this conundrum there are a couple possible approaches:
- Build a function that can convert what we actually want over to the proper index number (do not use this approach!)
- Iterate through the index numbers until we find the one we are actually after
Let’s explore each for a moment and you can choose the one that you believe will best suit your needs.
Build a function that can convert what we actually want over to the proper index number
To be able to build such a function, the first thing we need to do is somehow build a list of all the possible index numbers. Luckily, it is actually pretty straighforward to do as shown in the code below:
Public Sub ListAvailableFileProperties()
On Error GoTo Error_Handler
Dim oShell As Object 'Shell
Dim oFolder As Object 'Folder
Dim sFile As String
Dim sFilePath As String
Dim i As Long
sFile = CurrentDb.Name 'Any file will do, it makes no difference
sFilePath = Left(sFile, InStrRev(sFile, "\") - 1)
Set oShell = CreateObject("Shell.Application")
Set oFolder = oShell.NameSpace(CStr(sFilePath))
If (Not oFolder Is Nothing) Then
For i = 0 To 320 'This could be bumped up in case MS increase the number again
Debug.Print i, oFolder.GetDetailsOf(oFolder.Items, i)
Next
End If
Error_Handler_Exit:
On Error Resume Next
If Not oFolder Is Nothing Then Set oFolder = Nothing
If Not oShell Is Nothing Then Set oShell = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ListAvailableFileProperties" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
and this will output something along the lines of (generated on my Windows 10 PC):
0 Name 1 Size 2 Item type 3 Date modified 4 Date created 5 Date accessed 6 Attributes 7 Offline status 8 Availability 9 Perceived type 10 Owner 11 Kind 12 Date taken 13 Contributing artists 14 Album 15 Year 16 Genre 17 Conductors 18 Tags 19 Rating 20 Authors 21 Title 22 Subject 23 Categories 24 Comments 25 Copyright 26 # 27 Length 28 Bit rate 29 Protected 30 Camera model 31 Dimensions 32 Camera maker 33 Company 34 File description 35 Masters keywords 36 Masters keywords 37 38 39 40 41 42 Program name 43 Duration 44 Is online 45 Is recurring 46 Location 47 Optional attendee addresses 48 Optional attendees 49 Organizer address 50 Organizer name 51 Reminder time 52 Required attendee addresses 53 Required attendees 54 Resources 55 Meeting status 56 Free/busy status 57 Total size 58 Account name 59 60 Task status 61 Computer 62 Anniversary 63 Assistant's name 64 Assistant's phone 65 Birthday 66 Business address 67 Business city 68 Business country/region 69 Business P.O. box 70 Business postal code 71 Business state or province 72 Business street 73 Business fax 74 Business home page 75 Business phone 76 Callback number 77 Car phone 78 Children 79 Company main phone 80 Department 81 E-mail address 82 E-mail2 83 E-mail3 84 E-mail list 85 E-mail display name 86 File as 87 First name 88 Full name 89 Gender 90 Given name 91 Hobbies 92 Home address 93 Home city 94 Home country/region 95 Home P.O. box 96 Home postal code 97 Home state or province 98 Home street 99 Home fax 100 Home phone 101 IM addresses 102 Initials 103 Job title 104 Label 105 Last name 106 Mailing address 107 Middle name 108 Cell phone 109 Nickname 110 Office location 111 Other address 112 Other city 113 Other country/region 114 Other P.O. box 115 Other postal code 116 Other state or province 117 Other street 118 Pager 119 Personal title 120 City 121 Country/region 122 P.O. box 123 Postal code 124 State or province 125 Street 126 Primary e-mail 127 Primary phone 128 Profession 129 Spouse/Partner 130 Suffix 131 TTY/TTD phone 132 Telex 133 Webpage 134 Content status 135 Content type 136 Date acquired 137 Date archived 138 Date completed 139 Device category 140 Connected 141 Discovery method 142 Friendly name 143 Local computer 144 Manufacturer 145 Model 146 Paired 147 Classification 148 Status 149 Status 150 Client ID 151 Contributors 152 Content created 153 Last printed 154 Date last saved 155 Division 156 Document ID 157 Pages 158 Slides 159 Total editing time 160 Word count 161 Due date 162 End date 163 File count 164 File extension 165 Filename 166 File version 167 Flag color 168 Flag status 169 Space free 170 171 172 Group 173 Sharing type 174 Bit depth 175 Horizontal resolution 176 Width 177 Vertical resolution 178 Height 179 Importance 180 Is attachment 181 Is deleted 182 Encryption status 183 Has flag 184 Is completed 185 Incomplete 186 Read status 187 Shared 188 Creators 189 Date 190 Folder name 191 Folder path 192 Folder 193 Participants 194 Path 195 By location 196 Type 197 Contact names 198 Entry type 199 Language 200 Date visited 201 Description 202 Link status 203 Link target 204 URL 205 206 207 208 Media created 209 Date released 210 Encoded by 211 Episode number 212 Producers 213 Publisher 214 Season number 215 Subtitle 216 User web URL 217 Writers 218 219 Attachments 220 Bcc addresses 221 Bcc 222 Cc addresses 223 Cc 224 Conversation ID 225 Date received 226 Date sent 227 From addresses 228 From 229 Has attachments 230 Sender address 231 Sender name 232 Store 233 To addresses 234 To do title 235 To 236 Mileage 237 Album artist 238 Sort album artist 239 Album ID 240 Sort album 241 Sort contributing artists 242 Beats-per-minute 243 Composers 244 Sort composer 245 Disc 246 Initial key 247 Part of a compilation 248 Mood 249 Part of set 250 Period 251 Color 252 Parental rating 253 Parental rating reason 254 Space used 255 EXIF version 256 Event 257 Exposure bias 258 Exposure program 259 Exposure time 260 F-stop 261 Flash mode 262 Focal length 263 35mm focal length 264 ISO speed 265 Lens maker 266 Lens model 267 Light source 268 Max aperture 269 Metering mode 270 Orientation 271 People 272 Program mode 273 Saturation 274 Subject distance 275 White balance 276 Priority 277 Project 278 Channel number 279 Episode name 280 Closed captioning 281 Rerun 282 SAP 283 Broadcast date 284 Program description 285 Recording time 286 Station call sign 287 Station name 288 Summary 289 Snippets 290 Auto summary 291 Relevance 292 File ownership 293 Sensitivity 294 Shared with 295 Sharing status 296 297 Product name 298 Product version 299 Support link 300 Source 301 Start date 302 Sharing 303 Availability status 304 Status 305 Billing information 306 Complete 307 Task owner 308 Sort title 309 Total file size 310 Legal trademarks 311 Video compression 312 Directors 313 Data rate 314 Frame height 315 Frame rate 316 Frame width 317 Spherical 318 Stereo 319 Video orientation 320 Total bitrate
armed with this information, we are now ready to build our function, as shown below:
'---------------------------------------------------------------------------------------
' Procedure : GetFileProperty
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve a specific property for the specified file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename of the file to get the properties of
' sPropertyName : The name of the property you wish to retrieve for the file
'
' Usage:
' ~~~~~~
' GetFileProperty("C:\Temp\CropTest01.jpg", "Date Created")
' Returns -> 4/17/2020 7:55 PM
' GetFileProperty("C:\Temp\CropTest01.jpg", "Perceived type")
' Returns -> Image
' GetFileProperty("C:\Temp\CropTest01.jpg", "type")
' Returns -> JPG File
' GetFileProperty("C:\Temp\CropTest01.jpg", "dimensions")
' Returns -> 80 x 475
' GetFileProperty("C:\Temp\CropTest01.jpg", "kind")
' Returns -> Picture
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2021-12-14 Initial Public Release
'---------------------------------------------------------------------------------------
Function GetFileProperty(ByVal sFile As String, _
ByVal sPropertyName As String) As String
On Error GoTo Error_Handler
Dim oShell As Object 'Shell
Dim oFolder As Object 'Folder
Dim oFolderItem As Object 'FolderItem
Dim sFilePath As String
Dim sFileName As String
Dim vPropValue As Variant
Dim lPropertyNumber As Long
'The following const are based on Windows 10 testing
Select Case sPropertyName
Case "Name"
lPropertyNumber = 0
Case "Size"
lPropertyNumber = 1
Case "Item type"
lPropertyNumber = 2
Case "Date modified"
lPropertyNumber = 3
Case "Date created"
lPropertyNumber = 4
Case "Date accessed"
lPropertyNumber = 5
Case "Attributes"
lPropertyNumber = 6
Case "Offline status"
lPropertyNumber = 7
Case "Availability"
lPropertyNumber = 8
Case "Perceived type"
lPropertyNumber = 9
Case "Owner"
lPropertyNumber = 10
Case "Kind"
lPropertyNumber = 11
Case "Date taken"
lPropertyNumber = 12
Case "Contributing artists"
lPropertyNumber = 13
Case "Album"
lPropertyNumber = 14
Case "Year"
lPropertyNumber = 15
Case "Genre"
lPropertyNumber = 16
Case "Conductors"
lPropertyNumber = 17
Case "Tags"
lPropertyNumber = 18
Case "Rating"
lPropertyNumber = 19
Case "Authors"
lPropertyNumber = 20
Case "Title"
lPropertyNumber = 21
Case "Subject"
lPropertyNumber = 22
Case "Categories"
lPropertyNumber = 23
Case "Comments"
lPropertyNumber = 24
Case "Copyright"
lPropertyNumber = 25
Case "#"
lPropertyNumber = 26
Case "Length"
lPropertyNumber = 27
Case "Bit rate"
lPropertyNumber = 28
Case "Protected"
lPropertyNumber = 29
Case "Camera model"
lPropertyNumber = 30
Case "Dimensions"
lPropertyNumber = 31
Case "Camera maker"
lPropertyNumber = 32
Case "Company"
lPropertyNumber = 33
Case "File description"
lPropertyNumber = 34
Case "Masters keywords"
lPropertyNumber = 35
Case "Masters keywords"
lPropertyNumber = 36
Case ""
lPropertyNumber = 37
Case ""
lPropertyNumber = 38
Case ""
lPropertyNumber = 39
Case ""
lPropertyNumber = 40
Case ""
lPropertyNumber = 41
Case "Program name"
lPropertyNumber = 42
Case "Duration"
lPropertyNumber = 43
Case "Is online"
lPropertyNumber = 44
Case "Is recurring"
lPropertyNumber = 45
Case "Location"
lPropertyNumber = 46
Case "Optional attendee addresses"
lPropertyNumber = 47
Case "Optional attendees"
lPropertyNumber = 48
Case "Organizer address"
lPropertyNumber = 49
Case "Organizer name"
lPropertyNumber = 50
Case "Reminder time"
lPropertyNumber = 51
Case "Required attendee addresses"
lPropertyNumber = 52
Case "Required attendees"
lPropertyNumber = 53
Case "Resources"
lPropertyNumber = 54
Case "Meeting status"
lPropertyNumber = 55
Case "Free/busy status"
lPropertyNumber = 56
Case "Total size"
lPropertyNumber = 57
Case "Account name"
lPropertyNumber = 58
Case ""
lPropertyNumber = 59
Case "Task status"
lPropertyNumber = 60
Case "Computer"
lPropertyNumber = 61
Case "Anniversary"
lPropertyNumber = 62
Case "Assistant's name"
lPropertyNumber = 63
Case "Assistant's phone"
lPropertyNumber = 64
Case "Birthday"
lPropertyNumber = 65
Case "Business address"
lPropertyNumber = 66
Case "Business city"
lPropertyNumber = 67
Case "Business country/region"
lPropertyNumber = 68
Case "Business P.O. box"
lPropertyNumber = 69
Case "Business postal code"
lPropertyNumber = 70
Case "Business state or province"
lPropertyNumber = 71
Case "Business street"
lPropertyNumber = 72
Case "Business fax"
lPropertyNumber = 73
Case "Business home page"
lPropertyNumber = 74
Case "Business phone"
lPropertyNumber = 75
Case "Callback number"
lPropertyNumber = 76
Case "Car phone"
lPropertyNumber = 77
Case "Children"
lPropertyNumber = 78
Case "Company main phone"
lPropertyNumber = 79
Case "Department"
lPropertyNumber = 80
Case "E-mail address"
lPropertyNumber = 81
Case "E-mail2"
lPropertyNumber = 82
Case "E-mail3"
lPropertyNumber = 83
Case "E-mail list"
lPropertyNumber = 84
Case "E-mail display name"
lPropertyNumber = 85
Case "File as"
lPropertyNumber = 86
Case "First name"
lPropertyNumber = 87
Case "Full name"
lPropertyNumber = 88
Case "Gender"
lPropertyNumber = 89
Case "Given name"
lPropertyNumber = 90
Case "Hobbies"
lPropertyNumber = 91
Case "Home address"
lPropertyNumber = 92
Case "Home city"
lPropertyNumber = 93
Case "Home country/region"
lPropertyNumber = 94
Case "Home P.O. box"
lPropertyNumber = 95
Case "Home postal code"
lPropertyNumber = 96
Case "Home state or province"
lPropertyNumber = 97
Case "Home street"
lPropertyNumber = 98
Case "Home fax"
lPropertyNumber = 99
Case "Home phone"
lPropertyNumber = 100
Case "IM addresses"
lPropertyNumber = 101
Case "Initials"
lPropertyNumber = 102
Case "Job title"
lPropertyNumber = 103
Case "Label"
lPropertyNumber = 104
Case "Last name"
lPropertyNumber = 105
Case "Mailing address"
lPropertyNumber = 106
Case "Middle name"
lPropertyNumber = 107
Case "Cell phone"
lPropertyNumber = 108
Case "Nickname"
lPropertyNumber = 109
Case "Office location"
lPropertyNumber = 110
Case "Other address"
lPropertyNumber = 111
Case "Other city"
lPropertyNumber = 112
Case "Other country/region"
lPropertyNumber = 113
Case "Other P.O. box"
lPropertyNumber = 114
Case "Other postal code"
lPropertyNumber = 115
Case "Other state or province"
lPropertyNumber = 116
Case "Other street"
lPropertyNumber = 117
Case "Pager"
lPropertyNumber = 118
Case "Personal title"
lPropertyNumber = 119
Case "City"
lPropertyNumber = 120
Case "Country/region"
lPropertyNumber = 121
Case "P.O. box"
lPropertyNumber = 122
Case "Postal code"
lPropertyNumber = 123
Case "State or province"
lPropertyNumber = 124
Case "Street"
lPropertyNumber = 125
Case "Primary e-mail"
lPropertyNumber = 126
Case "Primary phone"
lPropertyNumber = 127
Case "Profession"
lPropertyNumber = 128
Case "Spouse/Partner"
lPropertyNumber = 129
Case "Suffix"
lPropertyNumber = 130
Case "TTY/TTD phone"
lPropertyNumber = 131
Case "Telex"
lPropertyNumber = 132
Case "Webpage"
lPropertyNumber = 133
Case "Content status"
lPropertyNumber = 134
Case "Content type"
lPropertyNumber = 135
Case "Date acquired"
lPropertyNumber = 136
Case "Date archived"
lPropertyNumber = 137
Case "Date completed"
lPropertyNumber = 138
Case "Device category"
lPropertyNumber = 139
Case "Connected"
lPropertyNumber = 140
Case "Discovery method"
lPropertyNumber = 141
Case "Friendly name"
lPropertyNumber = 142
Case "Local computer"
lPropertyNumber = 143
Case "Manufacturer"
lPropertyNumber = 144
Case "Model"
lPropertyNumber = 145
Case "Paired"
lPropertyNumber = 146
Case "Classification"
lPropertyNumber = 147
Case "Status"
lPropertyNumber = 148
Case "Status"
lPropertyNumber = 149
Case "Client ID"
lPropertyNumber = 150
Case "Contributors"
lPropertyNumber = 151
Case "Content created"
lPropertyNumber = 152
Case "Last printed"
lPropertyNumber = 153
Case "Date last saved"
lPropertyNumber = 154
Case "Division"
lPropertyNumber = 155
Case "Document ID"
lPropertyNumber = 156
Case "Pages"
lPropertyNumber = 157
Case "Slides"
lPropertyNumber = 158
Case "Total editing time"
lPropertyNumber = 159
Case "Word count"
lPropertyNumber = 160
Case "Due date"
lPropertyNumber = 161
Case "End date"
lPropertyNumber = 162
Case "File count"
lPropertyNumber = 163
Case "File extension"
lPropertyNumber = 164
Case "Filename"
lPropertyNumber = 165
Case "File version"
lPropertyNumber = 166
Case "Flag color"
lPropertyNumber = 167
Case "Flag status"
lPropertyNumber = 168
Case "Space free"
lPropertyNumber = 169
Case ""
lPropertyNumber = 170
Case ""
lPropertyNumber = 171
Case "Group"
lPropertyNumber = 172
Case "Sharing type"
lPropertyNumber = 173
Case "Bit depth"
lPropertyNumber = 174
Case "Horizontal resolution"
lPropertyNumber = 175
Case "Width"
lPropertyNumber = 176
Case "Vertical resolution"
lPropertyNumber = 177
Case "Height"
lPropertyNumber = 178
Case "Importance"
lPropertyNumber = 179
Case "Is attachment"
lPropertyNumber = 180
Case "Is deleted"
lPropertyNumber = 181
Case "Encryption status"
lPropertyNumber = 182
Case "Has flag"
lPropertyNumber = 183
Case "Is completed"
lPropertyNumber = 184
Case "Incomplete"
lPropertyNumber = 185
Case "Read status"
lPropertyNumber = 186
Case "Shared"
lPropertyNumber = 187
Case "Creators"
lPropertyNumber = 188
Case "Date"
lPropertyNumber = 189
Case "Folder name"
lPropertyNumber = 190
Case "Folder path"
lPropertyNumber = 191
Case "Folder"
lPropertyNumber = 192
Case "Participants"
lPropertyNumber = 193
Case "Path"
lPropertyNumber = 194
Case "By location"
lPropertyNumber = 195
Case "Type"
lPropertyNumber = 196
Case "Contact names"
lPropertyNumber = 197
Case "Entry type"
lPropertyNumber = 198
Case "Language"
lPropertyNumber = 199
Case "Date visited"
lPropertyNumber = 200
Case "Description"
lPropertyNumber = 201
Case "Link status"
lPropertyNumber = 202
Case "Link target"
lPropertyNumber = 203
Case "URL"
lPropertyNumber = 204
Case ""
lPropertyNumber = 205
Case ""
lPropertyNumber = 206
Case ""
lPropertyNumber = 207
Case "Media created"
lPropertyNumber = 208
Case "Date released"
lPropertyNumber = 209
Case "Encoded by"
lPropertyNumber = 210
Case "Episode number"
lPropertyNumber = 211
Case "Producers"
lPropertyNumber = 212
Case "Publisher"
lPropertyNumber = 213
Case "Season number"
lPropertyNumber = 214
Case "Subtitle"
lPropertyNumber = 215
Case "User web URL"
lPropertyNumber = 216
Case "Writers"
lPropertyNumber = 217
Case ""
lPropertyNumber = 218
Case "Attachments"
lPropertyNumber = 219
Case "Bcc addresses"
lPropertyNumber = 220
Case "Bcc"
lPropertyNumber = 221
Case "Cc addresses"
lPropertyNumber = 222
Case "Cc"
lPropertyNumber = 223
Case "Conversation ID"
lPropertyNumber = 224
Case "Date received"
lPropertyNumber = 225
Case "Date sent"
lPropertyNumber = 226
Case "From addresses"
lPropertyNumber = 227
Case "From"
lPropertyNumber = 228
Case "Has attachments"
lPropertyNumber = 229
Case "Sender address"
lPropertyNumber = 230
Case "Sender name"
lPropertyNumber = 231
Case "Store"
lPropertyNumber = 232
Case "To addresses"
lPropertyNumber = 233
Case "To do title"
lPropertyNumber = 234
Case "To"
lPropertyNumber = 235
Case "Mileage"
lPropertyNumber = 236
Case "Album artist"
lPropertyNumber = 237
Case "Sort album artist"
lPropertyNumber = 238
Case "Album ID"
lPropertyNumber = 239
Case "Sort album"
lPropertyNumber = 240
Case "Sort contributing artists"
lPropertyNumber = 241
Case "Beats-per-minute"
lPropertyNumber = 242
Case "Composers"
lPropertyNumber = 243
Case "Sort composer"
lPropertyNumber = 244
Case "Disc"
lPropertyNumber = 245
Case "Initial key"
lPropertyNumber = 246
Case "Part of a compilation"
lPropertyNumber = 247
Case "Mood"
lPropertyNumber = 248
Case "Part of set"
lPropertyNumber = 249
Case "Period"
lPropertyNumber = 250
Case "Color"
lPropertyNumber = 251
Case "Parental rating"
lPropertyNumber = 252
Case "Parental rating reason"
lPropertyNumber = 253
Case "Space used"
lPropertyNumber = 254
Case "EXIF version"
lPropertyNumber = 255
Case "Event"
lPropertyNumber = 256
Case "Exposure bias"
lPropertyNumber = 257
Case "Exposure program"
lPropertyNumber = 258
Case "Exposure time"
lPropertyNumber = 259
Case "F-stop"
lPropertyNumber = 260
Case "Flash mode"
lPropertyNumber = 261
Case "Focal length"
lPropertyNumber = 262
Case "35mm focal length"
lPropertyNumber = 263
Case "ISO speed"
lPropertyNumber = 264
Case "Lens maker"
lPropertyNumber = 265
Case "Lens model"
lPropertyNumber = 266
Case "Light source"
lPropertyNumber = 267
Case "Max aperture"
lPropertyNumber = 268
Case "Metering mode"
lPropertyNumber = 269
Case "Orientation"
lPropertyNumber = 270
Case "People"
lPropertyNumber = 271
Case "Program mode"
lPropertyNumber = 272
Case "Saturation"
lPropertyNumber = 273
Case "Subject distance"
lPropertyNumber = 274
Case "White balance"
lPropertyNumber = 275
Case "Priority"
lPropertyNumber = 276
Case "Project"
lPropertyNumber = 277
Case "Channel number"
lPropertyNumber = 278
Case "Episode name"
lPropertyNumber = 279
Case "Closed captioning"
lPropertyNumber = 280
Case "Rerun"
lPropertyNumber = 281
Case "SAP"
lPropertyNumber = 282
Case "Broadcast date"
lPropertyNumber = 283
Case "Program description"
lPropertyNumber = 284
Case "Recording time"
lPropertyNumber = 285
Case "Station call sign"
lPropertyNumber = 286
Case "Station name"
lPropertyNumber = 287
Case "Summary"
lPropertyNumber = 288
Case "Snippets"
lPropertyNumber = 289
Case "Auto summary"
lPropertyNumber = 290
Case "Relevance"
lPropertyNumber = 291
Case "File ownership"
lPropertyNumber = 292
Case "Sensitivity"
lPropertyNumber = 293
Case "Shared with"
lPropertyNumber = 294
Case "Sharing status"
lPropertyNumber = 295
Case ""
lPropertyNumber = 296
Case "Product name"
lPropertyNumber = 297
Case "Product version"
lPropertyNumber = 298
Case "Support link"
lPropertyNumber = 299
Case "Source"
lPropertyNumber = 300
Case "Start date"
lPropertyNumber = 301
Case "Sharing"
lPropertyNumber = 302
Case "Availability status"
lPropertyNumber = 303
Case "Status"
lPropertyNumber = 304
Case "Billing information"
lPropertyNumber = 305
Case "Complete"
lPropertyNumber = 306
Case "Task owner"
lPropertyNumber = 307
Case "Sort title"
lPropertyNumber = 308
Case "Total file size"
lPropertyNumber = 309
Case "Legal trademarks"
lPropertyNumber = 310
Case "Video compression"
lPropertyNumber = 311
Case "Directors"
lPropertyNumber = 312
Case "Data rate"
lPropertyNumber = 313
Case "Frame height"
lPropertyNumber = 314
Case "Frame rate"
lPropertyNumber = 315
Case "Frame width"
lPropertyNumber = 316
Case "Spherical"
lPropertyNumber = 317
Case "Stereo"
lPropertyNumber = 318
Case "Video orientation"
lPropertyNumber = 319
Case "Total bitrate"
lPropertyNumber = 320
Case Else
GoTo Error_Handler_Exit
End Select
sFilePath = Left(sFile, InStrRev(sFile, "\") - 1)
sFileName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
Set oShell = CreateObject("Shell.Application")
Set oFolder = oShell.NameSpace(CStr(sFilePath))
If (Not oFolder Is Nothing) Then
Set oFolderItem = oFolder.ParseName(sFileName)
vPropValue = oFolder.GetDetailsOf(oFolderItem, lPropertyNumber)
If vPropValue = "" Then GoTo Error_Handler_Exit
If Trim(vPropValue & vbNullString) <> "" Then
vPropValue = Replace(Replace(Replace(Replace(vPropValue, ChrW(8236), ""), ChrW(8234), ""), ChrW(8207), ""), ChrW(8206), "")
End If
GetFileProperty = vPropValue
End If
Error_Handler_Exit:
On Error Resume Next
If Not oFolderItem Is Nothing Then Set oFolderItem = Nothing
If Not oFolder Is Nothing Then Set oFolder = Nothing
If Not oShell Is Nothing Then Set oShell = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetFileProperty" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
BUT WAIT!
Something was bothering me with the above, and so I did some testing in Windows 7. Then in Window XP. I found out that Microsoft has not only expanded the available Properties, which in itself is great, but they didn’t always preserve what a given property number represented! (No Words!)
That’s right, if we take property number 11, in Windows XP it represented the ‘Subject’ property, but in Windows 7 & 10 it represents the ‘Kind’ property.
Another example of this would be the ‘Duration’ property. In Windows XP it was property number 21, then in Windows 7 it was changed to number 36 and now in Windows 10 it is number 43.
Here’s a side by side comparison of File Properties by OS
| Property Number | Windows XP | Windows 7 | Windows10 |
|---|---|---|---|
| 0 | Name | Name | Name |
| 1 | Size | Size | Size |
| 2 | Type | Item type | Item type |
| 3 | Date Modified | Date modified | Date modified |
| 4 | Date Created | Date created | Date created |
| 5 | Date Accessed | Date accessed | Date accessed |
| 6 | Attributes | Attributes | Attributes |
| 7 | Status | Offline status | Offline status |
| 8 | Owner | Offline availability | Availability |
| 9 | Author | Perceived type | Perceived type |
| 10 | Title | Owner | Owner |
| 11 | Subject | Kind | Kind |
| 12 | Category | Date taken | Date taken |
| 13 | Pages | Contributing artists | Contributing artists |
| 14 | Comments | Album | Album |
| 15 | Copyright | Year | Year |
| 16 | Artist | Genre | Genre |
| 17 | Album Title | Conductors | Conductors |
| 18 | Year | Tags | Tags |
| 19 | Track Number | Rating | Rating |
| 20 | Genre | Authors | Authors |
| 21 | Duration | Title | Title |
| 22 | Bit Rate | Subject | Subject |
| 23 | Protected | Categories | Categories |
| 24 | Camera Model | Comments | Comments |
| 25 | Date Picture Taken | Copyright | Copyright |
| 26 | Dimensions | # | # |
| 27 | Length | Length | |
| 28 | Bit rate | Bit rate | |
| 29 | Episode Name | Protected | Protected |
| 30 | Program Description | Camera model | Camera model |
| 31 | Dimensions | Dimensions | |
| 32 | Audio sample size | Camera maker | Camera maker |
| 33 | Audio sample rate | Company | Company |
| 34 | Channels | File description | File description |
| 35 | Company | Program name | Masters keywords |
| 36 | Description | Duration | Masters keywords |
| 37 | File Version | Is online | |
| 38 | Product Name | Is recurring | |
| 39 | Product Version | Location | |
| 40 | Optional attendee addresses | ||
| 41 | Optional attendees | ||
| 42 | Organizer address | Program name | |
| 43 | Organizer name | Duration | |
| 44 | Reminder time | Is online | |
| 45 | Required attendee addresses | Is recurring | |
| 46 | Required attendees | Location | |
| 47 | Resources | Optional attendee addresses | |
| 48 | Meeting status | Optional attendees | |
| 49 | Free/busy status | Organizer address | |
| 50 | Total size | Organizer name | |
| 51 | Account name | Reminder time | |
| 52 | Task status | Required attendee addresses | |
| 53 | Computer | Required attendees | |
| 54 | Anniversary | Resources | |
| 55 | Assistant’s name | Meeting status | |
| 56 | Assistant’s phone | Free/busy status | |
| 57 | Birthday | Total size | |
| 58 | Business address | Account name | |
| 59 | Business city | ||
| 60 | Business country/region | Task status | |
| 61 | Business P.O. box | Computer | |
| 62 | Business postal code | Anniversary | |
| 63 | Business state or province | Assistant’s name | |
| 64 | Business street | Assistant’s phone | |
| 65 | Business fax | Birthday | |
| 66 | Business home page | Business address | |
| 67 | Business phone | Business city | |
| 68 | Callback number | Business country/region | |
| 69 | Car phone | Business P.O. box | |
| 70 | Children | Business postal code | |
| 71 | Company main phone | Business state or province | |
| 72 | Department | Business street | |
| 73 | E-mail address | Business fax | |
| 74 | E-mail2 | Business home page | |
| 75 | E-mail3 | Business phone | |
| 76 | E-mail list | Callback number | |
| 77 | E-mail display name | Car phone | |
| 78 | File as | Children | |
| 79 | First name | Company main phone | |
| 80 | Full name | Department | |
| 81 | Gender | E-mail address | |
| 82 | Given name | E-mail2 | |
| 83 | Hobbies | E-mail3 | |
| 84 | Home address | E-mail list | |
| 85 | Home city | E-mail display name | |
| 86 | Home country/region | File as | |
| 87 | Home P.O. box | First name | |
| 88 | Home postal code | Full name | |
| 89 | Home state or province | Gender | |
| 90 | Home street | Given name | |
| 91 | Home fax | Hobbies | |
| 92 | Home phone | Home address | |
| 93 | IM addresses | Home city | |
| 94 | Initials | Home country/region | |
| 95 | Job title | Home P.O. box | |
| 96 | Label | Home postal code | |
| 97 | Last name | Home state or province | |
| 98 | Mailing address | Home street | |
| 99 | Middle name | Home fax | |
| 100 | Cell phone | Home phone | |
| 101 | Nickname | IM addresses | |
| 102 | Office location | Initials | |
| 103 | Other address | Job title | |
| 104 | Other city | Label | |
| 105 | Other country/region | Last name | |
| 106 | Other P.O. box | Mailing address | |
| 107 | Other postal code | Middle name | |
| 108 | Other state or province | Cell phone | |
| 109 | Other street | Nickname | |
| 110 | Pager | Office location | |
| 111 | Personal title | Other address | |
| 112 | City | Other city | |
| 113 | Country/region | Other country/region | |
| 114 | P.O. box | Other P.O. box | |
| 115 | Postal code | Other postal code | |
| 116 | State or province | Other state or province | |
| 117 | Street | Other street | |
| 118 | Primary e-mail | Pager | |
| 119 | Primary phone | Personal title | |
| 120 | Profession | City | |
| 121 | Spouse/Partner | Country/region | |
| 122 | Suffix | P.O. box | |
| 123 | TTY/TTD phone | Postal code | |
| 124 | Telex | State or province | |
| 125 | Webpage | Street | |
| 126 | Content status | Primary e-mail | |
| 127 | Content type | Primary phone | |
| 128 | Date acquired | Profession | |
| 129 | Date archived | Spouse/Partner | |
| 130 | Date completed | Suffix | |
| 131 | Device category | TTY/TTD phone | |
| 132 | Connected | Telex | |
| 133 | Discovery method | Webpage | |
| 134 | Friendly name | Content status | |
| 135 | Local computer | Content type | |
| 136 | Manufacturer | Date acquired | |
| 137 | Model | Date archived | |
| 138 | Paired | Date completed | |
| 139 | Classification | Device category | |
| 140 | Status | Connected | |
| 141 | Client ID | Discovery method | |
| 142 | Contributors | Friendly name | |
| 143 | Content created | Local computer | |
| 144 | Last printed | Manufacturer | |
| 145 | Date last saved | Model | |
| 146 | Division | Paired | |
| 147 | Document ID | Classification | |
| 148 | Pages | Status | |
| 149 | Slides | Status | |
| 150 | Total editing time | Client ID | |
| 151 | Word count | Contributors | |
| 152 | Due date | Content created | |
| 153 | End date | Last printed | |
| 154 | File count | Date last saved | |
| 155 | Filename | Division | |
| 156 | File version | Document ID | |
| 157 | Flag color | Pages | |
| 158 | Flag status | Slides | |
| 159 | Space free | Total editing time | |
| 160 | Bit depth | Word count | |
| 161 | Horizontal resolution | Due date | |
| 162 | Width | End date | |
| 163 | Vertical resolution | File count | |
| 164 | Height | File extension | |
| 165 | Importance | Filename | |
| 166 | Is attachment | File version | |
| 167 | Is deleted | Flag color | |
| 168 | Encryption status | Flag status | |
| 169 | Has flag | Space free | |
| 170 | Is completed | ||
| 171 | Incomplete | ||
| 172 | Read status | Group | |
| 173 | Shared | Sharing type | |
| 174 | Creators | Bit depth | |
| 175 | Date | Horizontal resolution | |
| 176 | Folder name | Width | |
| 177 | Folder path | Vertical resolution | |
| 178 | Folder | Height | |
| 179 | Participants | Importance | |
| 180 | Path | Is attachment | |
| 181 | By location | Is deleted | |
| 182 | Type | Encryption status | |
| 183 | Contact names | Has flag | |
| 184 | Entry type | Is completed | |
| 185 | Language | Incomplete | |
| 186 | Date visited | Read status | |
| 187 | Description | Shared | |
| 188 | Link status | Creators | |
| 189 | Link target | Date | |
| 190 | URL | Folder name | |
| 191 | Media created | Folder path | |
| 192 | Date released | Folder | |
| 193 | Encoded by | Participants | |
| 194 | Producers | Path | |
| 195 | Publisher | By location | |
| 196 | Subtitle | Type | |
| 197 | User web URL | Contact names | |
| 198 | Writers | Entry type | |
| 199 | Attachments | Language | |
| 200 | Bcc addresses | Date visited | |
| 201 | Bcc | Description | |
| 202 | Cc addresses | Link status | |
| 203 | Cc | Link target | |
| 204 | Conversation ID | URL | |
| 205 | Date received | ||
| 206 | Date sent | ||
| 207 | From addresses | ||
| 208 | From | Media created | |
| 209 | Has attachments | Date released | |
| 210 | Sender address | Encoded by | |
| 211 | Sender name | Episode number | |
| 212 | Store | Producers | |
| 213 | To addresses | Publisher | |
| 214 | To do title | Season number | |
| 215 | To | Subtitle | |
| 216 | Mileage | User web URL | |
| 217 | Album artist | Writers | |
| 218 | Album ID | ||
| 219 | Beats-per-minute | Attachments | |
| 220 | Composers | Bcc addresses | |
| 221 | Initial key | Bcc | |
| 222 | Part of a compilation | Cc addresses | |
| 223 | Mood | Cc | |
| 224 | Part of set | Conversation ID | |
| 225 | Period | Date received | |
| 226 | Color | Date sent | |
| 227 | Parental rating | From addresses | |
| 228 | Parental rating reason | From | |
| 229 | Space used | Has attachments | |
| 230 | EXIF version | Sender address | |
| 231 | Event | Sender name | |
| 232 | Exposure bias | Store | |
| 233 | Exposure program | To addresses | |
| 234 | Exposure time | To do title | |
| 235 | F-stop | To | |
| 236 | Flash mode | Mileage | |
| 237 | Focal length | Album artist | |
| 238 | 35mm focal length | Sort album artist | |
| 239 | ISO speed | Album ID | |
| 240 | Lens maker | Sort album | |
| 241 | Lens model | Sort contributing artists | |
| 242 | Light source | Beats-per-minute | |
| 243 | Max aperture | Composers | |
| 244 | Metering mode | Sort composer | |
| 245 | Orientation | Disc | |
| 246 | People | Initial key | |
| 247 | Program mode | Part of a compilation | |
| 248 | Saturation | Mood | |
| 249 | Subject distance | Part of set | |
| 250 | White balance | Period | |
| 251 | Priority | Color | |
| 252 | Project | Parental rating | |
| 253 | Channel number | Parental rating reason | |
| 254 | Episode name | Space used | |
| 255 | Closed captioning | EXIF version | |
| 256 | Rerun | Event | |
| 257 | SAP | Exposure bias | |
| 258 | Broadcast date | Exposure program | |
| 259 | Program description | Exposure time | |
| 260 | Recording time | F-stop | |
| 261 | Station call sign | Flash mode | |
| 262 | Station name | Focal length | |
| 263 | Summary | 35mm focal length | |
| 264 | Snippets | ISO speed | |
| 265 | Auto summary | Lens maker | |
| 266 | Search ranking | Lens model | |
| 267 | Sensitivity | Light source | |
| 268 | Shared with | Max aperture | |
| 269 | Sharing status | Metering mode | |
| 270 | Product name | Orientation | |
| 271 | Product version | People | |
| 272 | Support link | Program mode | |
| 273 | Source | Saturation | |
| 274 | Start date | Subject distance | |
| 275 | Billing information | White balance | |
| 276 | Complete | Priority | |
| 277 | Task owner | Project | |
| 278 | Total file size | Channel number | |
| 279 | Legal trademarks | Episode name | |
| 280 | Video compression | Closed captioning | |
| 281 | Directors | Rerun | |
| 282 | Data rate | SAP | |
| 283 | Frame height | Broadcast date | |
| 284 | Frame rate | Program description | |
| 285 | Frame width | Recording time | |
| 286 | Total bitrate | Station call sign | |
| 287 | Station name | ||
| 288 | Summary | ||
| 289 | Snippets | ||
| 290 | Auto summary | ||
| 291 | Relevance | ||
| 292 | File ownership | ||
| 293 | Sensitivity | ||
| 294 | Shared with | ||
| 295 | Sharing status | ||
| 296 | |||
| 297 | Product name | ||
| 298 | Product version | ||
| 299 | Support link | ||
| 300 | Source | ||
| 301 | Start date | ||
| 302 | Sharing | ||
| 303 | Availability status | ||
| 304 | Status | ||
| 305 | Billing information | ||
| 306 | Complete | ||
| 307 | Task owner | ||
| 308 | Sort title | ||
| 309 | Total file size | ||
| 310 | Legal trademarks | ||
| 311 | Video compression | ||
| 312 | Directors | ||
| 313 | Data rate | ||
| 314 | Frame height | ||
| 315 | Frame rate | ||
| 316 | Frame width | ||
| 317 | Spherical | ||
| 318 | Stereo | ||
| 319 | Video orientation | ||
| 320 | Total bitrate |
Now, knowing this and having no guarantees Microsoft won’t, yet again, change property numbers in Windows 11 or any future update or version, using a hard coded list of properties could be harzardous to one’s health. There are no guaratee that the code will return the proper property value at the end of the day. It may work on 1 PC and not another. So this approach should never be used!
Which leaves us with ‘Iterating through the index numbers until we find the one we are actually after’!
Iterate through the index numbers until we find the one we are actually after
Another option, rather than building the ‘never ending property case statement’ would be to create a real-time iteration within the function itself to get the proper index number for the property that has been requested, and that can easily be accomplished by doing:
'---------------------------------------------------------------------------------------
' Procedure : GetFileProperty
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve a specific property for the specified file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename of the file to get the properties of
' sPropertyName : The name of the property you wish to retrieve for the file
'
' Usage:
' ~~~~~~
' GetFileProperty("C:\Temp\CropTest01.jpg", "Date Created")
' Returns -> 4/17/2020 7:55 PM
' GetFileProperty("C:\Temp\CropTest01.jpg", "Perceived type")
' Returns -> Image
' GetFileProperty("C:\Temp\CropTest01.jpg", "type")
' Returns -> JPG File
' GetFileProperty("C:\Temp\CropTest01.jpg", "dimensions")
' Returns -> 80 x 475
' GetFileProperty("C:\Temp\CropTest01.jpg", "kind")
' Returns -> Picture
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2021-12-14 Initial Public Release
' 2 2022-10-05 Fixed code to retrieving the Name property
'---------------------------------------------------------------------------------------
Function GetFileProperty(ByVal sFile As String, _
ByVal sPropertyName As String) As String
On Error GoTo Error_Handler
Dim oShell As Object 'Shell
Dim oFolder As Object 'Folder
Dim oFolderItem As Object 'FolderItem
Dim sFilePath As String
Dim sFileName As String
Dim i As Long
Dim lPropertyNumber As Long
Dim vPropValue As Variant
sFilePath = left(sFile, InStrRev(sFile, "\") - 1)
sFileName = right(sFile, Len(sFile) - InStrRev(sFile, "\"))
Set oShell = CreateObject("Shell.Application")
Set oFolder = oShell.NameSpace(CStr(sFilePath))
If (Not oFolder Is Nothing) Then
lPropertyNumber = -9999
Set oFolderItem = oFolder.ParseName(sFileName)
For i = 0 To 320 'This could be bumped up in case MS increase the number again
' Debug.Print i, oFolder.GetDetailsOf(oFolder.Items, i)
If oFolder.GetDetailsOf(oFolder.Items, i) = sPropertyName Then
lPropertyNumber = i
Exit For
End If
Next
If lPropertyNumber = -9999 Then
'Property not found
GoTo Error_Handler_Exit
Else
vPropValue = oFolder.GetDetailsOf(oFolderItem, lPropertyNumber)
If Trim(vPropValue & vbNullString) <> "" Then
vPropValue = Replace(Replace(Replace(Replace(vPropValue, ChrW(8236), ""), ChrW(8234), ""), ChrW(8207), ""), ChrW(8206), "")
End If
GetFileProperty = vPropValue
End If
End If
Error_Handler_Exit:
On Error Resume Next
If Not oFolderItem Is Nothing Then Set oFolderItem = Nothing
If Not oFolder Is Nothing Then Set oFolder = Nothing
If Not oShell Is Nothing Then Set oShell = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetFileProperty" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
Simpler code! Yes, it is a little less efficient, but at least it will be dependable regardless of the OS it is run on and should even by Future Proof. Also, this approach should also work if Microsoft decides to add new properties down the road making this flexible.
Completely Versatile
The beauty of these solutions is that they all works any file type. So, it isn’t just for images or media files. You can pull information about any file you wish:
- Images
- Videos (mpeg, avi, mod, …)
- Office documents (docs, xlsx, pptx, …)
- E-mails (msg)
- PDFs
- Compressed files (zip, 7z, …)
- Installers (exe, iso, …)
- and anything else you can throw at it!
You might be amazed what information is attached/part of your files!
Any way to change these properties, or some of them?
That’s more complicated and it depends on what you need to edit.
In some cases, you can simply do it with DSOFile automation. Refer to https://docs.microsoft.com/en-us/previous-versions/tn-archive/ee692828(v=technet.10)
In other cases, you could use automation to edit the properties via the application itself.
But in more advanced cases, you need to use a dll file to gain full access to the necessary functionality.
Good job Daniel, thank you
Excellent research, Daniel!
I cannot believe MS did not preserve file properties compatibility when they added more properties.
I also find it strange that there are no properties for GPS coordinates.
Is it possible to develop an Access_based utility which allows users to display all file properties and edit them?
Editing is the issue! You’ll need to employ a 3rd party dll, or use application specific automation, to edit them as MS has given us no means to do so through simple VBA.
Hi Daniel, I’m having a problem using the “GetFileProperty” function. The “name” property does not return any value. While I noticed that in the office (Windows 10 in English) it works for the creation date and for the modification date (I have not tested others) while at home (Windows 11 in Italian) the name does not work and the attribute for the date of creation and modification I have to write it in Italian. What do you think?
AT home:
This.Nome = GetFileProperty(pFullPathName, “Nome”) NOT WORKS
This.DataCreazione = GetFileProperty(pFullPathName, “Data creazione”) YES WORKS
This.DataModifica = GetFileProperty(pFullPathName, “Ultima modifica”) YES WORKS
At Office
This.Nome = GetFileProperty(pFullPathName, “Name”) NOT WORKS
This.DataCreazione = GetFileProperty(pFullPathName, “Date created) YES WORKS
This.DataModifica = GetFileProperty(pFullPathName, “Date modified”) YES WORKS
the function “Test_GetFileProperties” works always
Lorenzo
It’s because the name property returns a property = 0 which I coded to be ignored because I wouldn’t use such a function to retrieve the name of a file. Let me see how I can fix this.
Lorenzo, I’ve updated the function and it now works properly to retrieve the Name property. Thank you for bringing it to my attention.
Thanks Daniel, for your timeliness.
Regarding the question of the language of the operating system, do you think something can be done about it?
And again thanks in advance
Lorenzo
Perhaps build a cross-reference table and then check the Windows LCID value and convert the English term into the match LCID value/term.
If it’s for images, perhaps try another one of the approaches I’ve featured (I’d probably try GDI+), but no guarantee they’ll behave any differently, but still worth investigating.
https://www.devhut.net/get-image-exif-properties-using-vba-and-powershell/
https://www.devhut.net/getting-image-properties-exif-metadata-using-wia-in-vba/
https://www.devhut.net/getting-image-properties-exif-metadata-using-freeimage/
https://www.devhut.net/getting-all-of-an-images-properties-using-the-gdi-api/
ok Daniel. At the moment I have no special needs to have to extract a particular property of a file, and not even an image.
Currently your “FSO_GetFileInfo” function fully satisfies all needs. Thanks again.
Lorenzo
When I have a bit more time and will revisit this as you’ve brought up a very interesting question!
Perfect.
Thx
Daniel, I have been looking for a way to access file properties, but on a much smaller scale. I am trying to create photo albums in PPT that use the Title property of the image file for the caption. Could you give me some guidance on how, once I manually link the image file in PPT, I can display the Title property in a text box? I have done some minor VBA coding. Thank you very much for your time.
Alan
That’s an excellent job, Daniel! Thanks a lot for your research, code and comments! The functions work on my Windows 10 64-bit like charm!
Marek
Many thanks for this really helpful and informative article. I have used it successfully in VB6 on Win10 to find the “Date Taken” in jpg files. This returns the full date and the time hours and minutes but not the seconds. I know that the seconds are in the metadata as an image display program can show the time including the seconds.
Is there an extension to your code which can retrieve the seconds.
Peter
Perhaps try an alternate solutions such as:
Success via:- https://www.devhut.net/getting-a-specific-image-property-using-the-gdi-api/
Many thanks