VBA Function to Create One, Two, or More, Paths

[AlexB123]

Hi all,

I have several databases that make use of the mkdir function to verify that year and month folders are always created before storing reports. To save room and make my code more efficient, I wrote a function, CheckForPaths, that accepts two string arguments, and uses if functions to call mkdir (where needed). Sometimes I need to make more than two paths ... sometimes three, or even four or six. To handle extra paths, I created CheckOnePath.

I will call the functions several times, as needed, to create the correct number of paths.

How can I rewrite my functions so that I only have one? That is, could I rewrite CheckForPaths so that I can call:

call CheckForPaths(onePath)
call CheckForPaths(onePath, twoPath)
call CheckForPaths(onePath, twoPath, ..., nPath)
?

Thanks!

Option Compare Database
Public Sub CheckForPaths(sPath1 As String, sPath2 As String)
    If Len(Dir(sPath1, vbDirectory)) = 0 Then
        MkDir (sPath1)
    End If
                        
    If Len(Dir(sPath2, vbDirectory)) = 0 Then
        MkDir (sPath2)
    End If
End Sub
Public Sub CheckOnePath(sPath3 As String)
    If Len(Dir(sPath3, vbDirectory)) = 0 Then
        MkDir (sPath3)
    End If
                        
End Sub

 

[dmt32]

Hi,
I created function below for Excel VBA but not able to test as do not have Access on my desktop and my Access days are very rusty.
see if it will do what you want

Function MakePath(ByVal FolderPath As String) As Boolean
    Dim SubFolders() As String, MakeFolder As String
    Dim i As Integer
    
    On Error GoTo exitfunction
    SubFolders = Split(FolderPath, "\")
    For i = 0 To UBound(SubFolders)
'build folder path
        MakeFolder = IIf(i = 0, SubFolders(i), MakeFolder & "\" & SubFolders(i))
'create folder(s)
        If Dir(MakeFolder, vbDirectory) = vbNullString Then MkDir MakeFolder
    Next
     
exitfunction:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error" Else MakePath = True
End Function

call by passing the whole path

  If Not MakePath("C:\Test\Folder1\Folder2\Folder3") Then Exit Sub

Dave

 

[AlexB123]

Wanted to follow up with my solution, in case anyone in the future looks at this thread. The solution was to just add the keyword "Optional" along with more values.

Public Sub CheckForPaths(sPath1 As String, Optional sPath2 As String, Optional sPath3 As String, Optional sPath4 As String, Optional sPath5 As String, Optional sPath6 As String)
    If Len(Dir(sPath1, vbDirectory)) = 0 Then
        MkDir (sPath1)
    End If
                        
    If Len(Dir(sPath2, vbDirectory)) = 0 Then
        MkDir (sPath2)
    End If
    If Len(Dir(sPath3, vbDirectory)) = 0 Then
        MkDir (sPath3)
    End If
                        
    If Len(Dir(sPath4, vbDirectory)) = 0 Then
        MkDir (sPath4)
    End If
    If Len(Dir(sPath5, vbDirectory)) = 0 Then
        MkDir (sPath5)
    End If
                        
    If Len(Dir(sPath6, vbDirectory)) = 0 Then
        MkDir (sPath6)
    End If

End Sub

 

[xenou]

Also note that you can pass an array of paths to allow for 1 or more paths.
Also note that there exist methods to create intervening folders so that if the point of multiple paths is to create parents of subfolders you can pass in one path for that too, without having to break it up and create each folder one by one.

 

[dmt32]

Wanted to follow up with my solution, in case anyone in the future looks at this thread.

Pleased you found a solution but a response from you with regard to suggestion I made would have been nice.

Dave