Attempting to create folder six levels down, get Path/File access error (runtime error 75)

[OaklandJim]

I am trying to use some code that I got from a Mr Excel post. I need to create a folder that is six levels down. Keep getting that Runtime error 75, Path/File access error. Code I am using is here. Might someone assist with correction(s) needed?

'routine to create your directory path
Sub MakeDirectory(FolderPath As String)

Debug.Print "FolderPath = " & FolderPath

    Dim i As Integer
    
    Dim strPath As String

    Dim x As Variant

    x = Split(FolderPath, "\")
    
    For i = 0 To UBound(x) - 1
        strPath = strPath & x(i) & "\"
        
Debug.Print i + 1 & ". " & strPath

        'If Not FolderExists(strPath) Then MkDir strPath '<= Runtime error 75
    Next i

End Sub


'function to check if folder exist
Function FolderExists(FolderPath As String) As Boolean
    On Error Resume Next
    ChDir FolderPath
    If Err Then FolderExists = False Else FolderExists = True
End Function

and debug.print output is here.

FolderPath = C:\Users\Planner3\XXXXX\Order Book - General\Type1\
1. C:\
2. C:\Users\
3. C:\Users\Planner3\
4. C:\Users\Planner3\XXXXX\
5. C:\Users\Planner3\XXXXX\Order Book - General\
6. C:\Users\Planner3\XXXXX\Order Book - General\Type1\

 

[OaklandJim]

If I uncomment the offending line of code

If Not FolderExists(strPath) Then MkDir strPath

Debug.Print shows three folder levels and no new folder is created.

FolderPath = C:\Users\Planner4\XXXXX\Order Book - General\Type3\
1. C:\
2. C:\Users\
3. C:\Users\Planner4\

 

[DanteAmor]

Hello @OaklandJim, thanks for posting on the forum.

Please try this. Run test macro.

Sub MakeDirectory(FolderPath As String)
  Dim i As Integer
  Dim strPath As String
  Dim x As Variant
  Dim myDrive As String
 
  x = Split(FolderPath, "\")
  myDrive = x(0) & "\"
  For i = 1 To UBound(x) - 1
    strPath = strPath & x(i) & "\"
    If Dir(myDrive & strPath, vbDirectory) = "" Then
      MkDir myDrive & strPath
    End If
  Next i
End Sub

Sub test()
  Call MakeDirectory("C:\Users\Planner3\XXXXX\Order Book - General\Type1\")
End Sub

Note:
The FolderExists function is not required

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[OaklandJim]

Dante. I really appreciate the assistance. I cannot get your code to work.

If I comment out the line with mkdir

    If Dir(myDrive & strPath, vbDirectory) = "" Then
      'MkDir myDrive & strPath
    End If

I get this Debug.Print so I know that code loops the correct path elements.

C:\Users\Planner2\test\Order Book - General\Type1\
1. C:\Users\
2. C:\Users\Planner2\
3. C:\Users\Planner2\test\
4. C:\Users\Planner2\test\Order Book - General\
5. C:\Users\Planner2\test\Order Book - General\Type1\

If I uncomment that line I get that Runtime error 75 and I get this Debug.Print output

C:\Users\Planner2\test\Order Book - General\Type1\
1. C:\Users\
2. C:\Users\Planner2\

 

[OaklandJim]

I should have posted your code with the Debug.Print commands added...

Sub MakeDirectory(FolderPath As String)

Debug.Print FolderPath

  Dim i As Integer
  Dim strPath As String
  Dim x As Variant
  Dim myDrive As String
 
  x = Split(FolderPath, "\")
  myDrive = x(0) & "\"
  For i = 1 To UBound(x) - 1
    strPath = strPath & x(i) & "\"
    
Debug.Print i & ". " & myDrive & strPath

    If Dir(myDrive & strPath, vbDirectory) = "" Then
      MkDir myDrive & strPath
    End If
  Next i
End Sub

 

[rlv01]

Another approach is to use recursion

Function MakeDirectory(ByRef FSO As Object, FolderPath As String) As Boolean
    With FSO
        If .FolderExists(FolderPath) Then
            MakeDirectory = True
            Exit Function
        End If
        If Not .FolderExists(.GetParentFolderName(FolderPath)) Then
            MakeDirectory FSO, .GetParentFolderName(FolderPath)
        End If
        .CreateFolder FolderPath
        If .FolderExists(FolderPath) Then
            MakeDirectory = True
        Else
            MakeDirectory = False
        End If
    End With
End Function

Sub Test()
    Dim FolderPath As String, FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FolderPath = "C:\Users\Planner3\XXXXX\Order Book - General\Type1\"

    If MakeDirectory(FSO, FolderPath) Then
        MsgBox "Success!"
    Else
        MsgBox "Failure"
    End If
End Sub

Note that if you get a Runtime Error 70, it means you do not have permission to create subfolders in one of your specified folders. This is not an uncommon restriction for the C:\Users directory on corporate managed PCs

 

[DanteAmor]

If I uncomment that line I get that Runtime error 75 and I get this Debug.Print output

Try another path, for example:
C:\work\example1\test\some

Manually create the initial "work" folder and then run my macro.

To rule out permissions issues.

 

[OaklandJim]

DanteAmor. I set permissions for the User folder and then your code worked. Thank you so much. I really appreciate the assistance.