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:
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
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 50641 times – 48.48 KBExtended 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:
A Few Resources on the Subject
