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
sFileCheck = ""
bReturn = False
'Check if file exists
On Error Resume Next
sFileCheck = Dir(sFilePath)
On Error GoTo 0
If sFileCheck <> "" Then bReturn = True
checkFileExists = bReturn
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