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.

Determining If a File Exists

by Allen Wyatt
(last updated June 23, 2018)

3

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).

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Wiping a Drive

Want to easily improve the security of your old data? Here's an addition to the venerable format command that can help.

Discover More

Using MPF Graphic Files

There are all sorts of file formats used to store graphics. You might think that one of those formats is the MPF format, ...

Discover More

Using the Sound Recorder

Over the years Windows has included many accessories you can use for a variety of purposes. One of the more arcane ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Cannot Double-Click to Open a Workbook

When you double-click on a workbook in Windows, the Excel program should be started and the workbook loaded. When this ...

Discover More

Working with Lotus 1-2-3 Spreadsheets

If you've got some older data around your office that started in an old Lotus 1-2-3 system, you may want to open it in ...

Discover More

Jumping Around Folders

When you open a workbook in Excel, the Open dialog box always starts within the folder in which you were last working. ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 1?

2015-03-27 14:23:06

Franklyn

Thank you, it works great.


2014-07-15 17:28:42

Greg Yoder

Never mind I figured it out. Had to add the function to the end of my sub


2014-07-15 17:22:01

Greg Yoder

I am using Excel 2010. Writing a macro to load a file from c:temp.

When I try using FileThere I get the following Error.

Compile Error SubFunction not defined?


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.