Accessing detailed file information provided by the Operating System

Download Demonstration MDB (Access 2000+) (Zipped)

Download Demonstration ACCDB [bigger form & anchoring, modified by Crystal Long] (Zipped)
        (also see RowSourceType custom function video -- thanks Crystal!)

Also check out this alternative solution by Colin Riddington

Background

Have you ever wanted to find out generic properties about a file, such as the duration length of an MP3 file, or the date a JPEG picture file was taken on a digital camera?

Windows Explorer shows us this sort of information, so surely we can tap into this facility to programmatically access these "properties"?

Note that I use the term "property" rather generically. Some information that Windows Explorer provides is calculated - e.g. the duration of an MP3 file isn't actually stored in the file, but instead is calculated from the filesize divided by the audio bitrate (for fixed bitrate files, anyway). Also, some of the displayed information comes from real properties in the file, such as MP3 ID3 tags or JPEG EXIF data. Windows Explorer properties can also originate from the filesystem, such as the shared status of the file.

So can we make use of the Operating System support for these detailed properties using VBA?

Fortunately, Windows does provide mechanisms for obtaining these detailed properties directly from the Operating System.

Unfortunately, the low-level interfaces that are defined to access these properties are not VB-friendly (they don't support the IDispatch interface and therefore need an external typelibrary reference at a minimum, or preferably a small DLL written in a lower level language). As Sascha Trowitzsch points out later in the comments of this article, there is a type library already defined for helping scripting languages access some of these properties, although I have to say that the options are more limited than the solution I'm going to provide here.

Some of you reading this will be familiar with my article on Disabling the Mouse Wheel in Access Forms and know that I provided a convenient solution to including native machine code into a small VBA module which doesn't require any external references. In effect, it is like having a small DLL file contained completely within your VBA project. Hopefully you can you see where this is heading...

I have done the same here in that the sample database file contains native machine code inside a VBA code module in order to avoid you needing to distribute an extra DLL or typelibrary file with your application should you choose to use this code.

At this point, I must thank Crystal Long (Microsoft Access MVP) for the inspiration to write the code and this article. Check out her ListFiles Database that makes use of this code.

Usage guide

1. Import the three VBA class modules 'FilePropertyExplorer', 'FileProperties' and 'FileProperty' into your project.
2. Call the FilePropertyExplorer.OpenFile(FilePath) method to obtain a reference to an object of type FileProperties.

Overview of each class

FilePropertyExplorer class is the root class. It is a self-instantiated class that contains the native machine code used throughout.

    Public Function OpenFile(FilePath As String, _
                    Optional WriteSupport As Boolean) As FileProperties

Given a valid file path, will return an object of type FileProperties. FileProperties are an enumerable collection of all the available properties for that file.

If you want to support writing to property values, you must open the file with the WriteSupport parameter set to True. This indirectly causes the property set to be more restricted (in terms of enumeration) in that you can then only enumerate direct properties of the file (for example, you won't get properties about the shared status of the file). Write-support is only applicable when run on Windows Vista or newer - in older systems, this is ignored. When the file is opened with write-support, the file is locked until the FileProperties object is released.

TIP: You do not need to declare a variable of type FilePropertyExplorer in your code. Just call FilePropertyExplorer.OpenFile() directly. See the examples below and in the provided database.

---

FileProperties is a collection class and supports the following members;

    Property Get Item(IndexOrNameOrID) As FileProperty

IndexOrNameOrID can be an integer index (from zero to .Count-1)
IndexOrNameOrID can be a name that corresponds to FileProperty.Name
IndexOrNameOrID can be an ID that corresponds to FileProperty.ID

    Property Get Count() As Long

Returns the count of properties defined in the collection

This collection class supports the use of For-Each enumeration -- see examples in the following section.

---

FileProperty is an item class and supports the following methods;

    Property Get Name() As String

Returns the system identity name of the property. e.g. "System.ItemFolderNameDisplay". This is safe to use as an identity field even on non-English systems. A handful of properties do not have a name assigned and the only method of referencing them is by the ID property.

    Property Get NameDesc() As String

This is a localized version of the name, but is not always available.

    Property Get ID() As String

This is a unique identity that can always be used to refer to the property. It consists of a GUID (identifying the property schema) followed by an integer (identifying the property index in the schema).

    Property Get Value() As Variant

This is a VARIANT value respresenting the current value of the property.
TIP: This can be an array for multi-value type properties.

    Property Let Value(Val As Variant)

This is the write method for the property Value. Requires Vista or newer.
Returns a "Permission Denied" error if the property is not writable.

    Property Get ValueDesc() As String

This is a STRING value representing the current value of the property, in LOCALIZED form. Multi-value properties are already combined into a single string. Properties such as "Filesize" might return "5.6Mb", and "Frequency" might return "44khz" rather than the full raw value.

Some simple examples

Obtaining a single property of a file:

    MsgBox FilePropertyExplorer.OpenFile(Path).Item("System.ItemFolderNameDisplay").Value

Obtaining a few properties of a file:

    With FilePropertyExplorer.OpenFile(Path)
        MsgBox .Item("System.ItemFolderNameDisplay").Value
        MsgBox .Item("System.ItemType").Value
        MsgBox .Item("{B725F130-47EF-101A-A5F1-02608C9EEBAC}/14").Value
    End With

Enumerating through all available properties of a file (using For-Each):

    Dim FileProp As FileProperty
    For Each FileProp In FilePropertyExplorer.OpenFile(Path)

        MsgBox FileProp.Name & ": " & FileProp.ValueDesc

    Next

Enumerating through all available properties of a file (using a While loop):

    Dim FileProps As FileProperties
    Dim FileProp As FileProperty
    Dim FilePropsCount As Long
    Dim Index As Long

    Set FileProps = FilePropertyExplorer.OpenFile(Path)
    FilePropsCount = FileProps.Count
    While Index < FilePropsCount

        Set FileProp = FileProps.Item(Index)
			
        MsgBox FileProp.Name & ": " & FileProp.ValueDesc
			
        Index = Index + 1

    Wend

Dumping all properties of a file to the immediate window

    Dim FileProps As FileProperties
    Set FileProps = FilePropertyExplorer.OpenFile(FilePath)

    ' Output header
    Debug.Print String(280, "-")
    Debug.Print "PROPERTY ID"; _
                Tab(50); " | "; "PROPERTY NAME"; _
                Tab(90); " | "; "PROPERTY NAME DESCRIPTION"; _
                Tab(130); " | "; "PROPERTY VALUE DESCRIPTION"; _
                Tab(205); " | "; "PROPERTY VALUE"
    Debug.Print String(280, "-")
    
    ' Enumerate through the properties
    Dim FileProp As FileProperty
    For Each FileProp In FileProps
        
        With FileProp
    
            Debug.Print .ID; _
                        Tab(50); " | "; .Name; _
                        Tab(90); " | "; .NameDesc; _
                        Tab(130); " | "; .ValueDesc; _
                        Tab(205); " | "; VarToStr(.Value)
        
        End With
        
    Next

Enjoy! If you like this code, you could always leave a comment :)

Wayne Phillips