Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Checking for the Existence of a File.
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:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Function FileExists1(sPath As String)
FileExists1 = 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"
FileExists2 = 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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7402) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Checking for the Existence of a File.
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!
Need to know the current hour of the day? You can derive the information in your macros by using the Hour function, as ...
Discover MoreNeed to figure out an absolute value within your macro code? It's easy to do using the Abs function, described in this tip.
Discover MoreWhen opening a workbook, you may want to make sure that a particular worksheet is always displayed first. The only way to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-01-23 15:19:22
Matthew Zelceski
Hello,
Thank you for your amazing tips!
I cannot seem to get this working. No matter what I try I seem to get FALSE as a return. What am I doing wrong? I have tried all the methods shown above. My corresponding file name is listed in column B and I want to use this to check if a pdf is listed in a folder.
Thank you.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments