How to Use VBA MkDir Function in Excel (4 Examples)

We’ll first discuss how to write code in the Visual Basic Editor. Follow the simple steps to create the setup for writing your code.

Steps:

• Go to the Developer Tab in the Excel Ribbon and click the Visual Basic Tab.

• From the Visual Basic Editor, choose the Module option from the Insert tab to add a new module.

• Enter your code in this module.

Method 1 – Creating a New Directory Using the VBA MkDir Function

Note: To follow along with the examples of this article, make a folder named Exeldemy in D drive. You can also use your preferred folder location. In this case, you need to change the path accordingly.

Steps:

• To make a new directory named “NewFolder”, enter the following code in the visual code editor and press F5:

Sub CreateNewFolder()
    MkDir "D:\Exceldemy\NewFolder"
    MsgBox "Folder has created: " & vbCrLf & Path, vbInformation
End Sub

• A success message appears.

• Open Windows Explorer to check whether the new directory has been created.

1.1 Set the Path Argument in a Variable

We can also store the path argument in a variable and use it in the VBA MkDir function. First, we need to declare a variable as a string. In this example, we declared a string variable named Path to store the drive location. Here is the code to create a new directory called NewFolder, just like the previous example.

Sub CreateNewFolder()
 Dim Path As String
    Path = "D:\softeko\Exceldemy\NewFolder"
    MkDir Path
    MsgBox "Folder has created: " & vbCrLf & Path, vbInformation
End Sub

1.2 Error: Path Doesn’t Exist

If we set a wrong drive location that doesn’t exist, Excel will warn us with an error message. Let’s put the code in the visual code editor to see what happens.

Sub CreateNewFolder()
 Dim Path As String
    Path = "D:\softeko\Exceldemy\Blog\NewFolder"
    MkDir Path
    MsgBox "Folder has created: " & vbCrLf & Path, vbInformation
End Sub

An error message appeared as there is no folder named Blog in the Exceldemy folder.

Read More: How to Use VBA DIR Function in Excel (7 Examples)

Method 2 – Using the If Condition with the VBA MkDir Function to Check and Create a New Folder

2.1 Folder Has Been Created

• Use the following code to create a new folder named “NewFolder2” in the Exceldemy directory. As the directory D: Exceldemy is a valid path, the code will create the new folder and return a success message.

Sub CreateNewFolder()
 Dim Path As String
    Path = "D:\Exceldemy\NewFolder2"
    If Len(Dir(Path, vbDirectory)) = 0 Then
    MkDir Path
    MsgBox "Folder has created : " & vbCrLf & Path, vbInformation
    Else
    MsgBox "Folder already Exists"
    End If
End Sub

• Run the code by pressing F5.

We have two folders in the Exceldemy directory.

2.2 Folder Already Exists

To create the NewFolder2 again in the Exceldemy directory, run the following code in the visual code editor:

Sub CreateNewFolder()
 Dim Path As String
    Path = "D:\Exceldemy\NewFolder2"
    If Len(Dir(Path, vbDirectory)) = 0 Then
    MkDir Path
    MsgBox "Folder has created : " & vbCrLf & Path, vbInformation
    Else
    MsgBox "Folder already Exists"
    End If
End Sub

• Excel returns a message “Folder already exists” in a MsgBox.

Read More: How to Use VBA Case Statement (13 Examples)

Method 3 –Using the VBA MkDir Function to Create a New Directory in the Same Drive

Sub CreateNewFolder()
 Dim Path As String
    Path = "NewFolder3"
    If Len(Dir(Path, vbDirectory)) = 0 Then
    MkDir Path
    MsgBox "Folder has created : " & vbCrLf & Path, vbInformation
    Else
    MsgBox "Folder already Exists"
    End If
End Sub

We found that a new folder named Folder3 has been created in the Documents folder of the C drive. The location can be different in your system.

Read More: How to Use VBA ChDir Function in Excel (4 Suitable Examples)

Method 4 – Creating Multiple Directories Using the VBA MkDir Function within a For Loop

Steps:

• Enter the name of 12 months i.e., Jan-Dec. in cells B2:B13.
• Copy the code below and run it in the visual code editor.

Sub CreateNewFolder()
MkDir "D:\Exceldemy\Month"
Dim i As Integer
For i = 2 To 13
MkDir "D:\Exceldemy\Month\" & Range("B" & i)
Next i
MsgBox "Folders are created "
End Sub

How the Code Works

• 2nd line: MkDir “D:\Exceldemy\Month” first created the folder Month in the Exceldemy directory.
• 3rd line: We defined a variable i as
• 4th line: We initiated a counter from 2 to 13 using the For Loop.
• 5th line: We used the MkDir function and used the values of cells B2:B13 using Range (“B” & i) function where B is the column name and i represents the row number.
• 6th line: We forwarded the loop using the Next statement.
• 7th line: Used the MsgBox to show a success

• We see a new folder named Month and 12 folders that are named according to the cell values of B2:B13 inside the Month folder.

Read More: How to Use VBA While Wend Statement in Excel (4 Examples)

Notes: In the above code, we used two functions while showing the success message.

vbCrLf- it shows a carriage-return character in combination with a linefeed character.

vbInformation– it displays an information icon when the MsgBox function is called. We also used vbDirectory field as an argument of the Len function.

Download the Practice Workbook

Download this workbook to practice.

Related Articles

• How to Use VBA RTrim Function (5 Suitable Examples)
• Use VBA Str Function in Excel (4 Examples)
• Use VBA DateDiff Function in Excel (9 Examples)
• How to Use VBA IsError Function (6 Examples)