When creating routines, it is commonly helpful to check if a file or directory (folder) exists before attempting to perform some action. Searching the internet will yield several examples of doing either, but this is a routine that I wrote in order to have one routine to test either.
- Check if the supplied path to a file or directory (folder) exists.
- When you need to check if a file OR directory exists before you attempt to so something with it (such as load a file).
- None identified at this time.
This function has been tested extensively with Excel 97 through Excel 2010, and should work with any version of Excel without any modifications.
VBA Code Required:
- Place the following code in a standard module of the workbook you wish to use it in.
Public Function FileFolderExists(strFullPath As String) As Boolean 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Check if a file or folder exists On Error GoTo EarlyExit If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True EarlyExit: On Error GoTo 0 End Function
- Call it from another routine, as shown below.
- Please note that you should provide the full file path to the file/folder as the argument.
- You may supply or ignore the trailing \ on a directory if you choose. It will work either way.
Public Sub TestFolderExistence() 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Test if directory exists If FileFolderExists("F:\Templates") Then MsgBox "Folder exists!" Else MsgBox "Folder does not exist!" End If End Sub Public Sub TestFileExistence() 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Test if directory exists If FileFolderExists("F:\Test\TestWorkbook.xls") Then MsgBox "File exists!" Else MsgBox "File does not exist!" End If End Sub
Thanks to Rory Byrne for pointing out a more efficient way to accomplish the above using the Dir function, as well as Richard Barrett for pointing out that a previous incarnation would throw an error when testing a removable drive.