VBA Express : Multiple Apps - Add trailing path separator (slash or colon) as necessary to folder path

Multiple Apps

Add trailing path separator (slash or colon) as necessary to folder path

Ease of Use

Intermediate

Version tested with

2003

Submitted by:

xCav8r

Description:

When given a folder path, it looks at the last character in the string to determine if a trailing path separator (slash or colon) should be added. If one is missing, it's added. If one is already there, the path remains unchanged.

Discussion:

If you ever have a need to concatenate (stick together) folder or file paths, then you're probably not unfamiliar with the unintended paths created as a result of a missing trailing path separator. This function was my simple answer to that headache. When used as a matter of habit, it reduces unnecessary error handling. This function will work in any application on Macintosh or Windows. The demonstration is in Microsoft Word.

Code:

instructions for use

			 ' One of the requirements for VBAX Knowledge Base submissions is that Option
 ' Explicit be used.  As a rule of thumb, I always use Option Explicit to reduce
 ' mistakes, but some of the code in my testing sub procedure requires that it
 ' be turned off.  See FixTrailingSeparator below for more details.
 
Option Explicit 
 
 ' Insert paths into the quotation makrs below to test the function.  The path
 ' may be a mapped drive path, UNC Path, or Macintosh path.
 
 ' eg.:  "D:\MyFolder\"   (mapped drive path)
 ' eg.:  "\\MachineName\VolumeName\My Folder\"  (UNC)
 ' eg.:  "MyHardDriveName:My Documents:My Files" (Mac)
 
Private Const mcstrPathWithSeparator As String = "C:\temp\" 
Private Const mcstrPathWithoutSeparator As String = "C:" 
 
 ' Macintosh only:  change the \ in the quotations below to :
 
Private Const mcstrPathSeparator As String = "\" 
 
Private Sub TestMyTrailingSeparator() 
     
     '---------------------------------------------------------------------------
     ' Desc      : This sub procedure calls the FixTrailingSeparator function
     '             using the module level constants defined above as the
     '             arguments, printing the results to the immediate window.  You
     '             need to adjust the values of these strings for your own file
     '             system.
     '
     ' Remarks   : The purpose of this procedure is to demonstrate how the
     '             FixTrailingSeparator function *might* be used.  It is included
     '             here soley for that reason, and it is not required for the
     '             function to work.
     '---------------------------------------------------------------------------
     
     ' Note:  These constants can be replaced with any valid string enclosed by
     '        quotation marks
     
    Debug.Print FixTrailingSeparator(mcstrPathWithSeparator, mcstrPathSeparator) 
    Debug.Print FixTrailingSeparator(mcstrPathWithoutSeparator, mcstrPathSeparator) 
     
     ' Application Specific Examples Below:  to use these examples, uncomment
     ' the block below and comment out Option Explicit.
     
     '    Select Case Application.Name
     '    Case "Microsoft Access"
     '        Debug.Print FixTrailingSeparator(CurrentProject.Path, mcstrPathSeparator)
     '    Case "Microsoft Excel"
     '        Debug.Print FixTrailingSeparator(ThisWorkbook.Path, mcstrPathSeparator)
     '    Case "Microsoft PowerPoint"
     '        Debug.Print FixTrailingSeparator(ActivePresentation.Path, mcstrPathSeparator)
     '    Case "Microsoft Publisher", "Microsoft Word", "Microsoft Visio"
     '        Debug.Print FixTrailingSeparator(ActiveDocument.Path, mcstrPathSeparator)
     '    Case "Microsoft Project"
     '        Debug.Print FixTrailingSeparator(ActiveProject.Path, mcstrPathSeparator)
     '    Case Else
     '        'Application not planned for; skip
     '    End Select
     
End Sub 
 
Public Function FixTrailingSeparator(Path As String, _ 
    Optional PathSeparator As String = "\") As String 
     
     '---------------------------------------------------------------------------
     ' Desc      : Adds a trailing path separator to a folder path if it doesn't
     '             exist.  Supports Windows or Macintosh.
     '
     ' Arguments : (1) Path:  Any path, Windows or Macintosh, UNC or mapped,
     '                 valid or not.
     '             (2) PathSeparator:  the default value is \, which is set
     '                 for Windows.  Macintosh users should use : for this
     '                 argument.
     '
     ' Remarks   : This function looks at the last character in the string
     '             supplied in the Path argument.  If it is the path separator,
     '             the function returns the string unchanged.  If it is not the
     '             path separator, the function appends the path separator and
     '             returns the modified string.
     '---------------------------------------------------------------------------
     
    Select Case Right(Path, 1) 
    Case PathSeparator 
        FixTrailingSeparator = Path 
    Case Else 
        FixTrailingSeparator = Path & PathSeparator 
    End Select 
     
End Function

How to use:

Open your document.
Press Alt + F11 to open the Visual Basic Editor (VBE).
Insert a standard module. (Insert -> Module)
Paste the code in the window to the right. (F7 to view; CTRL + V to paste)
Save the file.

Test the code:

From the VBE, make sure the Immediate Window is visible (CTRL + G)
With your cursor anywhere in the module, press F5. (If your cursor is in the TestFixTrailingSeparator sub procedure, then you won't have to do the next step.)
Select TestFixTrailingSeparator from the dialog box that appears, and then click Run.

Sample File:

AddTrailingPathSeparatorToFolderPath.zip 10.42KB

Approved by mdmackillop

This entry has been viewed 52 times.