Trying to create folders and subfolders from excel using VBA

Question

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!

I'm sure someone will suggest you use recursion to acheive this — user2261597
– user2261597, Commented Dec 13, 2018 at 17:08
Get rid of On Error Resume Next it's likely hiding the issue from you — chris neilsen
– chris neilsen, Commented Dec 13, 2018 at 17:23
@chris I missed that. On Error Resume Next. Only to be used on the next line and followed by On Error Goto 0. Really misplaced just before Next. Why Mason? — user2261597
– user2261597, Commented Dec 13, 2018 at 17:44
@peakpeak Well, I don't really have a description of why that's in the code, but the issue I'm having isn't with an error in the code. When used before, the code I posted works fine and creates a single list of folders without an issue. My question is how I could change it to accomplish my changed objective. — Mason B.
– Mason B., Commented Dec 13, 2018 at 17:53
Indenting is not for "working" (unless you're in Python) - it's for reading. All I'm suggesting is think about making life easier for us all here by indenting any posted code. — Tim Williams
– Tim Williams, Commented Dec 13, 2018 at 18:34

Tim Williams · Accepted Answer · 2018-12-13 19:43:38Z

2

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

edited Dec 13, 2018 at 19:43

answered Dec 13, 2018 at 18:42

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mason B. Over a year ago

Wow, thank you! I tested it and it looks like it's worked perfectly. I appreciate you sitting down to write out a solution to my question despite my lack of understanding of VBA.

Frank Ball Over a year ago

Mason B. - Also take note of how Tim indented his code. It makes it much more readable.

Collectives™ on Stack Overflow

Trying to create folders and subfolders from excel using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related