How to check if a Folder exists using a VBA Macro in Excel

excel vba

← PrevNext →

Tools you can use

You can use the FolderExists() method in VBA Excel to check if a particular folder exists in a computer or not. FolderExists returns a Boolean true or false and it’s from the FileSystemObject class in VBA.

Syntax of FolderExists() Method

object.FolderExists(folder_path)

It’s from the FileSystemObject class, therefore, you have to use the method with FSO (FSO stands for FileSystemObject) object. 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. 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

Here, I am checking if a given folder exists or not and if yes, I am extracting all the files from the folder and writing the name and size of each file in my Excel worksheet. In the above macro, I have used another important method in the FileSystemObject class, that is, GetFolder() method.

Happy coding. ☺

← PreviousNext →