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

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
207
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!

Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

Code:
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

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

Dave
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top