RYU5
RYU5

Reputation: 51

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

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

Upvotes: 4

Views: 130

Answers (2)

BLitE.exe
BLitE.exe

Reputation: 321

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

Upvotes: 1

CLR
CLR

Reputation: 12289

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

Upvotes: 2

Related Questions