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 June 23, 2018)

11

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:

=FileExists1(A1)

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

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

MORE FROM ALLEN

Setting User Information

Need to change the information that Word stores about you? Here's how to find the info.

Discover More

Conditional Formatting for Errant Phone Numbers

Conditional formatting can be used to draw attention to all sorts of data based upon the criteria you specify. Here's how ...

Discover More

Word 2016 Styles and Templates (Table of Contents)

Styles are at the heart of Word's formatting power. Understanding how to use styles can greatly increase your ability to ...

Discover More

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!

More ExcelTips (menu)

Seeing Full File Names in the Files Menu

Wouldn't it be great if you could look at the files in the MRU list and see the full path and file names? Excel condenses ...

Discover More

Full Path Names in Excel

Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name ...

Discover More

File Formats that Include Field Formats

If you import data into Excel that is created by other programs, you know that it can be bothersome to get your data ...

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 2 + 4?

2018-06-04 03:57:22

abdelaziz.hussein

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

allen@sharonparq.com

Rich:

See my resources page, which is here:

http://excelribbon.tips.net/resources.html

-Allen


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 www.onlc.com offer training?

Thanks
Rich S.


2016-08-09 15:37:47

Heather

#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

benny

this is a great post. thanks!
-b


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

NoFiles:
'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

ErrHandler:
'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

Anna

Typo, Thank you!


2015-02-24 18:38:17

Typo

Anna,

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

FileExists = Dir(sPath) <> ""

says:

FileExists1 = Dir(sPath) <> ""

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


2015-02-24 18:34:54

Typo

Mathijs,
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

Anna

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

Matthijs

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