How to Check if a File Exists Using VBA

1. Use the Dir() Function to Check if the File Exists on the Computer Using VBA
2. Introducing Wildcards in the Dir() Function to Check if a File/S Exist in Your Computer Using VBA
3. Get/Count All the Names That Exist in the Computer Using VBA

VBA is a powerful computer programming language, especially when dealing with desktop software development. One of the prowess of VBA is its ability to communicate and manipulate files on the computer.

This tutorial will demonstrate how to check if a certain file exists in a certain path. It is a typical must-do logic, especially when dealing with file access or manipulation.

It is good practice to check whether a file exists first before going further. It is to prevent system errors from happening, which will cause the entire program to crash.

Use the Dir() Function to Check if the File Exists on the Computer Using VBA

The code block below demonstrates how to check if a file exists using the Dir() function. The Dir() function is a powerful tool to get folder names and file names by using their file paths. The code block below contains a subroutine with a fileToCheck parameter. You can call the CheckFileExistence subroutine with fileToCheck as its parameter. The fileToCheck parameter will be the file name or the folder name you want to check for existence.

Syntax:

Dir([pathname],[attributes])

Parameters:

For [attributes], please see the below definitions.

Sub CheckFileExistence(fileToCheck as String)
Dim FileName As String
FileName = Dir(fileToCheck,vbNormal)

If FileName <> "" Then
    MsgBox "File Exists"
Else
    MsgBox "File Doesn't Exist"
End If
End Sub

Sub test1()

Call CheckFileExistence("Book1.xlsx")

test1 Output:

File Exists

Note that the file Book1.xlsx may be in any folder on your computer. The good thing about the Dir() function is that it checks the file in any possible location on all folders on your computer.

Thus, if the result is File Doesn't Exist, the file name was not used in any folder or file on the computer.

Introducing Wildcards in the Dir() Function to Check if a File/S Exist in Your Computer Using VBA

Additionally, the Dir() function allows wildcards on its arguments. These wildcards can be used to search files if you are not certain of the exact file name, folder name, and file path you need.

The list below were the wildcards accepted by the Dir() function:

Examples:

This vba code block will check if there is a file of the xlsx file type. Thus, we use *.xlsx search file name.

Sub CheckFileExistence(fileToCheck as String)
Dim FileName As String
FileName = Dir(fileToCheck,vbNormal)

If FileName <> "" Then
    MsgBox "File Exists"
Else
    MsgBox "File Doesn't Exist"
End If
End Sub

Sub test2()

Call CheckFileExistence("*.xlsx")

End Sub

test2 Output:

File Exists

The vba code below will check if there is a file which file name is only 2 characters long in any file type. Thus, we use ??.* to search file names.

Sub CheckFileExistence(fileToCheck as String)
Dim FileName As String
FileName = Dir(fileToCheck,vbNormal)

If FileName <> "" Then
    MsgBox "File Exists"
Else
    MsgBox "File Doesn't Exist"
End If
End Sub

Sub test3()

Call CheckFileExistence("??.*")

End Sub

test3 Output:

File Exists

Get/Count All the Names That Exist in the Computer Using VBA

The code blocks above are just detecting the file’s existence without getting any information regarding those files. In the code block below, we will list all the existing files matched with our search name string.

For the code block below, the output will be the file name of all .xlsx files which the file name is 1 to 4 letters long (????.xlsx).

Sub ListAllFiles(fileToCheck As String)

Dim FileName As String

FileName = Dir(fileToCheck, vbNormal)

Do While FileName <> ""

    Debug.Print FileName
    FileName = Dir()
Loop


End Sub

Sub test4()

Call ListAllFiles("????.xlsx")

End Sub

test4 output:

ATIC.xlsx
CS.xlsx
Data.xlsx
EA65.xlsx
edge.xlsx
fe10.xlsx
FV3P.xlsx
G!.xlsx
GZ7P.xlsx
HE6P.xlsx
IF.xlsx
IF27.xlsx
Lot.xlsx
SR.xlsx
Test.xlsx
WOP.xlsx

Lastly, the code block below will count all the existing files matched with our search name string.

For the code block below, the output will be the number of all .xlsx files in which the file name is 1 to 4 letters long (????.xlsx).

Sub CountAllFiles(fileToCheck As String)

Dim FileName As String
Dim fileCnt As Long

FileName = Dir(fileToCheck, vbNormal)

Do While FileName <> ""

    fileCnt = fileCnt + 1
    FileName = Dir()
Loop

Debug.Print "There are " & fileCnt & " existing files matched with the criteria."

End Sub

Sub test5()

Call CountAllFiles("????.xlsx")

End Sub

test5 Output:

There are 16 existing files matched with the criteria.