Excel VBA Tool to Create Folders
VBA Tools To Create Folders
In VBA, you can use MkDir function to create folders in your system or shared drive. To make your work simple, we bring a free Excel VBA tool to create folders. To use this tool, enter Base folder path where you want to create folders and the name of the folder. Click on ‘Create Folders’ and done, folders will be created in matter of seconds
VBA Tools To Create Folders Code:-
'This function reads data from Excel sheet
'and create folders in the system or shared drive
Sub CreateFolders()
'Variable declaration
Dim lCounter As Long
Dim strFolderPath As String
'Clear Status column
Sheet1.Range("E4:E1000").ClearContents
Sheet1.Range("E4:E1000").Font.Color = vbBlack
Sheet1.Range("E4:E1000").Interior.Color = vbWhite
'Loop through all the records in sheet1
For lCounter = 4 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
'Validation - if column C or D has no value go to next record
If Sheet1.Range("C" & lCounter).Value <> "" And Sheet1.Range("D" & lCounter).Value <> "" Then
strFolderPath = Sheet1.Range("C" & lCounter).Value
'If the path is ending with slash then remove
If InStr(1, Right(strFolderPath, 1), "/") > 0 Or InStr(1, Right(strFolderPath, 1), "\") > 0 Then
strFolderPath = Left(strFolderPath, Len(strFolderPath) - 1)
End If
'Check if the base folder path is valid
If Dir(strFolderPath, vbDirectory) <> "" Then
'Check if new folder already exist in the base folder
If Dir(strFolderPath & "\" & Sheet1.Range("D" & lCounter).Value, vbDirectory) = "" Then
'Create new folder
MkDir (strFolderPath & "\" & Sheet1.Range("D" & lCounter).Value)
'Update the status
Sheet1.Range("E" & lCounter).Value = "Folder Created"
Sheet1.Range("E" & lCounter).Interior.Color = vbGreen
Else
Sheet1.Range("E" & lCounter).Value = "Folder Already Exist"
Sheet1.Range("E" & lCounter).Font.Color = vbRed
End If
Else
Sheet1.Range("E" & lCounter).Value = "Invalid Base Folder Path"
Sheet1.Range("E" & lCounter).Font.Color = vbRed
End If
Else
Sheet1.Range("E" & lCounter).Value = "Incomplete Details"
Sheet1.Range("E" & lCounter).Font.Color = vbRed
End If
Next
MsgBox "Done", vbInformation
End SubHow to use this tool:
4. Click on ‘Create Folders’ shape
5. Done, your folders will be created and Status column will be updated as ‘Folder Created’
Note: You may get following Status comments as well
(i) Folder Already Exist – If the folder already available at destination
(ii) Invalid Base Folder Path – If the Base Folder Path is invalid
(iii) Incomplete Details – If Base Folder Path or New Folder Name are left blank
6. To clear the data, click on ‘Clear Data’
Nice Article….Keep sharing such articles. It adds worth to our knowledge 🙂
It works well.
Thank you for the post
Thank you for visiting our website 🙂
First, this is AWESOME! Second, is there a way to modify the VBA code for the status column, with a type of IF Statement? I get “Incomplete Details” for all rows that do not contain a folder name. Could the statement indicate if Base Folder Path AND Folder Name are blank, then Status = “”? Also, my company has high security rules, so your download page was blocked. Could you post the VBA code for the Clear Data macro?