Today, I thought I’d demonstrate a couple distinct methods that can be used to validate whether a file exists or not.

• VBA Dir Function
• File System Object‘s (FSO) FileExists Method
• GetAttr Function
• FindFirstFileA API
• PathFileExistsA API
• WizHook (Access Only)
• Windows Management Instrumentation (WMI)

Using The VBA Dir Function

'---------------------------------------------------------------------------------------
' Procedure : File_Exist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file; Returns True/False
'               True  => File exists
'               False => File does not exist
' 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: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to check for
'
' Usage:
' ~~~~~~
' ? File_Exist("C:\Temp\Flow-Undo-Dont.jpg")
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2007-03-06              Initial Release
' 2         2022-11-24              Update Function header
'                                   Update Error Handler
'---------------------------------------------------------------------------------------
Function File_Exist(ByVal sFile As String) As Boolean
On Error GoTo Error_Handler

        File_Exist = (Len(Dir(sFile)) > 0)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: File_Exist" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Example

If File_Exist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Or

If File_Exist("C:\Temp\Flow-Undo-Dont.jpg") = False Then
    MsgBox "The file could not be found!", vbCritical Or vbOKOnly, "Operation Aborted"
End If

Using The File System Object’s FileExists Method

'---------------------------------------------------------------------------------------
' Procedure : FSO_File_Exist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file; Returns True/False
'               True  => File exists
'               False => File does not exist
' 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: Late Binding  -> None required
'             Early Binding -> Microsoft Scripting Runtime
' Also check out: https://www.devhut.net/vba-fso-files-folders-drives-and-more/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to check for
'
' Usage:
' ~~~~~~
' ? FSO_File_Exist("C:\Temp\Flow-Undo-Dont.jpg")
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-11-24              Initial Release
'---------------------------------------------------------------------------------------
Function FSO_File_Exist(ByVal sFile As String) As Boolean
On Error GoTo Error_Handler
    #Const FSO_EarlyBind = False 'True = Early Binding, False = Late Binding

    #If FSO_EarlyBind = True Then
        Dim oFSO              As Scripting.FileSystemObject
        Set oFSO = New FileSystemObject
    #Else
        Dim oFSO              As Object
        Set oFSO = CreateObject("Scripting.FileSystemObject")
    #End If

    FSO_File_Exist = oFSO.FileExists(sFile)

Error_Handler_Exit:
    On Error Resume Next
    Set oFSO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: FSO_File_Exist" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has occurred!"
    Resume Error_Handler_Exit
End Function

If you go the route of FSO, I’d recommend implementing Self-Healing Object Variables, so be sure to checkout:

Self-Healing Object Variables

So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables! So What Are Self-Healing Object Variables? In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session.  If reset, they will…

Continue reading →

Usage Example

If FSO_File_Exist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Or

If FSO_File_Exist("C:\Temp\Flow-Undo-Dont.jpg") = False Then
    MsgBox "The file could not be found!", vbCritical Or vbOKOnly, "Operation Aborted"
End If

Using The GetAttr Function

'---------------------------------------------------------------------------------------
' Procedure : GA_FileExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file; Returns True/False
'               True  => File exists
'               False => File does not exist
' 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: None required
' References: https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/getattr-function
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to check for
'
' Usage:
' ~~~~~~
' GA_FileExist("C:\Temp\Flow-Undo-Dont.jpg")
'   Returns -> True/False
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-02-02
'---------------------------------------------------------------------------------------
Function GA_FileExist(ByVal sFile As String) As Boolean
On Error GoTo Error_Handler
    Dim iRetVal               As Integer

    iRetVal = GetAttr(sFile)
    GA_FileExist = Not ((iRetVal And vbDirectory) = vbDirectory)
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    'We're here because nothing was found
    Resume Error_Handler_Exit
End Function

Usage Example

If GA_FileExist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Using the FindFirstFileA API

The following is an approach I came up with while wondering if there were any APIs that could do the job. It has limited testing, but does seem to work nicely. That said there are a number of other APIs that could be used in a similar fashion.

Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long

Private Type FILETIME
  dwLowDateTime  As Long
  dwHighDateTime As Long
End Type

Const MAX_PATH  As Long = 260

Type WIN32_FIND_DATA
        dwFileAttributes As Long
        ftCreationTime As FILETIME
        ftLastAccessTime As FILETIME
        ftLastWriteTime As FILETIME
        nFileSizeHigh As Long
        nFileSizeLow As Long
        dwReserved0 As Long
        dwReserved1 As Long
        cFileName As String * MAX_PATH
        cAlternate As String * 14
End Type

Private Const INVALID_HANDLE_VALUE  As Long = -1


'---------------------------------------------------------------------------------------
' Procedure : API_FileExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file; Returns True/False
'               True  => File exists
'               False => File does not exist
' 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:      FindFirstFile API and Type/Const Declarations
' References: https://learn.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-findfirstfilea
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to check for
'
' Usage:
' ~~~~~~
' API_FileExist("C:\Temp\Flow-Undo-Dont.jpg")
'   Returns -> True/False
'
' API_FileExist("C:\Temp\Flow-Undo-Dont.*")
'   Returns -> True/False 'careful using wildcards though!
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-02-02              Initial Release
'---------------------------------------------------------------------------------------
Function API_FileExist(ByVal sFile As String) As Boolean
    On Error GoTo Error_Handler
    Dim lHwnd                 As Long
    Dim FileData              As WIN32_FIND_DATA

    lHwnd = FindFirstFile(sFile, FileData)
    If lHwnd <> INVALID_HANDLE_VALUE Then API_FileExist = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: API_FileExist" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Example

If API_FileExist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Using the PathFileExistsA API

