Make Folders with Sub Folders Using Excel Worksheet

[Ame270]

Hey,

I am trying to make folders for 100's of clients on my C drive with subfolders in each client's folder.

Column A would be Client Folder
Column B would be Subfolder to Client such as "worksheet"
Column C would be a subfolder to "worksheet"

A
B
C

Etc.

Thank you for your help!

 

[dmt32]

Hi,
you can give this a try & see if does what you want:

Place ALL code in a standard module:

Sub CreateFolders()
    Dim rng As Range, Item As Range
    Dim ws As Worksheet
    Dim Drive As String, SheetName As String
    Dim StartRow As Long
    
    '********************************************************************
    '*****************************SETTINGS*******************************
    Drive = "C:\"
    
    SheetName = "Sheet1"
    
    StartRow = 2
    
    '********************************************************************
    
    Set ws = ThisWorkbook.Worksheets(SheetName)
    
    Set rng = ws.Range(ws.Range("A" & StartRow), ws.Range("A" & ws.Rows.Count).End(xlUp))
    
    For Each Item In rng
        MakeDirectory FolderPath:=Drive & _
        Folders(Target:=ws.Cells(Item.Row, 1).Resize(1, ws.Cells(Item.Row, ws.Columns.Count).End(xlToLeft).Column))
    Next


End Sub


Sub MakeDirectory(ByVal FolderPath As String)
    Dim SubFolders As Variant
    Dim MakeFolder As String
    
    SubFolders = Split(FolderPath, "\")
    
    For i = 0 To UBound(SubFolders)
        Select Case i
        Case 0
            MakeFolder = SubFolders(i) & "\"
        Case 1
            MakeFolder = MakeFolder & SubFolders(i)
        Case Else
            MakeFolder = MakeFolder & "\" & SubFolders(i)
        End Select
        
        If Dir(MakeFolder, vbDirectory) = vbNullString Then MkDir MakeFolder
    Next
    
End Sub


Function Folders(ByVal Target As Range, Optional ByVal PathSeparator As String = "\") As String
    Folders = Join(Application.Transpose(Application.Transpose(Target)), PathSeparator)
End Function

You will need to update the variable values in RED shown in SETTINGS as required.

I have made code dynamic to allow for possibility of a different number of folders for each Client but note that this is a VBA solution & may prove tad slow if your list is very large as each folder has to be created one at a time.

I have also assumed that in you have in each column, just the folder name e.g Folder1 Folder2 etc without any path separators

There are cleaner / quicker methods such as using API but I am very rusty in this area & perhaps another on this board may offer such a solution.

Hope Helpful

Dave