Excel VBA Tool to Create Folders

Excel VBA Tool 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

Excel Tricks

'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 Sub


How to use this tool:

1. Save the following attachment in your system Folder Creator Tool
2. Unzip the file and open
3. Enter the Base Folder Path and New Folder Name

Excel Tricks

4. Click on ‘Create Folders’ shape

Excel Tricks

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’

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.
Comments
  • Nice Article….Keep sharing such articles. It adds worth to our knowledge 🙂

    • It works well.
      Thank you for the post

      • excelsirji says:

        Thank you for visiting our website 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs

Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business