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:
If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):
Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime
All code samples, download samples, links, ... on this site are provided 'AS IS'.
In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.
Download a Demo Database
Feel free to download a 100% unlocked demo copy by using the link provided below:
Download “Access - List Files and Get File Properties (x32 accdb)” GetFileListAndSpecs.zip – Downloaded 50613 times – 48.48 KB
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 →
