Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Checking for the Existence of a File

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.

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.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

allen@sharonparq.com    18 Sep 2016, 10:09
Rich:

See my resources page, which is here:

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

-Allen
Rich Scholle    17 Sep 2016, 22:18
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.
Heather    09 Aug 2016, 15:37
#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
benny    10 Jun 2016, 17:08
this is a great post. thanks!
-b
Ajith Kumar    17 Feb 2016, 14:36
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:\Work\Work AH\Entry\" & Mnth & "-" & Yr & " Entries" 'Source path
    strDestFolder = "E:\Work\Work AH\Entry\" & 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
 
Anna    25 Feb 2015, 11:33
Typo, Thank you!
Typo    24 Feb 2015, 18:38
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.
Typo    24 Feb 2015, 18:34
Mathijs,
In the Function FileExists2, make sure the line:

FileExists = Dir(sPath) <> ""

actually says:

FileExists2 = Dir(sPath) <> ""

Just a typo in the example above.
Anna    04 Feb 2015, 20:11
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.
Matthijs    05 Sep 2014, 04:14
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?
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.