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

The VBA MkDir function can create a new directory or folder to a given drive location in your system. It is a convenient way to make necessary directories while working in Excel to save files into them.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

VBA MkDir Function.xlsm

Introduction to the VBA MkDir Function

Objective: To create a new folder or directory to a specified path.

Syntax: MkDir path

Argument: The MkDir function takes only one argument.

Path – It is a string expression that defines the drive location along with the directory name to create a new directory. If no drive location is specified, it creates the new folder in the current drive where the workbook is saved.

Output: It doesn’t return any value.

6 Useful Examples of Using VBA MkDir Function in Excel

In this article, how to create a directory using the VBA MkDir function in 6 different ways. But first, we’ll discuss how to write code in the Visual Basic Editor in the first place. 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.

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

Now, write your code here inside this method.

1. Create New Directory Using the VBA MkDir Function

By using the MkDir function, we can create a folder or directory to the desired drive location. In this example, we set the path as D:\Exceldemy, which is currently empty.

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

To make a new directory named “NewFolder”, write the following code in the visual code editor and press F5 to run it.

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

A success message has appeared as we set in the code.

Now, open windows explorer again to check whether the new directory is created or not.

1.1 Set the Path Argument in a Variable

We can also put the path argument in a variable and then use it in the VBA MkDir function. At first, we need to declare a variable as a string. In this example, we declared a string variable named Path to put the drive location. Here is the code to make 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

In case 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.

2. Use If Condition with the VBA MkDir Function to Check and Create a New Folder

Using the IF…Then…Else statement, we can check whether a directory exists or not. If the directory location is valid it’ll create a new folder.

2.1 Folder Has Been Created

Put 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.

Now we have two folders in the Exceldemy directory.

2.2 Folder Already Exists

In this stage, let’s try to create the NewFolder2 again in the Exceldemy directory. Let’s 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

As we set in the code, Excel returns a message “Folder already exists” in a MsgBox.

3. Use of the VBA MkDir Function to Create a New Directory in the Same Drive

What happens if we forget or intentionally left the path argument without any drive location. To see what happens let’s run the following code.

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.

Similar Readings:

4. Create Multiple Directories Using the VBA MkDir Function within a For Loop

Let’s think of a scenario. We want to make a folder named Month and then create folders for each of the months from Jan to Dec. We’ll use the For…Next statements to accomplish this. Let’s follow the steps below to make this happen!

Steps:

In the worksheet, in cells B2:B13 write down the name of 12 months e., Jan-Dec.
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

As a result, 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.

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.

Conclusion

Now, we know how to use the VBA MkDir function in Excel. Hopefully, it would encourage you to use this function more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Get FREE Advanced Excel Exercises with Solutions!