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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 3 4 5 6 7 8 |
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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 |
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 correctly returned TRUE and FALSE values whether the file exists or not.