I also discovered the PathFileExistsA API which can be used to test the existence of both files and/or folders.

Private Declare Function PathFileExists Lib "SHLWAPI.DLL" Alias "PathFileExistsA" (ByVal pszPath As String) As Long


'---------------------------------------------------------------------------------------
' Procedure : API_PathFileExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file or folder; Returns True/False
'               True  => File/Folder exists
'               False => File/Folder does not exist
' 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:      FindFirstFile API and Type/Const Declarations
' References: https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/getattr-function
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename (optional) to check for
'
' Usage:
' ~~~~~~
' API_PathFileExist("C:\Temp\Flow-Undo-Dont.jpg") 'Check for a File
'   Returns -> True/False
'
' API_PathFileExist("C:\Temp\") 'Check for a Folder
'   Returns -> True/False
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-02-02              Initial Release
'---------------------------------------------------------------------------------------
Function API_PathFileExist(ByVal sFile As String) As Boolean
    On Error GoTo Error_Handler

    API_PathFileExist = PathFileExists(sFile)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: API_PathFileExist" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Example – File

If API_PathFileExist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Usage Example – Folder

If API_PathFileExist("C:\Temp\") = True Then
    'Do something because the folder exists
End If

In reality, I’ve wrapped the function in another function, which truthfully isn’t necessary. Feel free to simply use the API directly! Hind sight is always 20/20!
 

Using WizHook (Access Only)

This was brought to my attention by a comment on my YouTube channel by @epdmfn3747. I knew of WizHook, but haven’t really played around with it much. I know it has a lot of possibilities, but no documentation. The other drawback is this is Microsoft Access specific. Never the less, thanks to @epdmfn3747 comment it was easy to wrap it up in a reusable function like so:

'---------------------------------------------------------------------------------------
' Procedure : WizHook_FileExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file; Returns True/False
'               True  => File exists
'               False => File does not exist
' 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: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to check for
'
' Usage:
' ~~~~~~
' ? WizHook_FileExist("C:\Temp\Flow-Undo-Dont.jpg")
'   Returns -> True/False
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-02-02              Initial Release
'---------------------------------------------------------------------------------------
Function WizHook_FileExist(ByVal sFile As String) As Boolean
On Error GoTo Error_Handler

    WizHook.Key = 51488399
    WizHook_FileExist = WizHook.FileExists(sFile)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: WizHook_FileExist" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Example

If WizHook_FileExist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Using Windows Management Instrumentation (WMI)

One last approach I thought of was using WMI to query the system. The code would look something like the following.

'---------------------------------------------------------------------------------------
' Procedure : WMI_FileExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Test for the existence of a file; Returns True/False
'               True  => File exists
'               False => File does not exist
' 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: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename to check for
'
' Usage:
' ~~~~~~
' ? WMI_FileExist("C:\Temp\Flow-Undo-Dont.jpg")
'   Returns -> True/False
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-02-03              Initial Release
'---------------------------------------------------------------------------------------
Public Function WMI_FileExist(ByVal sFile As String) As Boolean
    On Error GoTo Error_Handler
    #Const WMI_EarlyBind = False    'True => Early Binding / False => Late Binding
    #If WMI_EarlyBind = True Then
        Dim oWMI              As WbemScripting.SWbemServices
        Dim oCols             As WbemScripting.SWbemObjectSet
    #Else
        Dim oWMI              As Object
        Dim oCols             As Object
    #End If
    Dim sWMIQuery             As String         'WMI Query

    sFile = Replace(sFile, "\", "\\")    'Escape \ character!

    Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
    sWMIQuery = "SELECT * FROM CIM_Datafile WHERE Name = '" & sFile & "'"
    Set oCols = oWMI.ExecQuery(sWMIQuery)
    WMI_FileExist = (oCols.Count <> 0)

Error_Handler_Exit:
    On Error Resume Next
    Set oCols = Nothing
    Set oWMI = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WMI_FileExist" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Example

If WMI_FileExist("C:\Temp\Flow-Undo-Dont.jpg") = True Then
    'Do something because the file exists
End If

Speed Comparisons

Stubborn, stubborn me! I had to know how the various approaches compared to each other (speedwise), so I quickly ran a few test and these were the results. Now this was on limited testing and all local. So I’d love to also run this test over a corporate network to see if there are any surprises.
 

I’ve rerun the test multiple times (it’s automated, so easy to do) and in some cases GetAttr comes in faster, and other times it’s PathFileExist.

WizHook seems to bounce around and I don’t quite understand why. Sometimes it is fast and other times it is slower for the exact same files???

The only finding that doesn’t seem to change is FSO and WMI are consistently the slowest techniques, by far. Not too much of a surprise at the end of the day. That said, do notice the improvement the SHOV technique brings though.

So my recommendation, based on my testing, would be to use either the GetAttr, PathFileExist or FindFirstFile approach. That said, GetAttr is baked into VBA and doesn’t require any declarations…

One exception perhaps, if you are already using FSO with SHOV in your db, so you’ve already taken the initial CreateObject performance hit, then, and only then does FSO SHOV still hold value as an option.

So now you know and you can make your own educate choice!
 

Resources on the Subject

Dir function (Visual Basic for Applications)

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

FileExists method (Visual Basic for Applications)

Returns True if a specified file exists; False if it does not.

GetAttr function (Visual Basic for Applications)

Returns an Integer representing the attributes of a file, directory, or folder.

FindFirstFileA function (fileapi.h) - Win32 apps

Searches a directory for a file or subdirectory with a name that matches a specific name (or partial name if wildcards are used). (ANSI)

PathFileExistsA function (shlwapi.h) - Win32 apps

Determines whether a path to a file system object such as a file or folder is valid. (ANSI)