How To Create A Folder And Sub Folder In Excel VBA

In this article we will learn how to create folders and subfolder using Excel VBA.There can be many different ways to write the macros to create folder but we 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:tototesttest") 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
    CreateFolder = True
End Function



We are using the macro createfolder_subfolder which uses the function:


Function CreateFolder(ByVal sPath As String) As Boolean


Under this macro we have the pass the argument which is the folder path of new folder:


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


vba create folder function


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


created folder


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


path1 = "C:documentsnew folder created"


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


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


new subfolder created


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



You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell