How to Determine if a File Exists in Excel VBA

Written by stephanie ellen
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Determine if a File Exists in Excel VBA
Excel can help you manage vast amounts of data. (Jupiterimages/Photos.com/Getty Images)

Visual Basic for Applications (VBA) is a subset of Visual Basic that is used to tailor Microsoft Office programs to suit your needs. When writing to a file in VBA, it's good practice to make sure that particular file exists before you attempt to write to it. If you want to find if a file exists in Excel, build a VBA module that does the trick. In order to use this function, you should have some prior experience with VB programming.

Skill level:
Challenging

Other People Are Reading

Things you need

  • Excel 2007 or Excel 2010

Show MoreHide

Instructions

  1. 1

    Copy the following code:

    Option Explicit

    Function FileOrDirExists(PathName As String) As Boolean

     'Macro Purpose: Function returns TRUE if the specified file
    
     '               or folder exists, false if not.
    
     'PathName     : Supports Windows mapped drives or UNC
    
     '             : Supports Macintosh paths
    
     'File usage   : Provide full file path and extension
    
     'Folder usage : Provide full folder path
    
     '               Accepts with/without trailing "\" (Windows)
    
     '               Accepts with/without trailing ":" (Macintosh)
    
    
    
    Dim iTemp As Integer
    
    
    
     'Ignore errors to allow for error evaluation
    
    On Error Resume Next
    
    iTemp = GetAttr(PathName)
    
    
    
     'Check if error exists and set response appropriately
    
    Select Case Err.Number
    
    Case Is = 0
    
        FileOrDirExists = True
    
    Case Else
    
        FileOrDirExists = False
    
    End Select
    
    
    
     'Resume error checking
    
    On Error Goto 0
    

    End Function

    Sub TestItWithWindows()

     'Macro Purpose: To test the FileOrDirExists function with Windows
    
     'Only included to demonstrate the function.  NOT required for normal use!
    
    
    
    Dim sPath As String
    
    
    
     'Change your directory here
    
    sPath = "C:\Test.xls"
    
    
    
     'Test if directory or file exists
    
    If FileOrDirExists(sPath) Then
    
        MsgBox sPath & " exists!"
    
    Else
    
        MsgBox sPath & " does not exist."
    
    End If
    

    End Sub

    Sub TestItWithMacintosh()

     'Macro Purpose: To test the FileOrDirExists function with a Macintosh
    
     'Only included to demonstrate the function.  NOT required for normal use!
    
    
    
    Dim sPath As String
    
    
    
     'Change your directory here
    
    sPath = "HardDriveName:Documents:Test.doc"
    
    
    
     'Test if directory or file exists
    
    If FileOrDirExists(sPath) Then
    
        MsgBox sPath & " exists!"
    
    Else
    
        MsgBox sPath & " does not exist."
    
    End If
    

    End Sub

  2. 2

    Open Excel and press "Alt-F11" to enter the Visual Basic Editor.

  3. 3

    Click "Insert" and then click "Module."

  4. 4

    Paste the code into the right-hand pane by pressing "Ctrl-"V."

  5. 5

    Change "text.xls" to the file name you are searching for.

  6. 6

    Press "F5" to run the procedure. The procedure will return a pop-up window telling you whether the file exists.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.