VBA to Check If Folder Exists

Sometimes, when working with VBA code, checking whether a folder exists might be necessary before you create, delete, or access files. Checking is essential to, for instance,  prevent runtime errors if the folder doesn’t exist.

In this tutorial, I will show you how to check if a folder exists in VBA using the ‘Dir’ function,  the ‘FolderExists’ method, and the ‘ChDir’ statement.

Using The ‘DIR’ function in VBA

The ‘Dir’ function in VBA retrieves a file or folder name matching a specified pattern or file attribute. It is commonly used for file and folder manipulation tasks in VBA. 

The basic syntax of the ‘Dir’ function is as follows:

Dir([pathname], [attributes])

The function has the following optional parameters:

Parameter	Description
pathname	String expression specifying a file name. It may include a folder and drive. If the pathname is not found, a zero-length (“”) string is returned. If this parameter is omitted, the ‘Dir’ function returns the first folder or file in the default folder.
attributes	Is a numeric expression or constant whose sum indicates file attributes. If the parameter is omitted, files that match the pathname without attributes are returned.

The settings for the ‘attributes’ parameter are as follows:

Constant	Value	Description
vbNormal	0	Is the default setting, specifying files with no attributes.
vbReadOnly	1	Indicates read-only files and files without attributes.
vbHidden	2	Specifies hidden files and files without attributes.
vbSystem	3	Indicates system files and files without attributes.
vbVolume	8	Indicates volume label. However, ‘vbVolume’ is ignored if any other attribute is specified.
vbDirectory	16	Indicates folders and files without attributes.

VBA to Check If Folder Exists Using the ‘Dir’ Function

Suppose you want to check whether the folder ‘Excel Tutorials’ exists in the root directory.

You can use the following VBA code to accomplish the task:

Sub CheckFolderExists()
Dim FolderPath As String
FolderPath = "C:\Excel Tutorials"
If Right(FolderPath, 1) <> "\" Then
    FolderPath = FolderPath & "\"
End If
If Dir(FolderPath, vbDirectory) <> vbNullString Then
    MsgBox "Folder exists.", vbInformation
Else
    MsgBox "Folder doesn't exist.", vbExclamation, "Error!"
End If
End Sub

Explanation of the Code

The VBA code checks if a folder exists at the specified path and displays a message box accordingly. It also ensures the folder path is correctly formatted by adding ‘\’ at the end if needed.

Create Folder If It Does Not Exist

You can use the following modified code if you desire the code to create the folder if it does not exist:

Sub CheckFolderExistsWithDirAndCreateIfNotExist()
Dim FolderPath As String
FolderPath = "C:\Excel Tutorials"
If Right(FolderPath, 1) <> "\" Then
    FolderPath = FolderPath & "\"
End If
    If Dir(FolderPath, vbDirectory) = "" Then
        MkDir FolderPath
        MsgBox "Folder has been created.", vbInformation
End If
End Sub

The modified code uses the ‘MkDir’ function to create the folder if it does not exist and displays a message box accordingly.

Also read: VBA Delete Files in Folder

Using The ‘FolderExists’ Method

The ‘FolderExists’ method is a VBA function that checks whether a specified folder exists.

It returns ‘True’ if the folder exists and ‘False’ if it does not. You can use this function with a ‘FileSystemObject’ that provides access to a computer’s file system to verify the existence of a folder before performing any operations on it.

The basic syntax of the ‘FolderExists’ method is as follows:

object.FolderExists (folderspec)

The object part is required and is always the name of a ‘FileSystemObject.’

The folderspec part is required and is the name of the folder whose existence is to be verified.

VBA to Check If Folder Exists Using the ‘FileSystemObject.FolderExists’ Method

Assuming you want to verify whether the folder ‘Electronic Sales’ exists in the root directory.

You can use the following VBA code to accomplish the task:

Sub CheckIfFolderExists()
Dim FSO As Object
Application.ScreenUpdating = False
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists("C:\Electronic Sales") Then
    MsgBox "Folder exists.", vbInformation
Else
    MsgBox "Folder doesn't exist.", vbExclamation, "Error!"
End If
Application.ScreenUpdating = True
End Sub

Explanation of the Code

The VBA code verifies the existence of a folder with the path ‘C:\Electronic Sales’ on the computer. The subroutine displays a message box to indicate whether the folder exists. 

The code temporarily turns off screen updating to optimize the code’s performance and prevent unnecessary screen refreshes during execution.

Create Folder If It Does Not Exist

You can use the following modified code if you want the code to create the folder if it does not exist:

Sub CreateFolderIfNotExist()
Dim FSO As Object
Application.ScreenUpdating = False
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists("C:\Electronic Sales") Then
    MsgBox "Folder exists.", vbInformation
Else

    FSO.CreateFolder ("C:\Electronic Sales")
    MsgBox "The folder has been created.", vbInformation
End If
Application.ScreenUpdating = True
End Sub

This modified code uses the ‘CreateFolder’ method of the ‘FileSystemObject’ to create the folder if it does not exist and displays a message box accordingly.

Also read: VBA to Loop Through Files in a Folder

Using the ‘ChDir’ Statement

The ‘ChDir’ statement changes the current folder or directory.

The basic syntax of the ‘ChDir’ statement is as follows:

ChDir path

The ‘ChDir’ command requires a path argument, a string expression identifying the directory or folder you want to set as the new default.

The path may include the drive letter. If you don’t specify a drive, ‘ChDir’ will change the default directory or folder on the current drive.

The ‘ChDir’ statement changes the default folder but not the drive.

VBA to Check If Folder Exists Using the ‘ChDir’ Statement

Suppose you want to verify whether the folder ‘Regional Sales’ exists in the root directory.

You can use the following VBA code to accomplish the task:

Sub CheckFolderExistsUsingChDir()
Dim strFolderName As String
strFolderName = "C:\Regional Sales\"
On Error Resume Next
ChDir strFolderName
If Err.Number <> 0 Then
    MsgBox "The selected folder doesn't exist!", vbExclamation, "Error!"
Else
    MsgBox "The selected folder exists!", vbInformation
End If
On Error GoTo 0
End Sub

Explanation of the Code

The VBA code utilizes an error-triggering mechanism that detects if there’s an attempt to change the current directory to a non-existent folder.

This method serves as a check for the folder’s existence.

Also read: Open Folder and Select File Using VBA

Benefits of VBA Check If Folder Exists

The following are some reasons why you might want to check if a folder exists in VBA:

1. The VBA check helps avoid runtime errors if the target folder does not exist.
2. Suppose your VBA code is meant to perform operations within a specific folder. In that case, checking for its existence is essential to prevent overwriting or deleting files in the wrong location and causing potential data loss.
3. You might want to execute different instructions depending on whether a folder exists. Checking the existence of a folder allows you to incorporate conditional logic into your VBA code.
4. In automated processes like batch file processing or data imports, it is essential to check the existence of folders. This way, you can ensure that the required folders are in place before the automated tasks begin.

In this tutorial, I showed you three ways of checking whether a folder exists in VBA. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• How to Save Workbook Using VBA
• Create New Workbook Using VBA in Excel
• VBA to Check If Sheet Exists
• Rename Files Using VBA
• Move Files Using VBA