VBA check if file exists

Check if a file exists

In VBA, you can check whether the file or a directory exists by using the Dir method. Look at the following Subroutine.

Sub fileOrDirectoryExists()
    Dim full_path As String
    full_path = "C:\Excel\1.png"
    MsgBox Dir(full_path) <> ""
End Sub

It will return the message True if the file exists.

But if you change

full_path = “C:\Excel\1.png”

to

full_path = “C:\Excel\ “

It will also return True. You can modify the following code to return true only for files.

Sub fileExists()
    Dim full_path As String

    full_path = "C:\Excel\1.png"

    If Dir(full_path) <> "" Then
        If Right(full_path, 1) <> "\" Then
            MsgBox True
        Else
            MsgBox False
        End If
    Else
        MsgBox False
    End If
End Sub

The code If Right(full_path, 1) <> “\” Then will be executed only if the first character from the right is “\”.

Check if a file exists with FileSystemObject

There is another better way to check whether the file exists. This time we are going to use FileSystemObject. It provides an easy way to access a computer’s file system.

The following code will work in a similar way as the last one.

Sub fileExistsFSO()
    Dim fso_obj As Object
    Dim full_path As String

    full_path = "C:\Excel\1.png"
    Set fso_obj = CreateObject("Scripting.FileSystemObject")
    MsgBox fso_obj.fileExists(full_path)
End Sub

Check if files exist in a list

Let’s create a subroutine that will check a range of cells inside a worksheet and format. Cells will contain file paths to files.

But first, take a look which files actually exist.

As you can see there are files from 1 to 10, with files 4, 5, and 6 missing. Take a look at our worksheet.

Select all cell from A1 to A12 and run the following code.

Sub fileExistsFSORange()
    Dim Rng As Range
    Set fso_obj = CreateObject("Scripting.FileSystemObject")
    
    Set Rng = Selection
    
    For Each Cell In Rng
        If fso_obj.fileExists(Cell) Then
            Cell.Font.Color = vbGreen
        Else
            Cell.Font.Color = vbRed
        End If
    Next Cell
End Sub

All files that exist are green and the other files are read.

Check if the file exists function

So far we used subroutines. Let’s create a function that can be executed from the worksheet. Create a new module inside the current project and insert the following function.

Function FileExists(full_path As String)
    Dim fso_obj As Object
    
    Set fso_obj = CreateObject("Scripting.FileSystemObject")
    FileExists = fso_obj.FileExists(full_path)
End Function

Now, if you start typing you will see a new function called FileExists.

Create the function for all the data on the worksheet.

As you can see it correct returned TRUE and FALSE values whether the file exists or not.

Posted in vba