Joining Paths in VBA

A common source of errors when building file paths is to end up with a double path separator or a missing path separator.

For example, say you have a function that is writing to a file that should be named C:\Path\To\My\File.txt.  Depending on how you call said function, you can easily end up with errors like this...

Path C:\Path\To\MyFile.txt does not exist.

... or this ...

Invalid path: C:\Path\To\My\\File.txt

FileSystemObject BuildPath Method

One way to avoid this fate is to use the BuildPath method of the FileSystemObject.

The StackOverflow answer below offers this as a solution:

How to write a function to combine folder path and file name?

I would like to pass the full path of a text file to one of the function.i am placing the my script, and text file at same locationby using the below command i found the folder path where my scri...

Stack Overflowsurendra

Joining Multiple Paths

I often find myself wanting to join multiple subfolders to create a full path.  This is something I often do when building file names to refer to images stored on a network folder.  

(Storing images within a database is usually a bad idea.  There are some exceptions to this rule, such as when you need to guarantee transactional consistency between the image and the related record.  The best practice in most cases is to store the image on the file system with some sort of name that can be composed from database field values.  Newer versions of SQL Server include the FileTable feature which does exactly this, but the database engine handles all the details behind the scenes.)

Joining more than two path sections requires multiple calls to the BuildPath method.  The code's readability suffers as a result.  You either nest the function calls, which is ugly, or you use multiple lines to call the function, which is unnecessarily verbose.

I wrote the function below to combine an arbitrary number of path segments into a full filepath.  Each segment can be passed with or without a leading or trailing backslash.  The function is loosely based on the Python os.path.join method,  so I've also borrowed the name.

The Code: PathJoin()

By default, all trailing backslashes are removed.  If you want to force a trailing backslash, you need to pass a single backslash as the final argument to the function.  This is true even if the final section ends in a backslash.

For complete usage details, refer to the doc tests in the comments of the function header.  (Doc tests are great, right? They're another feature I shamelessly ripped off of the Python ecosystem.)  The function also makes calls to my Conc() function, so you'll need a copy of that to get everything to compile.  

Enjoy!

'---------------------------------------------------------------------------------------
' Procedure : PathJoin
' Author    : Mike Wolfe (mike@nolongerset.com)
' Source    : https://nolongerset.com/joining-paths-in-vba/
' Date      : 10/21/2015
' Purpose   : Intelligently joins path components automatically dealing with backslashes.
' Notes     - To add a trailing backslash, pass a single backslash as the final parameter.
'           - If there is no single backslash passed at the end, there will be no trailing
'               backslash (even if the final parameter contains a trailing backslash).
'           - A leading backslash in the first parameter will be left in place.
'           - Empty path components are ignored.
'---------------------------------------------------------------------------------------
'>>> PathJoin("C:\", "Users", "Public", "\")
'C:\Users\Public\
'>>> PathJoin("C:", "Users", "Public", "Settings.ini")
'C:\Users\Public\Settings.ini
'>>> PathJoin("\\localpc\C$", "\Users\", "\Public\")
'\\localpc\C$\Users\Public
'>>> PathJoin("\\localpc\C$", "\Users\", "\Public\", "")
'\\localpc\C$\Users\Public
'>>> PathJoin("\\localpc\C$", "\Users\", "\Public\", "\")
'\\localpc\C$\Users\Public\
'>>> PathJoin("Users", "Public")
'Users\Public
'>>> PathJoin("\Users", "Public\Documents", "New Text Document.txt")
'\Users\Public\Documents\New Text Document.txt
'>>> PathJoin("C:\Users\", "", "Public", "\", "Documents", "\")
'C:\Users\Public\Documents\
'>>> PathJoin("C:\Users\Public\")
'C:\Users\Public
'>>> PathJoin("C:\Users\Public\", "\")
'C:\Users\Public\
'>>> PathJoin("C:\Users\Public", "\")
'C:\Users\Public\
Public Function PathJoin(ParamArray PathComponents() As Variant) As String
    Dim LowerBound As Integer
    LowerBound = LBound(PathComponents)

    Dim UpperBound As Integer
    UpperBound = UBound(PathComponents)

    Dim i As Integer
    For i = LowerBound To UpperBound
        Dim Component As String
        Component = CStr(PathComponents(i))

        If Component = "\" And i = UpperBound Then
            'Add a trailing slash
            PathJoin = PathJoin & "\"
        Else
            'Strip trailing slash if necessary
            If Right(Component, 1) = "\" Then Component = Left(Component, Len(Component) - 1)

            'Strip leading slash if necessary
            If i > LowerBound And Left(Component, 1) = "\" Then Component = Mid(Component, 2)

            If Len(Component) = 0 Then
                'do nothing
            Else
                PathJoin = Conc(PathJoin, Component, "\")
            End If
        End If
    Next i
End Function

Image by Jill Wellington from Pixabay