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.
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 Sub1.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 SubAn 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)
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 SubRun 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 SubAs we set in the code, Excel returns a message “Folder already exists” in a MsgBox.
Read More: How to Use VBA Case Statement (13 Examples)
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 SubWe 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)
Similar Readings:
- Excel VBA ASC() Function – Get ASCII Value of Character
- How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
- Use TRIM Function in VBA in Excel (Definition + VBA Code)
- How to Use VBA SPLIT Function in Excel (5 Examples)
- How to Use LCase Function in VBA in Excel (With 4 Examples)
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 SubHow 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.
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.
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.
