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 August 19, 2016)

10

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

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

Importing Many Files Into Excel

Importing a single file is easy. Importing a whole slew of files can be much more of a challenge.

Discover More

Understanding Forms

If you have ever created several documents that contain the same basic information with only a few minor differences, then ...

Discover More

Unlinking an Excel Chart Automatically

When Excel charts are linked in a Word document, they update every time the document is opened. Here's how to unlink the ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (MENU)

Personal.xls File Not Opening

The Personal.xls workbook is used primarily to store macros that you want available through all of your workbooks. ...

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

Discover More

Closing Multiple Files

When working with multiple workbooks open, you may want a way to close them all with a single command. Here's the secret.

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 for this tip:

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. 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 nine more than 2?

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?


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.

Links and Sharing
Share