VBA Express : Multiple Apps - Determine if a file exists using the File System Object (FS0)

Multiple Apps

Determine if a file exists using the File System Object (FS0)

Ease of Use

Intermediate

Version tested with

2003

Submitted by:

xCav8r

Description:

When given a Windows path to a file, this function returns true if it exists; false, if it doesn't.

Discussion:

When you're working with files in VBA, an easy way to avoid a "file not found" error is to check if the file exists before trying to do something with it. This function uses the File System Object to determine if the file exists or not. This is an alternative to kpuls' KB entry entitled "Check if directory or file exists." His function arrives at the same result, but without the File System Object. His also supports macintosh paths. (http://www.vbaexpress.com/kb/getarticle.php?kb_id=559) This is related to another KB entry entitled "Test if a folder exists using the File System Object". (http://www.vbaexpress.com/kb/getarticle.php?kb_id=634) NB: This will not work on the Macintosh.

Code:

instructions for use

			 '---------------------------------------------------------------------------
 ' NB:  I have avoided abbreviating variable and procedure names to make
 '      things easier to grasp for beginners and non-native English speakers.
 '      Feel free to use Find and Replace to swap these names with something
 '      you prefer.
 '---------------------------------------------------------------------------
 
Option Explicit 
 
 '---------------------------------------------------------------------------
 ' Insert paths into the quotations below.  These will be used to test the
 ' MyFolderExists function.  The path may be a mapped drive path or a UNC Path.
 ' See the comments for the MyFileExists function for more information.
 '
 ' EG.:  "D:\MyFolder\MyFile.ext"   (mapped drive path)
 ' EG.:  "\\MachineName\VolumeName\MyFolder\MyFile.ext"  (UNC)
Private Const mcstrValidPathToFile As String = "C:\temp\test.txt" 
Private Const mcstrInvalidPathToFile As String = "C:\temp\test2.txt" 
 '---------------------------------------------------------------------------
 
Private Sub TestMyFileExists() 
     
     '---------------------------------------------------------------------------
     ' Desc      : This sub procedure calls the MyFileExists function using the
     '             module level constants defined above as the arguments,
     '             printing the results to the immediate window.  You need to
     '             adjust the values of these strings for your own file system.
     '
     ' Remarks   : The purpose of this procedure is to demonstrate how the
     '             MyFileExists function *might* be used.  It is included
     '             here soley for that reason, and it is not required for the
     '             function to work.
     '---------------------------------------------------------------------------
     
     ' NB:  These constants can be replaced with any valid string enclosed by
     '      quotation marks
     
    Debug.Print MyFileExists(mcstrValidPathToFile) 
    Debug.Print MyFileExists(mcstrInvalidPathToFile) 
     
End Sub 
 
Public Function MyFileExists(Path As String) As Boolean 
     
     '---------------------------------------------------------------------------
     ' Desc:       When given a path to a file, this function returns TRUE if
     '             the file exists and FALSE if it doesn't.
     '
     ' Argument:   The File System Object supports both mapped drive and UNC
     '             paths. The file extension must be included.
     '
     ' Required  : Microsoft Scripting Runtime (scrrun.dll)
     '---------------------------------------------------------------------------
     
    Dim objFSO As Object 
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
     
    Select Case objFSO.FileExists(Path) 
    Case True 
        MyFileExists = True 
    Case False 
        MyFileExists = False 
    End Select 
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
    Set objFSO = Nothing 
     
End Function

How to use:

Open your document.
Press Alt + F11 to open the Visual Basic Editor (VBE).
Insert a standard module. (Insert -> Module)
Paste the code in the window to the right. (F7 to view; CTRL + V to paste)
Save the file.

Test the code:

From the VBE, make sure the Immediate Window is visible (CTRL + G)
With your cursor anywhere in the module, press F5. (If your cursor is in the TestMyFileExists sub procedure, then you won't have to do the next step.)
Select TestMyFileExists from the dialog box that appears, and then click Run.

Sample File:

TestFileExistsWithFSO.zip 8.31KB

Approved by mdmackillop

This entry has been viewed 111 times.