How can i create for example 5 folders out of an excel sheet with vba?

4
votes

I am using the following vba code to create an folder structure with the data of the excel sheet:

Option Explicit

Declare Function MakePath& Lib "imagehlp.dll" Alias _
   "MakeSureDirectoryPathExists" (ByVal sPath$)

Sub CreatePaths()
Dim i&
With Tabelle1
For i = 1 To .Cells(.Rows.Count, "E").End(xlUp).Row
MakePath .Cells(i, "E").Text
Next
End With
End Sub

"C:\Users\xxxxx\Desktop\test\H01\U01\UU01"

Each row is created as a folder structure in my chosen folder, until here it does what i need to. Column E adds column A-D and separates it with a backslash. Now i need to add for example 4 folders (A,B,C,D) in each folder of the column 'E' and i tried to add it but it does not work. What do i have to add to my vba code so the folders are created?

Feel free to ask questions. myExcelsheet

2
vbaexcel
Can you give a sample output that you want? Like you want something 4 separate folders in C:\Users\xxxx\Desktop\test so this certain directory will have a folder name H01, U01, UU01? Is this what you need? – BLitE.exe
Sure, for example the first row C:\Users\xxxxx\Desktop\test\H01\U01\UU01 I want to add the folders "A","B","C","D" to the folder UU01, and i want to add it to each folder in the column "E" so every Folder "below" UU01 – RYU5
You can't create "C:\Users\xxxxx\Desktop\test\H01\U01\UU01" unless "C:\Users\xxxxx\Desktop\test\H01\U01" exists. You need to work through the path, level by level and where each level doesn't exist, create it. – CLR
No you missunderstood me, it creates every folder until the last one already, but i want to add 4 Folders into the last one. – RYU5
Sorry, yes I see that now. Can't believe in all these years I've been using my own make_directory() sub! – CLR

2 Answers

2
votes

How about something like this:

Option Explicit

Declare Function MakePath& Lib "imagehlp.dll" Alias _
    "MakeSureDirectoryPathExists" (ByVal sPath$)

Sub CreatePaths()

Dim i&, subfolders As Variant, subfolder As Variant
subfolders = Split("A,B,C,D", ",")

With Tabelle1
    For i = 1 To .Cells(.Rows.Count, "E").End(xlUp).Row
        For Each subfolder In subfolders
            MakePath .Cells(i, "E").Text & "\" & subfolder
        Next
    Next
End With

End Sub
1
votes

Try this one because you have a root folder in column A. We will call this is as our ParentDirectory and as we traverse on the inner folder which on Column B, C, D we will create individual folders name "A,B,C,D" respectively.

Option Explicit

Declare Function MakePath& Lib "imagehlp.dll" Alias _
   "MakeSureDirectoryPathExists" (ByVal sPath$)

Sub CreatePaths()

Dim i As Integer, x As Integer
Dim currentDirectory As String, parentDirectory As String
With Tabelle1
For i = 1 To .Cells(.Rows.Count, "E").End(xlUp).Row
    parentDirectory = .Cells(i, 1) & "\"
    For x = 2 To 4
        currentDirectory = parentDirectory & .Cells(i, x).Text & "\"
        MakePath currentDirectory
        MakePath currentDirectory & "A\"
        MakePath currentDirectory & "B\"
        MakePath currentDirectory & "C\"
        MakePath currentDirectory & "D\"
        parentDirectory = currentDirectory
    Next x
Next
End With
End Sub