CODEWITHSUNDEEP
excelvba
Mason B.
Mason B.

Reputation: 11

Trying to create folders and subfolders from excel using VBA

I have two columns of data within excel that I'm trying to transform into a list of folders and subfolders. Column A will be the first list of primary folders, and each entry of Column B will be a subfolder in the corresponding folder from Column A. The end result would be 20 folders, each with a single folder inside. I previously used this code-

Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub

-to create a list of individual folders from a single column of data. I'm wondering how I could alter that code to make a list of folders with the first column, and to have each entry in the second column be a subfolder within the corresponding folder from column A. The Excel Spreadsheet looks like this:

    Column A        Column B
1   Folder 1    Subfolder in Folder 1
2   Folder 2    Subfolder in Folder 2
3   Folder 3    Subfolder in Folder 3
4   Folder 4    Subfolder in Folder 4
5   Folder 5    Subfolder in Folder 5
6   Folder 6    Subfolder in Folder 6
7   Folder 7    Subfolder in Folder 7
8   Folder 8    Subfolder in Folder 8
9   Folder 9    Subfolder in Folder 9
10  Folder 10   Subfolder in Folder 10

With my very limited understanding of VBA, any help at all would be appreciated!

Upvotes: 1

Views: 3526

Answers (1)

Tim Williams
Tim Williams

Reputation: 166181

Untested:

Sub MakeFolders()
    Dim Rng As Range, rw As Range, c As Range
    Dim p As String, v As String

    Set Rng = Selection

    'process each selected row
    For Each rw In Rng.Rows
        p = ActiveWorkbook.Path & "\" 'set initial root path for this row
        'process each cell in this row
        For Each c In rw.Cells
            v = Trim(c.Value) 'what's in the cell?
            If Len(v) > 0 Then
                If Len(Dir(p & v, vbDirectory)) = 0 Then MkDir (p & v) 'create if not already there
                p = p & v & "\" 'append to path (regardless of whether it needed to be created)
            End If
        Next c
    Next rw

End Sub

Upvotes: 2

Related Questions