Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining If a File Exists.
As you are programming your macros, you may have a need to determine if a particular file exists on disk. For instance, the purpose of your macro may be to open and read from a text file. Before doing so, you will want to check to see if the file exists, in order to avoid an error.
The following VBA function can be used to check for the existence of a file. All you need to do is pass it the full filename as a string, and the macro returns either True (if the file exists) or False (if it doesn't).
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function
This function works by using the Dir function, which checks for a file in a directory. If the file exists, then Dir returns the full path of the file. The True/False condition of the function is derived by comparing what Dir returns against an empty string. If something is returned, the file exists because Dir doesn't return an empty string.
You can use the function similar to the following:
If FileThere("c:\myfile.txt") Then
'
' Do stuff here
'
Else
MsgBox "File Not There!"
End If
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9050) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Determining If a File Exists.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you have some legacy data stored in very old spreadsheet files, you may be wondering how to get that data into an ...
Discover MoreWorkbooks are loaded from disk files, but workbooks aren't the only type of files that Excel can load. This tip provides ...
Discover MoreNeed to save a workbook in more than one location? Here's a handy macro that can save your workbook in lots of different ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-10-24 06:51:00
Ken Varley
I previously used the function "Exist" (Below) and called it with IF EXIST (<filename>) THEN, to produce a result of True or False.
Your function is much simpler. Top marks from me.
Note: My function catches both folders and files
------------------------------------------------------------------------
Function Exist(FileNameWithPathandExt As String)
Exist = False
Dim FSO
'Set Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(FileNameWithPathandExt) Then Exist = True _
Else _
If FSO.FileExists(FileNameWithPathandExt) Then Exist = True
End Function
--------------------------------------------------------------------------------------------
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Comments