In this Excel VBA tutorial lesson, you learn how to create folders and subfolders using Excel VBA.

Using the CreateFolder function

The CreateFolder function is a built-in function that can be used to create a folder. The syntax for the CreateFolder function is as follows:

Sub createfolder_subfolder()
path1 = ThisWorkbook.path & "\" & "new folder created"
CreateFolder (path1) 
End Sub

Function CreateFolder(ByVal sPath As String) As Boolean

  'create full sPath at once, if required
  'returns False if folder does not exist and could NOT be created, True otherwise
  'sample usage: If CreateFolder("C:\toto\test\test") Then debug.print "OK"

    Dim fs As Object
    Dim FolderArray
    Dim Folder As String, i As Integer, sShare As String

    If Right(sPath, 1) = "\" Then sPath = Left(sPath, Len(sPath) - 1)
    Set fs = CreateObject("Scripting.FileSystemObject")
    'UNC path ? change 3 "\" into 3 "@"
    If sPath Like "\\*\*" Then
        sPath = Replace(sPath, "\", "@", 1, 3)
    End If
    'now split
    FolderArray = Split(sPath, "\")
    'then set back the @ into \ in item 0 of array
    FolderArray(0) = Replace(FolderArray(0), "@", "\", 1, 3)
    On Error GoTo hell
    'start from root to end, creating what needs to be
    For i = 0 To UBound(FolderArray) Step 1
        Folder = Folder & FolderArray(i) & "\"
        If Not fs.FolderExists(Folder) Then
            fs.CreateFolder (Folder)
        End If
    Next
    CreateFolder = True
hell:
End Function

path is the path to the folder that you want to create.

Using the macro createfolder_subfolder

The macro createfolder_subfolder is a macro that can be used to create a folder and a subfolder. The code for the macro is as follows:

Function CreateFolder(ByVal sPath As String) As Boolean

Under this macro, you have to pass the argument which is the folder path of the new folder:

path1 = ThisWorkbook.path & "\" & "new folder created"

You are creating a folder named “new folder created” in the same location as this file is kept.

But you can create the same in any other location by giving the complete path in address:

path1 = "C:\documents\new folder created"

Now, in order to create a subfolder, you can use the path as follows:

path1 = ThisWorkbook.path & "\" & "new folder created" & "\" & "new subfolder created"

As you can see in the picture above, the new directory and subdirectory have been created.

Automating Folder Creation for File Storage

Imagine you’re working on a project that generates multiple reports, and you want to save each report in a folder named after the project and subfolders based on dates or categories. Here’s how you can automate this using Excel VBA:

Sub CreateProjectFolders()
    Dim mainPath As String
    Dim projectFolder As String
    Dim subfolder As String
    
    mainPath = "C:\Projects"
    
    projectFolder = mainPath & "\" & Range("A1").Value
    
    If Dir(projectFolder, vbDirectory) = "" Then
        MkDir projectFolder
    End If
    
    subfolder = projectFolder & "\" & "Reports"
    If Dir(subfolder, vbDirectory) = "" Then
        MkDir subfolder
    End If
    
End Sub

In this example, you define the main project folder and subfolders based on your project’s requirements. You can change the project name dynamically, create subfolders as needed, and easily save reports in the correct location.

Handling Errors and Exceptions

When working with file and folder creation, it’s essential to anticipate and handle potential errors. Here’s an updated version of your CreateFolder function with improved error handling:

Function CreateFolder(ByVal sPath As String) As Boolean
    Dim fs As Object
    Dim FolderArray
    Dim Folder As String, i As Integer
    
    On Error Resume Next
    Set fs = CreateObject("Scripting.FileSystemObject")
    On Error GoTo 0
    
    If fs Is Nothing Then
        CreateFolder = False
        Exit Function
    End If
    
    If Right(sPath, 1) = "\" Then sPath = Left(sPath, Len(sPath) - 1)
    FolderArray = Split(sPath, "\")
    
    For i = 0 To UBound(FolderArray) Step 1
        Folder = Folder & FolderArray(i) & "\"
        If Not fs.FolderExists(Folder) Then
            On Error Resume Next
            fs.CreateFolder (Folder)
            On Error GoTo 0
        End If
    Next
    
    CreateFolder = True
End Function

In this version, we’ve added error handling to gracefully handle situations where the FileSystemObject cannot be created or if there are issues creating folders. This ensures that your VBA code doesn’t break unexpectedly and can continue running with proper error notifications.

Whether it’s automating file storage or implementing robust error handling, VBA can be a powerful tool for enhancing your Excel applications.

Related posts:

How to Record a Macro in Excel How to Use an Option Button in Excel How to Add Hours to Time in Excel Vba