vba - FileSystemObject.CreateFolder to create directory and subdirectories

Question

I would like to create a directory and a subdirectory with the following code:

Public fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
fso.CreateFolder ("C:Users<my_username>DataEntrylogs")

I am trying to create nested directories. In this case, the DataEntry directory would not exist, so essentially I would like to create 2 directories, DataEntrylogs under C:Users<username>

If I enter command prompt, I can create that directory with mkdir without any issues. However, I simply cannot get VBA to create that folder and I get:

Run-time error '76':

Path not found                        

I am using Excel VBA 2007/2010

Answer 1

tigeravatar's looping answer might work, but it's a bit hard to read. Instead of micromanaging the string handling yourself, the FileSystemObject has path manipulation functions available, and recursion is slightly easier to read than a loop.

Here is the function I use:

Function CreateFolderRecursive(path As String) As Boolean
    Dim FSO As New FileSystemObject

    'If the path exists as a file, the function fails.
    If FSO.FileExists(path) Then
        CreateFolderRecursive = False
        Exit Function
    End If

    'If the path already exists as a folder, don't do anything and return success.
    If FSO.FolderExists(path) Then
        CreateFolderRecursive = True
        Exit Function
    End If

    'recursively create the parent folder, then if successful create the top folder.
    If CreateFolderRecursive(FSO.GetParentFolderName(path)) Then
        If FSO.CreateFolder(path) Is Nothing Then
            CreateFolderRecursive = False
        Else
            CreateFolderRecursive = True
        End If
    Else
        CreateFolderRecursive = False
    End If
End Function