Make Directory

Ran into a situation this week where one of my clients wanted to save documents to a deeply nested path structure which included their clients name, the calendar year, calendar month, and then the file name.  Unfortunately, many times those paths don’t exist and you cannot simply execute a command like:

mkdir “H:\ClientReports\ClientName\2019\January\”

to create the path (folder) you want, unless the previous folder already exists.  To resolve this problem, I created a simple function into which I pass the full path where I want to write the file, and the function returns either a True (if the folder already exists or if the function was able to create it) or False(if the folder doesn’t already exist and attempting to create it raised an error).

Now I can use code like:

If MakeDirectory(“H:\ClientReports\ClientName\2019\January\”) = False Then
msgbox “Could not create the destination folder”
exit sub
Else
docmd.OutputTo …
End if

The function looks like the following.  It probably needs a bit more in the error handler, but works find for the time being:

Public Function MakeDirectory(FullPath As String) As Boolean

Dim strPathSegments() As String
Dim intLoop As Integer
Dim strBuildPath As String

On Error GoTo ProcError

strPathSegments() = Split(FullPath, “\”)
For intLoop = LBound(strPathSegments) To UBound(strPathSegments)
If Trim(strPathSegments(intLoop)) = “” Then
‘do nothing
Else
strBuildPath = strBuildPath & strPathSegments(intLoop) & “\”
If Dir(strBuildPath, vbDirectory) = “” Then MkDir strBuildPath
End If
Next
MakeDirectory = True

ProcExit:
Exit Function
ProcError:
MakeDirectory = False
Select Case Err.Number
Case 52, 75
MsgBox “Unable to create the following folder:” & vbCrLf & vbCrLf & strBuildPath
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
Debug.Print Err.Number, Err.Description
End Select
Resume ProcExit
Resume

End Function

Leave a Reply

Your email address will not be published. Required fields are marked *

© 2019 Developing Solutions | ScrollMe by AccessPress Themes