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