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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
The undo list can be a lifesaver when working in a macro. Unfortunately, the undo list is not preserved when you run a ...
Discover MoreWant to print a document by using a macro? One way is to display the Print dialog box and allow the user to interact with ...
Discover MoreNeed your macro to get some input from a user? The standard way to do this is with the InputBox function, described in ...
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 © 2019 Sharon Parq Associates, Inc.
Comments