vbadirectorydirectory-structureexcel-365

Script to create folders and subfolders and their subfolders shows an error


I'm attempting to create a 5 level folder having an Excel sheet as the source.

NOTE: Excel sheet is column organized by levels on each column as shown.

Reference VB script from:

https://superuser.com/questions/799666/creating-folders-and-sub-folders-with-a-vba-macro

ERR: **'Shell ("cmd /c md " & Chr(34) & strFolders & Chr(34))**

Script:

Sub CreateFolderStructure()
    Dim objRow As Range, objCell As Range, strFolders As String

    For Each objRow In ActiveSheet.UsedRange.Rows
        strFolders = "C:\Users\morar\Downloads\FoldersNSubsr"
        
        For Each objCell In objRow.Cells
            strFolders = strFolders & "\" & objCell
        Next
           **Shell ("cmd /c md " & Chr(34) & strFolders & Chr(34))**
    Next
End Sub

I haven't found any solution yet.


Solution

  • Without Shell:

    Sub CreateFolderStructure()
        Const ROOT_DIR As String = "C:\Temp"
        
        Dim objRow As Range, c As Range, strFolders As String, v
    
        For Each objRow In ActiveSheet.UsedRange.Rows
            strFolders = ROOT_DIR
            
            For Each c In objRow.Cells
                v = Trim(c.Value)
                If Len(v) > 0 Then
                    strFolders = strFolders & "\" & v
                    'do we need to create this folder?
                    If Dir(strFolders, vbDirectory) = vbNullString Then
                        Debug.Print "Creating: " & strFolders
                        MkDir strFolders
                    Else
                        Debug.Print "Exists: " & strFolders
                    End If
                End If
            Next c
        Next objRow
    End Sub