VBA MkDir statement is used to create a new folder or directory in your file system. This is a useful tool when working with large amounts of data or when automating repetitive tasks in Excel. It allows you to create a directory structure and organize your files in a systematic way.
Purpose of VBA MkDir Statement
The MkDir statement can be particularly helpful in situations where you need to create a new folder for each date or month, or for each category or subcategory of data. This not only helps with organization, but it also makes it easier to retrieve and work with specific datasets.
Syntax of the VBA MkDir Statement
The syntax for the MkDir statement is as follows:
MkDir path
The ‘path’ in the above syntax represents the path of the new folder or directory that you want to create. This can be an absolute path (starting from the root directory) or a relative path (relative to the current directory). The path can also include the name of the new folder, for example, “C:\Users\John\Documents\New Folder”
Examples of Using the VBA MkDir Statement
Example 1: Creating a New Folder in the Current Directory
Let’s say you want to create a new folder called “New Folder” in the current directory of your Excel workbook. The following code can be used to achieve this:
Sub createNewFolder()
MkDir "New Folder"
End Sub
Note that this will create the new folder in the same location as your Excel workbook.
Example 2: Creating a New Folder in a Specific Directory
Suppose you have a macro that imports data from an external source into an Excel worksheet, and you want to save the file in a specific folder. You can use the MkDir statement to create a new folder in that directory, as shown in the code below:
Sub importData()
Dim importPath As String
Dim folderName As String
'set import path
importPath = "C:\Users\John\Documents\Imported Data\"
'set folder name
folderName = "May 2021"
'create new folder
MkDir importPath & folderName
'import data and save in created folder
'...
End Sub
In this example, the ‘importPath’ variable is set to the parent directory, and the ‘folderName’ variable is set to the name of the new folder that we want to create. The MkDir statement then combines the two variables to create the full path for the new folder.
Example 3: Creating a New Folder Based on a Cell Value
Instead of hard-coding the folder name in the code, you can also use a cell value to dynamically create the name of the new folder. For example, you might have a cell in your worksheet that contains the current month, and you want to create a new folder with that month’s name. The following code can help you achieve this:
Sub createNewFolder()
Dim folderName As String
'get current month from cell A1
folderName = Range("A1")
'create new folder
MkDir folderName
End Sub
This code will create a new folder with the name found in cell A1.
Example 4: Creating Multiple Subfolders at Once
The MkDir statement can also be used to create multiple subfolders at once within a specific directory. This can be achieved using a loop to iterate through a list of folder names, as shown in the code below:
Sub createSubfolders()
Dim importPath As String
Dim folderName As Variant
'set import path
importPath = "C:\Users\John\Documents\Imported Data\"
'list of folder names
folderNames = Array("May 2021", "June 2021", "July 2021")
'loop through array and create new folders
For Each folderName In folderNames
MkDir importPath & folderName
Next folderName
End Sub
This code will create three new subfolders (May 2021, June 2021, and July 2021) within the ‘Imported Data’ folder.
Example 5: Check If a Folder Already Exists Before Creating
It is essential to note that if a folder with the same name already exists, the MkDir statement will throw an error. To avoid this, you can check if the folder exists before attempting to create it. The following code shows how this can be done:
Sub createNewFolder()
Dim folderPath As String
'set folder path
folderPath = "C:\Users\John\Documents\New Folder"
'check if folder already exists
If Dir(folderPath, vbDirectory) = "" Then
'create new folder
MkDir folderPath
Else
MsgBox "Folder already exists."
End If
End Sub
This code checks if the folder exists using the ‘Dir’ function. If it does not find a directory with the specified name, it will create a new folder. If the folder already exists, a message box will appear, informing the user.
Important Notes & Remarks
- The MkDir statement can only create one folder at a time. To create multiple folders, you will need to use a loop.
- If the folder path is not valid (e.g., contains invalid characters or exceed maximum allowed length), the MkDir statement will throw an error.
- The ‘path’ argument for the MkDir statement must be enclosed in double quotes.
- The ‘path’ argument cannot contain wildcard characters (*) or square brackets ([]).
- The MkDir statement will create the new folder with default permissions, which may vary depending on your operating system.
In Conclusion
In this blog post, we have explored the purpose, syntax, and top 5 examples of using the VBA MkDir statement. This is a handy tool that can help you efficiently organize and manage your files and automate tasks in Excel. By using the MkDir statement, you can create new folders in a specific directory, based on cell values, and even check if a folder already exists before creating it. This can save you time and effort, making your VBA code more efficient.
Have you used the VBA MkDir statement before? Do you have any additional tips or examples to share? We would love to hear your thoughts and feedback in the comments below.
