I was trying to help user mezentia on UtterAccess.com with getting a list of files contained within a folder with their respective properties (size, date created, date modified, last accessed, …), below is a link to the particular discussion thread:

File List And File Properties

I need to extract a list of the names of all the files in a specific directory. I also need to be able to get and store some of the attributes of each file, specifically the date created, date last changed, and current file size. Rather than populate a control on a form, could I create a collection to hold the filenames and attributes that I can then pass to another module to process? If so, any tips on how to create and then reference items in the collection?

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 of similar questions over the years, I thought I would add it to my blog in the hopes it may help someone else out.

Although there are a few build-in functions (such as: FileLen() – do not FileLen() as it is unreliable!, FileDateTime()), because we needed other more advanced properties, at the end of the day, the easiest method to get such information is to use the File System Object (FSO) to extract the relevant information regarding each file and I demonstrate how this is done.

To illustrate how you can use FSO, here is a simple VBA example of how you can get some basic file properties

'---------------------------------------------------------------------------------------
' Procedure : FSO_GetFileInfo
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve some basic file information
'               Right now, print result to the VBA immediate window
'               Could return an Array, Dictionary Object, ...
' 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: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path & filename with extension of the file to report on
'
' Usage:
' ~~~~~~
' FSO_GetFileInfo "c:\Tests\myXLS.xls"
' FSO_GetFileInfo "c:\Tests\myMDB.mdb"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2017-09-01              Initial Release
' 2         2021-12-19              Updated proc Name, variable naming to standardize
'                                   Added all available properties
'                                   Added Early Binding sample declarations
'                                   Updated Copyright
'                                   Minor update to inline comments
'---------------------------------------------------------------------------------------
Public Function FSO_GetFileInfo(ByVal sFile As String)
    On Error GoTo Error_Handler
    'Early Binding
    '**********************************************************************************
    '    'Requires a reference to 'Microsoft Scripting Runtime' library
    '    Dim oFSO                  As Scripting.FileSystemObject
    '    Dim oFSOFile              As Scripting.File
    '
    '    Set oFSO = New FileSystemObject
    'Late Binding
    '**********************************************************************************
    Dim oFSO                  As Object
    Dim oFSOFile              As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    '**********************************************************************************
    '**********************************************************************************
    'Invariable Code regardless if Early or Late Binding is used
    Set oFSOFile = oFSO.GetFile(sFile)

    With oFSOFile
        Debug.Print "Attributes:", .Attributes
        Debug.Print "Created:", .DateCreated
        Debug.Print "Accessed:", .DateLastAccessed
        Debug.Print "Modified:", .DateLastModified
        Debug.Print "Drive:", .Drive
        Debug.Print "Name:", .Name
        Debug.Print "Parent Folder:", .ParentFolder
        Debug.Print "Path:", .Path
        Debug.Print "Short Name:", .ShortName
        Debug.Print "Short Path:", .ShortPath
        Debug.Print "Size:", .size                'Remember FileLen() is unreliable!!!
        Debug.Print "Type:", .Type
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not oFSOFile Is Nothing Then Set oFSOFile = Nothing
    If Not oFSO Is Nothing Then Set oFSO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FSO_GetFileInfo" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

The demo illustrated how the above can be adapted to inventory a whole directory of files and log it to a table for easy review.

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Download a Demo Database

Feel free to download a 100% unlocked demo copy by using the link provided below:

Extended Properties

If you are looking to retrieve Extended Properties, Exif Properties, …, things that cannot be retrieved using FSO, be sure to check out my article on the subject:

How to Retrieve a File's Properties with VBA

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: No, I’m interested…

Continue reading →

A Few Resources on the Subject

FileSystemObject

The following sections explain the concept of the FileSystemObject and how to use it.

GetFile method (Visual Basic for Applications)

Details of the File Scripting Object GetFile method.

VBA's FileLen Unreliable!

I’ve been working on a personal project in which I need to index files. As part of the indexing process I retrieve the file size. The Problem With FileLen For years, in various databases, I have always used VBA’s built-in FileLen Function to quickly and easily return the file size (in bytes) and never experienced…

Continue reading →