Creating multiple child folders under a parent folder using VBA

[coetzej9]

Hi all,

I would like to create folders from a list. Each item in Col A needs to be a parent folder and Col B and Col C need to be the child folders for each.

Example:

Excel 2010

	A​	B​	C​
1​	France	Paris	Nice
2​	UK	London	Manchester
3​	Italy	Milan	Naples

[Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]


I tried to copy and past code from post http://www.mrexcel.com/forum/excel-questions/872763-create-folder-subfolders-cell.html but get Run-time error 424.

The code I used:

Sub CreateFolderStructure()
For Each objRow In UsedRange.Rows
        strFolders = "C:\The_NEWS\"
        For Each ObjCell In objRow.Cells
            strFolders = strFolders & "\" & ObjCell
        Next
        Shell ("cmd /c md " & Chr(34) & strFolders & Chr(34))
    Next
End Sub

......I would like to get the following results:

C:\The_NEWS\France\Paris\Nice\Marseille\Lyon\Nantes
C:\The_NEWS\Desktop\Italy\Milan\Naples\Rome\Turin\Genoa
C:\The_NEWS\UK\London\Manchester\Liverpool\Oxford\Preston

I have not been able to find a post addressing the copy and past error that return Run-time error 424 - Object Required.

Since I am new at VBA I am not sure if I did something wrong. I created a new module and pasted the code, changed the folder location in the code and tried to run the code.

Your assistance or pointing in the right direction would be much appreciated.

 

[John_w]

I've tweaked your code:

Sub CreateFolderStructure()
    Dim objRow As Range, objCell As Range, strFolders As String
    
    For Each objRow In Worksheets("Sheet2").UsedRange.Rows
        strFolders = "C:\The_NEWS"
        For Each objCell In objRow.Cells
            strFolders = strFolders & "\" & objCell.Value
        Next
        Shell "cmd /c md " & Chr(34) & strFolders & Chr(34)
    Next
End Sub

 

[coetzej9]

Thanks John,
On my side it creates folder C:\The_NEWS, but the folder is empty.
Thanks,
Hans

 

[Seric]

20200217 Dummy K1 Advisor List.xlsx
	A	B	C	D	E
1	France	Paris	Marseille	Lyon	Toulouse
2	UK	London	Birmingham	Cambridge	Canterbury
3	Italy	Milan	Rome	Naples	Turin
File Names

How would the code above need to be adjusted if I wanted the country to be the main folder and all the cities would be subfolders within it, instead of all nested inside each other?
The result would be something like this:

 

[John_w]

Change baseFolder string as required.

Public Sub CreateFolderStructure2()

    Dim baseFolder As String
    Dim objRow As Range, c As Long
  
    baseFolder = "C:\path\to\base folder\"
    If Right(baseFolder, 1) <> "\" Then baseFolder = baseFolder & "\"
  
    For Each objRow In Worksheets("Sheet2").UsedRange.Rows
        For c = 2 To objRow.Cells.Count
            Shell "cmd /c md " & Chr(34) & baseFolder & objRow.Cells(1, 1) & "\" & objRow.Cells(1, c) & Chr(34)
        Next
    Next
  
End Sub