VBA Express : Multiple Apps - Check if directory or file exists (using VBA, not FSO)

Multiple Apps

Check if directory or file exists (using VBA, not FSO)

Ease of Use

Intermediate

Version tested with

2000, 2002, 2003, 2004 (Mac)

Submitted by:

Ken Puls

Description:

This function can be used to check if a specific folder or file exists. It works on both Windows and Macintosh systems.

Discussion:

It is a best practice to test if a folder exists before you try to write a file to it, and to test if a specific file exists before you try to open it in your code. Getting the error in the middle of your procedure is never a good thing, as it is distracting to your users and can result in problems. This procedure allows you to test for the existence of critical files/directories and take appropriate actions based on the results returned. Since adding this to the Knowledge Base, xCav8r has submitted two similar entries that use the File System Object to return the same results. It should be noted, however, that the File System Object will not work with the Macintosh. If you are interested in the alternate methods, you will need to get both entries, since they make a distinction between folders and files. The first called "Determine if a folder exists using the File System Object (FS0)." (http://www.vbaexpress.com/kb/getarticle.php?kb_id=634) The second is entitled "Determine if a file exists using the File System Object (FS0)." (http://www.vbaexpress.com/kb/getarticle.php?kb_id=635)

Code:

instructions for use

			Option Explicit 
 
Function FileOrDirExists(PathName As String) As Boolean 
     'Macro Purpose: Function returns TRUE if the specified file
     '               or folder exists, false if not.
     'PathName     : Supports Windows mapped drives or UNC
     '             : Supports Macintosh paths
     'File usage   : Provide full file path and extension
     'Folder usage : Provide full folder path
     '               Accepts with/without trailing "\" (Windows)
     '               Accepts with/without trailing ":" (Macintosh)
     
    Dim iTemp As Integer 
     
     'Ignore errors to allow for error evaluation
    On Error Resume Next 
    iTemp = GetAttr(PathName) 
     
     'Check if error exists and set response appropriately
    Select Case Err.Number 
    Case Is = 0 
        FileOrDirExists = True 
    Case Else 
        FileOrDirExists = False 
    End Select 
     
     'Resume error checking
    On Error GoTo 0 
End Function 
 
Sub TestItWithWindows() 
     'Macro Purpose: To test the FileOrDirExists function with Windows
     'Only included to demonstrate the function.  NOT required for normal use!
     
    Dim sPath As String 
     
     'Change your directory here
    sPath = "C:\Test.xls" 
     
     'Test if directory or file exists
    If FileOrDirExists(sPath) Then 
        MsgBox sPath & " exists!" 
    Else 
        MsgBox sPath & " does not exist." 
    End If 
End Sub 
 
Sub TestItWithMacintosh() 
     'Macro Purpose: To test the FileOrDirExists function with a Macintosh
     'Only included to demonstrate the function.  NOT required for normal use!
     
    Dim sPath As String 
     
     'Change your directory here
    sPath = "HardDriveName:Documents:Test.doc" 
     
     'Test if directory or file exists
    If FileOrDirExists(sPath) Then 
        MsgBox sPath & " exists!" 
    Else 
        MsgBox sPath & " does not exist." 
    End If 
End Sub

How to use:

Copy above code.
In the Office Application press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

Open the VBE again.
Change the path to your file in the appropriate Sub TestItWith procedure. (sPath = ???)
Press F5 to run the procedure.
You should see a message telling you that the file/directory does or does not exist.
An Excel based sample file is attached which demostrates how to use the formula from a worksheet cell or in VBA.

Sample File:

FileOrDirExists.zip 7KB

Approved by xCav8r

This entry has been viewed 332 times.