Check Folder Existence with VBA Macros in Excel: A Step-by-Step Guide

← PrevNext →

You can use the FolderExists() method in VBA Excel to determine if a specific folder exists on your computer. This method, which belongs to the FileSystemObject class, returns a Boolean value: True if the folder exists and False if it does not.

Syntax of FolderExists() Method

object.FolderExists(folder_path)

It’s from the FileSystemObject class, therefore, you have to use the method with FSO object (FSO stands for FileSystemObject).

The method takes a parameter in the form of folder specification, like the name of the folder and the complete path where it exits. For example,

objFso.FolderExists("D:/myfolder")

To use the method, you’ll have to first create an FSO object in your VBA application. There are two simple methods, which you can apply to create an FSO object in Excel VBA.

See both methods here.

In the first method, you can add a Microsoft Scripting Library Reference to your VBA macro and second, by using the CreateObject method. I am using the second method in my example here.

Option Explicit

Dim sFolder As String

Sub checkFolderExists()
    Dim objFso
    Set objFso = CreateObject("Scripting.FileSystemObject")
    
    sFolder = "D:/myfolder"
    
    If objFso.FolderExists(sFolder) Then        ' Check if the folder exists.
        MsgBox "Yes, it exist"
    Else
        MsgBox "No, the folder does not exist"
    End If
End Sub

Simple to use and its just a one-line code.

objFso.FolderExists(sFolder)

You can perform any number of tasks if the condition returns true. For example, we can get all the files in the folder and show it in our Excel worksheet.

Option Explicit

Dim sFolder As String

Sub checkFolderExists()
        
    Dim objFso
    Set objFso = CreateObject("Scripting.FileSystemObject")
    
    sFolder = "D:/myfolder"
    
    If objFso.FolderExists(sFolder) Then        ' Check if the folder exists.
        
        Dim objFolder As folder
        Set objFolder = objFso.GetFolder(sFolder)
        
        If objFolder.Files.Count > 0 Then         ' See if the folder has files.
            Dim file As file
            Dim iRow, iCol
            
            iRow = 1
            iCol = 1
            
            For Each file In objFolder.Files       ' Get all the files.
                Cells(iRow, iCol) = file.Name
                Cells(iRow, iCol + 1) = file.Size
                
                iRow = iRow + 1
            Next file
        End If
    Else
        MsgBox "folder does not exists"
    End If
End Sub

In this example, I check if a specified folder exists. If it does, I extract all the files from the folder and write their names and sizes into my Excel worksheet. The macro also utilizes another crucial method from the FileSystemObject class, the GetFolder() method.


← PreviousNext →