In this Excel VBA tutorial lesson, you will learn how to create folders and subfolders using Excel VBA.
VBA code
There can be many different ways to write the macros to create a folder, but you are choosing an easy one 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
The macro
You are using the macro createfolder_subfolder which uses the function:
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.