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: Checking for the Existence of a File.

Checking for the Existence of a File

by Allen Wyatt
(last updated February 22, 2021)


John has a column of invoice numbers in a worksheet. He has a directory on the network where staff save a PDF of the actual invoice and name it using the same invoice number that is in the worksheet. Each invoice number in the worksheet should have a correspondingly named PDF in the directory on the network. John is looking for a way, within Excel, to check and verify that a PDF really does exist for each invoice number.

There is no way to do this using built-in Excel commands. You can, however, create a macro that will do the checking for you. For instance, consider the following simple user-defined function:

Function FileExists1(sPath As String)
    FileExists = Dir(sPath) <> ""
End Function

The routine simply returns a True or False value, based on whether the specified file exists. The value that is passed to the function needs to include a full path and file name. For example, if the file specification (including the path) were in cell A1, you could use the following in a cell:


You may not, however, want to put the full path name into the cell. In that case, you could specify it in the actual formula, in this way:

=FileExists1("c:\your\path\here\" & A1 & ".pdf")

Of course, you could instead specify the path in the user-defined function:

Function FileExists2(sFile As String)
    sPath = "c:\your\path\here\" & sFile & ".pdf"
    FileExists = Dir(sPath) <> ""
End Function

With such a function you could easily create a formula in your worksheet that would "flag" any invoices missing from the directory:

=IF(FileExists2(A1),"","Missing Invoice")


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 (7512) 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: Checking for the Existence of a File.

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


Setting a Spacing Adjustment in the Equation Editor

The Equation Editor is a handy tool when you are creating documents that rely on mathematical formulas. If you want to ...

Discover More

Ensuring Unique Values in a Column

If you want to make sure that only unique values are entered in a particular column, you can use the data validation ...

Discover More

Custom Formats for Scientific Notation

Excel allows you to format your numeric values in a wide variety of ways. One such formatting option is to display ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Selectively Importing Records

Want to easily control which records get imported from a text file into Excel? It's easy to do when you write the macro ...

Discover More

Aligning Cells when Importing from CSV

When you import information from a CSV text file, Excel formats the data according to its default settings. Wouldn't it ...

Discover More

Importing Huge Data Files

Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here's ...

Discover More

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

View most recent newsletter.


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?

2020-09-24 10:05:30


It wasn't working for me either, but I noticed that on the second line 'FileExists' is missing the 1 on the end. I changed it and it works....

Function FileExists1(sPath As String)
FileExists = Dir(sPath) <> ""
End Function

2019-07-03 08:29:44


allways the same error, or must the return value of the function not the same name as the function him self??

2018-06-04 03:57:22


thank a lot, but there are problem>>
assume i assigned cell A1 for the file location.
when i create a formula it works and reflect the status properly. but,
as a test i removed the file from its location to check if the excel will know the file still exist or not but it did not updated.
i should go inside A1 cell or formula cell and press enter to make the cell of formula know that the file has been removed.

2016-09-18 10:09:40


See my resources page, which is here:


2016-09-17 22:18:38

Rich Scholle

Besides offering your books - do you provide training. I'm looking for Advanced VBA training/mentoring?

If you do not whom would you recommend?

I see NewHorizon and offer training?

Rich S.

2016-08-09 15:37:47


#NAME Problem: You have to insert this into Excel as a Function (you can't simply enter a formula as =FileExists1).

After pasting the code in VBA:
In the Ribbon, select Formulas > Insert Function > Select User Defined in the Drop down > Click OK > Select the cell you want to check (A1 for example) > Click OK

2016-06-10 17:08:01


this is a great post. thanks!

2016-02-17 14:36:45

Ajith Kumar

Hi All, I have problem in Macro created for "Copy and Rename Folder and Files Using File System Objetc (FSO)"

Below is the VBA script.. from the below script I need to change the file name. Kindly help me.

Sub Copy_and_Rename_To_New_Folder()

Dim objFSO As FileSystemObject, objFolder As Folder, PathExists As Boolean
Dim objFile As File, strSourceFolder, Mnth, Dmnth, Yr, Dyr As String, strDestFolder As String
Dim x, Counter As Integer, Overwrite As String, strNewFileName As String
Dim strName As String, strMid As String, strExt As String

Application.ScreenUpdating = False 'turn screenupdating off
Application.EnableEvents = False 'turn events off

Mnth = DatePart("m", (DateAdd("m", -1, Date))) 'Source Month
Yr = DatePart("yyyy", (DateAdd("m", -1, Date))) 'Source Year
Dyr = Year(Date) 'Desination Month
Dmnth = Month(Date) 'Destination Year

If Len(Mnth) = 1 Then Mnth = "0" & Mnth
If Len(Dmnth) = 1 Then Dmnth = "0" & Dmnth

strSourceFolder = "E:WorkWork AHEntry" & Mnth & "-" & Yr & " Entries" 'Source path
strDestFolder = "E:WorkWork AHEntry" & Dmnth & "-" & Dyr & " Entries" 'destination path

'below will verify that the specified destination path exists, or it will create it:
On Error Resume Next
x = GetAttr(strDestFolder) And 0
If Err = 0 Then 'if there is no error, continue below
PathExists = True 'if there is no error, set flag to TRUE
Overwrite = MsgBox("The folder may contain duplicate files," & vbNewLine & _
"Do you wish to overwrite existing files with same name?", vbYesNo, "Alert!")
'message to alert that you may overwrite files of the same name since folder exists
If Overwrite <> vbYes Then Exit Sub 'if the user clicks YES, then exit the routine..
Else: 'if path does NOT exist, do the next steps
PathExists = False 'set flag at false
If PathExists = False Then MkDir (strDestFolder) 'If path does not exist, make a new one
End If 'end the conditional testing

On Error GoTo ErrHandler
Set objFSO = New FileSystemObject 'creates a new File System Object reference
Set objFolder = objFSO.GetFolder(strSourceFolder) 'get the folder
Counter = 0 'set the counter at zero for counting files copied

If Not objFolder.Files.count > 0 Then GoTo NoFiles 'if no files exist in source folder "Go To" the NoFiles section

For Each objFile In objFolder.Files 'for every file in the folder...

strName = Left(objFile.Name, 5) 'Fist
strMid = Format(Now(), "mm") ' Middle
strExt = Mid(objFile.Name, 8, 50) ' Last

strNewFileName = strName & strMid & strExt 'build the string file name (can be done below as well)

objFile.Copy strDestFolder & "" & strNewFileName, False 'False = do not overwrite/ True = Overwrite if exist

'End If 'where conditional check, if applicable would be placed.

Counter = Counter + 1
Next objFile 'go to the next file

MsgBox "All " & Counter & " Files from " & vbCrLf & vbCrLf & strSourceFolder & vbNewLine & vbNewLine & _
" copied to: " & vbCrLf & vbCrLf & strDestFolder, , "Completed Transfer/Copy!"

Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects

Exit Sub

'Message to alert if Source folder has no files in it to copy
MsgBox "There Are no files or documents in : " & vbNewLine & vbNewLine & _
strSourceFolder & vbNewLine & vbNewLine & "Please verify the path!", , "Alert: No Files Found!"
Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects

Application.ScreenUpdating = True 'turn screenupdating back on
Application.EnableEvents = True 'turn events back on

Exit Sub

'A general error message
MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
"Please verify that all files in the folder are not currently open," & _
"and the source directory is available"

Err.Clear 'clear the error
Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
Application.ScreenUpdating = True 'turn screenupdating back on
Application.EnableEvents = True 'turn events back on
End Sub

2015-02-25 11:33:09


Typo, Thank you!

2015-02-24 18:38:17



Same deal, make sure in the Function FileExists1 that the following line:

FileExists = Dir(sPath) <> ""


FileExists1 = Dir(sPath) <> ""

Also, ensure you've put this into a Module, not just the worksheet code.

2015-02-24 18:34:54


In the Function FileExists2, make sure the line:

FileExists = Dir(sPath) <> ""

actually says:

FileExists2 = Dir(sPath) <> ""

Just a typo in the example above.

2015-02-04 20:11:52


I cannot get this to work either.

I have pasted the function in a macro-enabled workbook (tried both the data sheet and ThisWorkbook). In one column of data I have the full path, filename, and extension concatenated. In another column, I have the formula =FileExists1(E2). I get a #NAME? error.

Old thread, but if there are any good Samaritans looking to help, it would be appreciated.

2014-09-05 04:14:30


I used this, thankyou, but i only get a 0 if i enter the formula. I used the user-defined function without the path. In the cell i put the formula with path + A1 etc

Can you tell me what i did wrong?

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

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.