Many times when writing VBA code, we find the need to work with Files in one form or another. Whatever the process may be, we need to be sure that a File is present in a specified location. One Method to guarantee that a File does in fact exist, is to pass the Absolute Path to the File to a Function which will investigate the Path and return a Boolean Value (True/False) indicating whether or not the File exists. The following code segments will do just that. It accepts a single String Argument representing the Path to the File, if it exists, it returns True, and the User can proceed as normal, if not, a Message Box appears indicating to the User that the File does not exist in the location that was passed to it.
- Function Definition
-
Public Function fFileExists(strName As String) As Boolean
-
Dim strTempString As String
-
-
On Error Resume Next 'will check for an Error condition shortly in the Function assignment
-
-
'Search for the File with 'any' attribute set.
-
strTempString = Dir$(strName, vbHidden Or vbSystem Or vbArchive Or vbReadOnly)
-
-
'If the Length of the FilePath > 0 and no Error condition exists, File exists
-
fFileExists = ((Len(strTempString) > 0) And (Err.Number = 0))
-
-
Err.Clear 'Clear the Error Object
-
-
End Function
-
- Call to the Function
-
Const strPathToFile As String = "C:\Windows\System32\Import_1.txt"
-
-
If fFileExists(strPathToFile) Then
-
'normal processing of the File occurs here
-
Else
-
Dim strBaseFileName As String, strFolderPath As String, Msg As String
-
-
strBaseFileName = Right$(strPathToFile, Len(strPathToFile) - InStrRev(strPathToFile, "\"))
-
strFolderPath = Left(strPathToFile, InStrRev(strPathToFile, "\"))
-
-
Msg = strBaseFileName & " was not found in " & strFolderPath & vbCrLf & vbCrLf
-
Msg = Msg & "Please verify your FileName and/or Path, then try again."
-
MsgBox Msg, vbCritical, "Invalid Path"
-
End If
-
- Custom Error Message (Text only) if File and/or Path not found
-
Import_1.txt was not found in C:\Windows\System32\
-
-
Please verify your FileName and/or Path, then try again.
-
- Special considerations
- If your File exists on a Network Drive, you would probably want to enhance your Error Handling code since the Path and/or File Name may be valid, but there are a host of additional Network related Errors which may occur.
- As Access developers, code similar to that listed above should be SOP (Standard Operating Procedure) anytime we attempt to Open, Close, or Manipulate a File in any manner. We should never assume that the File exists, and the Path to it is valid. It would be a bad programming practice to attempt to operate on it prior to verifying its existence.
