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:

Calling a function in Excel VBA Regular Expression in Excel Vba How to Use VLOOKUP with VBA