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.

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.

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.

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 at 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 cells from A1 to A12 and run the following code.

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.

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 correctly returned TRUE and FALSE values whether the file exists or not.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.