top of page
John

Get extended properties for a file

Updated: Apr 11

It's fairly straight-forward to get the basic properties for a file using standard VBA code and/or using the FileSystemObject - file size, whether a file is read-only, whether a file is hidden, date created / modified / accessed - these are all easily accessible.


However some properties are not so easily available. If you use File Explorer and right-click on a file then select Properties from the menu, then select Details in the Properties dialog, for many types of file you will see the 'extended properties'. Exactly what properties you see depends on the type of the file, the folder it is saved within, the OS version and the application that created the file. Examples are:

  • .xlsx file - you might have properties for 'Authors' and 'Last printed'

  • .pptx file - you might have properties for 'Slides' and 'Total editing time'

  • .docx file - you might have properties for 'Authors' and 'Word count'

  • .mp3 file - you might have have properties for 'Album' and 'Genre'

  • .jpg file taken with a camera - you might have properties for 'Camera model', 'Dimensions' and 'F-stop'

  • .exe file - you might have properties for 'Product name' and 'Product version'

Some of the extended file properties for a .jpg file
Some of the extended file properties for a .jpg file

So how do you get the values for these 'extended properties'?


First, this is how to just log all of the available extended properties and their values to the Immediate window. In any Office application, add this Sub to a standard module:

Then call it, passing in the full path to the file you want the extended properties for. Normally pass True (or omit) the bOnlyWithValidValue parameter otherwise you may find that more rows of data are sent to the Immediate window than it can handle (so some rows will 'scroll off the top' of the window and be lost).


Nice enough. But what if you want to get the value of a specific extended property to use in your VBA code? Add this Function to a standard module:

An example of using this is:

Obviously replace the value for sFILE_PATH with the path to an actual file on your device, and set the value for sPROPERTY_NAME to the name of the extended property you want to get the value for.


The key line of code here is ...

... which:

  • If the requested extended property was found, returns True and loads the value of it into the sPropertyValue String variable that your code can then use for whatever purpose is required.

  • If the requested extended property was not found, returns False so that your code can handle the absence of the extended property

Why bother with the 'Try' ... why not just return the value of the extended property or return an empty String if it was not found? The answer is that an empty String might actually be the value of the extended property and then your code would never be able to tell the difference between 'extended property does not exist' and 'extended property exists and its value is an empty String'.


For further explanation of what the TryGetFileProperty Function does and how it does it, see the comments in the code.


Some final notes:

  1. Obviously this code works on Windows only.

  2. My device uses Windows configured for the English language. I do not have a device with a non-English language version of Windows on which to test the code and so I do not know if the extended properties use English-language or (I guess more likely) local-language names and return local-language values.

  3. Both the LogAllFileProperties Sub and the TryGetFileProperty Function use two 'magic values': -1 and 350. Every extended property has an index number but that index number is not fixed, so you cannot use an index number directly and rely on getting back the value for the extended property that you want to get. Hence the loop that accesses the extended property via its index number but then checks whether the name of the extended property matches that of the required extended property. In the course of using these two procedures over a number of years, I have never seen an index number out of these values. But I can't rule out that new extended properties won't be added over time and so the -1 and 350 values might need to be adjusted.

1 comment

Recent Posts

See All

1 Comment


Re. Sub LogAllFileProperties, I found the results more useful by replacing the following statement

If Len(sDetailValue) > 0 Or (Not bOnlyWithValidValue And Len(sDetailName) > 0) Then

with this statement

If (Len(sDetailValue) > 0 Or (Not bOnlyWithValidValue)) And Len(sDetailName) > 0 Then

so only valid sDetailName's are included.

Like
bottom of page