VBA Split Path (either logical or network) to create each folder in path (excel)

Question

i am looking to a solution to create each folder from the path either logical path with drive letter or network path better in loop format

strPath = "c:\Parent Folder\Child Folder\Sub Folder\Stuff"
' OR
strPath = "\\serverfolder\Parent Folder\Child Folder\Sub Folder\Stuff"

varSplit = Split(strPath, "\", , vbTextCompare)

'   CreateFolder for each folder in path
CreateFolder(varSplit(0)) ' if valid, C: or identify is it server folder
CreateFolder(varSplit(1)) ' if dosent exist, Parent Folder
CreateFolder(varSplit(2)) ' Child Folder
CreateFolder(varSplit(3)) ' Sub Folder
CreateFolder(varSplit(4)) ' Stuff

' better in loop

Public Function CreateFolder(ByVal Path As String) As String 
   strPath = Path

   ' Check Destination Folder. Create it, if not exist` 
   On Error Resume Next 
   Select Case Dir(strPath, vbDirectory) 
       Case vbNullString 
           MakeDir = Empty 
       Case Else 
           On Error Resume Next 
           VBA.FileSystem.MkDir (strPath) 
           MakeDir = strPath 
   End Select 

End Function

Use `LBound` and `Ubound` to loop, if that is your question. — BigBen, Jan 09 '20 at 19:19
using loop but facing error at first part for network path when check "\\serverfolder" or "\\serverfolder\" error=bad file name or path — Akhtar, Jan 09 '20 at 19:22
`Public Function MakeDir(ByVal Path As String, ByVal Directory As String) As String` `strPath = Path & Directory` ` ' Check Destination Folder. Create it, if not exist` `On Error Resume Next` `Select Case Dir(strPath, vbDirectory)` `Case vbNullString` `MakeDir = Empty` `Case Else` `On Error Resume Next` `VBA.FileSystem.MkDir (strPath)` `MakeDir = strPath` `End Select` `End Function` — Akhtar, Jan 09 '20 at 19:35
Please [edit] your original question with that code, instead of posting in a comment, thanks. — BigBen, Jan 09 '20 at 19:35
https://stackoverflow.com/questions/10803834/create-a-folder-and-sub-folder-in-excel-vba — Tim Williams, Jan 09 '20 at 19:47

score 0 · Accepted Answer · answered Jan 09 '20 at 20:58

You can instead use a single path, and create it using a recursive function:

This will create all necessary folders. (So if you just have C:\Users\Akhtar, it'll create MyFolder and subFolder in MyFolder, etc.:

Dim myPath as String
myPath = "C:\Users\Akhtar\MyFolder\subFolder\moreFolders\Others\"
myMkDir(myPath)

Public Sub MyMkDir(sPath As String)
'https://www.devhut.net/2011/09/15/vba-create-directory-structurecreate-multiple-directories/
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

what if `path = "\\servername` or `path = \\servername\sharedfolder` or `path = \\servername\sharedfolder\folder1` where **sharefolder** doesn't exist ? — Akhtar, Jan 10 '20 at 15:59
@Akhtar I am not sure if it'll work with "non windows" paths but assuming it does it'll create the folders needed. — BruceWayne, Jan 10 '20 at 16:00
yes, **servername** and **sharedfolder** can't be verify by `DIR()` function. — Akhtar, Jan 10 '20 at 17:15

VBA Split Path (either logical or network) to create each folder in path (excel)

1 Answers1