One of he most common methods for creating directories/folders in VBA is to use the MkDir statement. For instance:

MkDir "C:\databases\"

One quickly learns the limitations of this technique the minute they have to create a directory structure with multiple sub-folders. MkDir can only create 1 directory at a time and cannot create a sub-directory. Hence, assuming that C:\databases does not already exist, the following would not work and will return an error!

MkDir "C:\databases\msaccess\"

If you absolutely want to create such a structure using the MkDir statement you’d have to do so using 2 MkDir statement. For instance:

MkDir "C:\databases\"
MkDir "C:\databases\msaccess\"

Now if you need to merely create 1 or 2 sub-folder MkDir may still be acceptable, but there are cases where this is simply impracticable and another solution needs to be found. Well, I found 3 possible alternate approaches that I thought I’d share today.

Using The MakeSureDirectoryPathExists API

The second I found searching through the net and I no longer know the original source of the code (if someone knows e-mail me and I will put credit where it is due). It is a simple API which can create multiple directories in 1 call.

#If VBA7 Then
    Private Declare PtrSafe Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
#Else
    Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
#End If
 
Public Function MakeFullDir(sPath As String) As Boolean
    'Ensure we have the trailing \ in our Path
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    'Create our directory structure, if it does not already exist
    MakeFullDir = MakeSureDirectoryPathExists(sPath)
End Function

and you could use it by doing:

Call MakeFullDir("C:\Users\Dev\Desktop\dir1\dir11\dir111")

or, perhaps:

If MakeFullDir("C:\Users\Dev\Desktop\dir1\dir11\dir111") = True Then
    '...
End If

Using Plain VBA

Another possible solution, if someone wanted to tinker a little would be to merely parse the path into it’s directories and the using the DIR statement evaluate and create the directories where need be. Shouldn’t be that hard, but I haven’t taken the time to do this (maybe one day I will and will update this post then).

Okay, so it bothered me and I had to quickly put something together to stop my brain from churning! Below is what I pieced together rapidly. It is missing proper variable definitions (DIM statements) and error handling, but from my very brief testing, it does appear to work and doesn’t require any APIs! 100% VBA.

Public Sub MyMkDir(sPath As String)
    Dim iStart          As Integer
    Dim aDirs           As Variant
    Dim sCurDir         As String
    Dim i               As Integer

    If sPath <> "" Then
        aDirs = Split(sPath, "\")
        If Left(sPath, 2) = "\\" Then
            iStart = 3
        Else
            iStart = 1
        End If

        sCurDir = Left(sPath, InStr(iStart, sPath, "\"))

        For i = iStart To UBound(aDirs)
            sCurDir = sCurDir & aDirs(i) & "\"
            If Dir(sCurDir, vbDirectory) = vbNullString Then
                MkDir sCurDir
            End If
        Next i
    End If
End Sub

Chip Pearson’s Solution

Another possible approach can be found at:

Create Multiple Directories

Creating Nested Directories

As you can see, there are numerous way to handles this issue. Hopefully this helped answer a question for a few of you out there!

Useful Resources

MkDir statement (VBA)

Creates a new directory or folder.

Dir function (Visual Basic for Applications)

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

MakeSureDirectoryPathExists function (dbghelp.h) - Win32 apps

Creates all the directories in the specified path, beginning with the root.