Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Determining If a File Exists.
Written by Allen Wyatt (last updated April 24, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
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: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later 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 (2516) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Determining If a File Exists.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
The VBA programming language provide with Excel allows you to create and modify text files quite easily. Here's how to ...
Discover MoreThe data stored in a worksheet can often correspond to information external to that worksheet. For instance, you might ...
Discover MoreWhen you save a workbook to disk, you may want to automatically save a duplicate workbook in a separate location. This ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments