16

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>\DataEntry\logs")

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, DataEntry\logs 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

Improve this question
4
  • Try just using the mkDir command. Commented Jun 24, 2015 at 18:07
  • @nicholas79171 I've also tried that, but it doesn't work. I think I may be on to something. It would appear VBA cannot create nested sub-directories? I've updated my question Commented Jun 24, 2015 at 18:10
  • Does the first directory get created? Commented Jun 24, 2015 at 18:17
  • @nicholas79171 The first directory does not get created either. I now changed my code to use 2 separate directory creations, one for the DataEntry and one for the logs and it seems to work. It seems VBA cannot create nested directories in a single statement? Commented Jun 24, 2015 at 18:27

4 Answers 4

Reset to default
18

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

i like to use the File System Object over the built in vba file handling. +1
Of course, if it's not practical to set a reference to Microsoft Scripting Runtime, you can replace Dim FSO As New FileSystemObject with Dim FSO Set FSO = CreateObject("Scripting.FileSystemObject")
8

Need to create each folder one at a time. You can use code like this to do so:

Sub tgr()

    Dim strFolderPath As String
    Dim strBuildPath As String
    Dim varFolder As Variant

    strFolderPath = "C:\Users\<my_username>\DataEntry\logs"

    If Right(strFolderPath, 1) = "\" Then strFolderPath = Left(strFolderPath, Len(strFolderPath) - 1)
    For Each varFolder In Split(strFolderPath, "\")
        If Len(strBuildPath) = 0 Then
            strBuildPath = varFolder & "\"
        Else
            strBuildPath = strBuildPath & varFolder & "\"
        End If
        If Len(Dir(strBuildPath, vbDirectory)) = 0 Then MkDir strBuildPath
    Next varFolder

    'The full folder path has been created regardless of nested subdirectories
    'Continue with your code here

End Sub
Improve this answer

1 Comment

I must say I am amazed VBA is not smart enough to create the parent directories by itself...
3

Agree with MarkD's suggestion to utilize recursion, it was the code I came here looking to find. In a scenario where the path provided uses a nonexistent root folder it will result in an infinite loop. Adding to MarkD's solution to check for zero length path.

Function CreateFolderRecursive(path As String) As Boolean
    Static FSO As FileSystemObject
 
    'Initialize FSO variable if not already setup
    If FSO Is Nothing Then Set lFSO = New FileSystemObject

    'Is the path paramater populated
    If Len(path) = 0 Then
      CreateFolderRecursive = False
      Exit Function
    End If

    '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
Improve this answer

Comments

3

This is how I normally do it:

Public Function FSOCreateFolder2(strPath As String) As Boolean
    Static FSO As New FileSystemObject
    If Not FSO.FolderExists(FSO.GetParentFolderName(strPath)) Then
        'walk back up until you find one that exists
        FSOCreateFolder2 FSO.GetParentFolderName(strPath)
    End If
    FSO.CreateFolder strPath
End Function

I prefer to check the arguments and the existence of the path outside of a recursive function.

Improve this answer

1 Comment

This is a very concise solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.