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.