How To Sanitize A Filename

As a part 2 to my post on Validating a Filename to ensure it conforms to Microsoft Windows naming convention, refer to:

Determine If A Filename Is Valid
It is quite common in applications to allow the user to specify a path and/or filename for saving files. As such, a best practice would be to validate the user’s input prior to blindly using it and perhaps getting errors and crashing our applications. Thus, I thought I’d quickly share one possible way to tackle this.

I also wanted to demonstrate how we could alternatively sanitize any proposed filename.  So instead of validating, or reporting back issues with the proposed filename, how we could simply eliminate the issues and return a valid filename.


The principle is very similar to the code to perform validation, but instead, we replace the offending characters/terms.

The code thus turns into:

'---------------------------------------------------------------------------------------
' Procedure : Filename_Sanitize
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Sanitize a filename, thus eliminating any non-conforming characters/terms
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFileName             : Filename to sanitize (including extension)
' sReplacementCharacter : Character to use to replace illegal characters/terms
'
' Usage:
' ~~~~~~
' ? Filename_Sanitize("VBA Samples and Explanations.docx")
'   Returns -> VBA Samples And Explanations.docx
'
' ? Filename_Sanitize("VBA Samples >>> Explanations.docx")
'   Returns -> VBA Samples ___ Explanations.docx
'
' ? Filename_Sanitize("VBA Samples >>> Explanations.docx", "")
'   Returns -> VBA Samples  Explanations.docx
'
' ? Filename_Sanitize("VBA Samples >>> Explanations.docx", "-")
'   Returns -> VBA Samples - --Explanations.docx
'
' ? Filename_Sanitize("COM3.docx")
'   Returns -> ____.docx
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-03-25              Public release
' 2         2024-03-28              Filename ending with '..' or '  '
'---------------------------------------------------------------------------------------
Public Function Filename_Sanitize(sFilename As String, _
                                  Optional sReplacementCharacter As String = "_") As String
    On Error GoTo Error_Handler
    Dim iCounter              As Long
    Dim aChars()              As String
    Dim aNames()              As String
    Dim sFilenameWOExt        As String    'Filename without extension
    Dim sFilenameExt          As String
    Const sIllegalChrs = "<,>,:,"",/,\,|,?,*"
    Const sIllegalNames = "CON,PRN,AUX,NUL,COM0,COM1,COM2,COM3,COM4,COM5,COM6,COM7,COM8" & _
          ",COM9,COM¹,COM²,COM³,LPT0,LPT1,LPT2,LPT3,LPT4,LPT5,LPT6,LPT7" & _
          ",LPT8,LPT9,LPT¹,LPT²,LPT³"

    'illegal characters
    aChars = Split(sIllegalChrs, ",")
    For iCounter = 0 To UBound(aChars)
        sFilename = Replace(sFilename, aChars(iCounter), sReplacementCharacter)
    Next iCounter

    'Ascii 0 through 31
    For iCounter = 0 To 31
        sFilename = Replace(sFilename, Chr(iCounter), sReplacementCharacter)
    Next iCounter

    'Illegal names
    If InStr(sFilename, ".") > 0 Then
        sFilenameWOExt = Left(sFilename, InStrRev(sFilename, ".") - 1)
        sFilenameExt = Right(sFilename, Len(sFilename) - InStrRev(sFilename, "."))
    Else
        sFilenameWOExt = sFilename    'No Extension
    End If
    aNames = Split(sIllegalNames, ",")
    For iCounter = 0 To UBound(aNames)
        If sFilenameWOExt = aNames(iCounter) Then
            sFilename = String(Len(sFilenameWOExt), sReplacementCharacter) & _
                        "." & sFilenameExt
        End If
    Next iCounter

    'End with '..' or '  '
    If Right(sFilename, 2) = ".." Then
         sFilename = Left(sFilename, Len(sFilename) - 2) & sReplacementCharacter & "."
    End If
    If Right(sFilename, 2) = "  " Then
         sFilename = Left(sFilename, Len(sFilename) - 2) & sReplacementCharacter & "."
    End If

    Filename_Sanitize = sFilename

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Filename_Sanitize" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Then, we can simply use it by doing:

    Dim sFilename             As String

    sFilename = "VBA Samples and Explanations.docx"
    sFilename = Filename_Sanitize(sFilename)
    Debug.Print sFilename

which would output

VBA Samples and Explanations.docx

Or

    Dim sFilename             As String

    sFilename = "VBA Samples > Explanat|ions.docx"
    sFilename = Filename_Sanitize(sFilename, "")
    Debug.Print sFilename

which in turn outputs

VBA Samples  Explanations.docx

Or

    Dim sFilename             As String

    sFilename = "COM3.docx"
    sFilename = Filename_Sanitize(sFilename, "-")
    Debug.Print sFilename

and the result is

----.docx

Also note that if you which to stop users from using other characters beyond those specifically mentioned by Microsoft in their documentation, all you have to do is add them to the sIllegalChrs declaration in the function.