Finding an existing file & its path using Excel VBA

When writing Excel VBA code that works with external files like XML or JSON, it is important that you have controls in your code to check it the file exist. If your macro (VBA program) doesn’t find a file when it is expecting one, it will produce an error message, and you don’t want that.

The code below shows you how to check if the file exist.  If the file does exist, the code can proceed with the program execution. You also need to write code in case the file does not exist, perhaps a warning requesting the user to make sure the file is in the right path.

The code below is an User Defined Function, which will return a true or false value value based on the existence of a file. The function expect one argument, stored in variables File path, which will be the full path of the possibly existing file.

Function checkFileExists(sFilePath As String) As Boolean
    Dim sFileCheck As String
    Dim bReturn As Boolean

    'Initialize
    sFileCheck = ""
    bReturn = False

    'Check if file exists
    On Error Resume Next
    sFileCheck = Dir(sFilePath)
    On Error GoTo 0

    'Return Value
    If sFileCheck <> "" Then bReturn = True
    checkFileExists = bReturn
End Function

If you would like to know more about how to do this refer to the below link as well.

External Link: Learn to check/find existing file path