Invalid Charaters (VBA)

Topic

New Reply

Does anybody have a snippet of code that will test if a string would be invalid as a filename. I have an Access app that uses a user-defined string as part of the filename for a printed report. I need to test for and prohibit a user from using invalid filename characters. I appreciate any input here. Thanks

Reply | Quote

Replies

Not exactly, but I have this old generic function that I use to strip out specified invalid file name characters without asking the user:

Function StripTheseChars(ByVal strInput As String, _
ByVal strChars2Del As String) As String
Dim lngPos As Long, lngInputSLen As Long, lngDelCharsLen As Long, lngCounter As Long
Dim strCurrentChar2Del As String
lngDelCharsLen = Len(strChars2Del)
For lngCounter = 1 To lngDelCharsLen
strCurrentChar2Del = Mid(strChars2Del, lngCounter, 1)
DWhile InStr(strInput, strCurrentChar2Del)
lngInputSLen = Len(strInput)
lngPos = InStr(strInput, strCurrentChar2Del)
strInput = Left(strInput, lngPos – 1) & Right(strInput, lngInputSLen – lngPos)
Loop
Next lngCounter
StripTheseChars = strInput
End Function

called like this
strSuggestFN = StripTheseChars(strSuggestFN, ” .*,|/()[]{}”””)

I think it’s coded for VB5, it could probably be simpler in VB6.

Reply | Quote

This is some old VBScript, but it looks as though the VBA would be identical in this case:

Do
    strTemp = InputBox("[Instructions to user]", _
        "Enter your information here!", strDefault)
    If Len(Trim(strTemp)) = 0 Then
        If MsgBox("Do you want to skip this directory after all?", 4+32) = 6 Then
            getInput = "*SKIPDIRECTORY*"
            Exit Function
        Else
            MsgBox "Please re-enter the prefix you want to add to the file names."
        End If
    Else
        'some minimal validation for illegal characters
        If (InStr(1, strTemp, "/") > 0) Or (InStr(1, strTemp, "") > 0) Or _
          (InStr(1, strTemp, "*") > 0) Or (InStr(1, strTemp, "?") > 0) Or _
          (InStr(1, strTemp, "|") > 0) Or (InStr(1, strTemp, """") > 0) Or _
          (InStr(1, strTemp, " 0) Or (InStr(1, strTemp, ">") > 0) Or _
          (InStr(1, strTemp, ":") > 0) Then
            MsgBox "Please revise the prefix so it does not contain illegal characters."
        Else
            getInput = strTemp
            Exit Do
        End If
    End If
Loop

Hope this helps.

Reply | Quote

This is some old VBScript, but it looks as though the VBA would be identical in this case:

Do
    strTemp = InputBox("[Instructions to user]", _
        "Enter your information here!", strDefault)
    If Len(Trim(strTemp)) = 0 Then
        If MsgBox("Do you want to skip this directory after all?", 4+32) = 6 Then
            getInput = "*SKIPDIRECTORY*"
            Exit Function
        Else
            MsgBox "Please re-enter the prefix you want to add to the file names."
        End If
    Else
        'some minimal validation for illegal characters
        If (InStr(1, strTemp, "/") > 0) Or (InStr(1, strTemp, "") > 0) Or _
          (InStr(1, strTemp, "*") > 0) Or (InStr(1, strTemp, "?") > 0) Or _
          (InStr(1, strTemp, "|") > 0) Or (InStr(1, strTemp, """") > 0) Or _
          (InStr(1, strTemp, " 0) Or (InStr(1, strTemp, ">") > 0) Or _
          (InStr(1, strTemp, ":") > 0) Then
            MsgBox "Please revise the prefix so it does not contain illegal characters."
        Else
            getInput = strTemp
            Exit Do
        End If
    End If
Loop

Hope this helps.

Reply | Quote

This is along the lines of John’s suggestion, but for Office 2000 VBA or later.

Function CleanFileName(strFileName As String) As String
Dim Invalids, e, strTemp As String
Invalids = Array(“?”, “*”, “:”, “|”, “”, “[“, “]”, “”, “/”)
strTemp = strFileName
For Each e In Invalids
strTemp = Replace(strTemp, e, “”)
Next
CleanFileName = strTemp
End Function

Any or all characters in the Invalids array are stripped from the string passed to the function.

Andrew C

Reply | Quote

This is along the lines of John’s suggestion, but for Office 2000 VBA or later.

Function CleanFileName(strFileName As String) As String
Dim Invalids, e, strTemp As String
Invalids = Array(“?”, “*”, “:”, “|”, “”, “[“, “]”, “”, “/”)
strTemp = strFileName
For Each e In Invalids
strTemp = Replace(strTemp, e, “”)
Next
CleanFileName = strTemp
End Function

Any or all characters in the Invalids array are stripped from the string passed to the function.

Andrew C

Reply | Quote

Hi Randall

Yet another way:

Function IsValidFileName(sFileName As String) As Boolean
If (sFileName Like "*[?*:/{}]*" Or _
    sFileName Like "*[[]*" Or _
    sFileName Like "*[]]*") Then
   IsValidFileName = False
Else
   IsValidFileName = True
End If
End Function

Reply | Quote

Hi Randall

Yet another way:

Function IsValidFileName(sFileName As String) As Boolean
If (sFileName Like "*[?*:/{}]*" Or _
    sFileName Like "*[[]*" Or _
    sFileName Like "*[]]*") Then
   IsValidFileName = False
Else
   IsValidFileName = True
End If
End Function

Reply | Quote

Here is yet another example, with option to replace invalid character with another character of your (not user’s) choosing. This takes into account that although periods are “legal” characters, a filename cannot consist of only periods (or, for that matter, an empty string); and that Windows will truncate any trailing periods not followed by another character or characters. StrConv function is used to convert string to and from an array of bytes. Code:

Public Function IsFileNameValid(ByRef strFileSpec As String, _
ByRef bReplace As Boolean, _
Optional ReplaceWith As String = “_”) As Boolean
On Error GoTo Err_Handler

Dim strMsg As String
Dim b() As Byte
Dim n As Long

IsFileNameValid = True

‘ Invalid FileName characters (XP):
‘ 92 / 47 : 58 * 42 ? 63 ” 34 62 | 124

‘ Trailing periods removed from file name:
If Len(Trim$(Replace(strFileSpec, “.”, vbNullString, , , vbBinaryCompare))) > 0 Then
‘ Remove any trailing periods:
Do Until Right$(strFileSpec, 1) “.”
strFileSpec = Left$(strFileSpec, Len(strFileSpec) – 1)
Loop

‘ Convert string to byte array:
b = StrConv(strFileSpec, vbFromUnicode)
For n = 0 To UBound(
Select Case b(n)
Case 34, 42, 47, 58, 60, 62, 63, 92, 124
IsFileNameValid = False
If bReplace Then
‘ Asc function evaluates 1st char only:
b(n) = Asc(ReplaceWith)
Else
‘ Not replacing character:
Exit For
End If
Case Else
‘ OK
End Select
Next
Else
IsFileNameValid = False
End If

‘ Optional – convert input string back to Unicode text (passed by ref):
If bReplace Then
strFileSpec = StrConv(b, vbUnicode)
Debug.Print strFileSpec
End If

Exit_Sub:
Exit Function
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “IS FILENAME VALID FUNCTION ERROR”
Resume Exit_Sub
End Function

Sample results:

? IsFileNameValid(“ABC…”,True)
ABC
True

? IsFileNameValid(“ABC?.d*c”,True)
ABC_.d_c
False

? IsFileNameValid(“”,True,”%”)
%htm.doc%
False

Note that AFAIK, brackets in filename are “legal”, while double-quotes (“) are not (at least in XP – see attached pic).

HTH

Reply | Quote

Here is yet another example, with option to replace invalid character with another character of your (not user’s) choosing. This takes into account that although periods are “legal” characters, a filename cannot consist of only periods (or, for that matter, an empty string); and that Windows will truncate any trailing periods not followed by another character or characters. StrConv function is used to convert string to and from an array of bytes. Code:

Public Function IsFileNameValid(ByRef strFileSpec As String, _
ByRef bReplace As Boolean, _
Optional ReplaceWith As String = “_”) As Boolean
On Error GoTo Err_Handler

Dim strMsg As String
Dim b() As Byte
Dim n As Long

IsFileNameValid = True

‘ Invalid FileName characters (XP):
‘ 92 / 47 : 58 * 42 ? 63 ” 34 62 | 124

‘ Trailing periods removed from file name:
If Len(Trim$(Replace(strFileSpec, “.”, vbNullString, , , vbBinaryCompare))) > 0 Then
‘ Remove any trailing periods:
Do Until Right$(strFileSpec, 1) “.”
strFileSpec = Left$(strFileSpec, Len(strFileSpec) – 1)
Loop

‘ Convert string to byte array:
b = StrConv(strFileSpec, vbFromUnicode)
For n = 0 To UBound(
Select Case b(n)
Case 34, 42, 47, 58, 60, 62, 63, 92, 124
IsFileNameValid = False
If bReplace Then
‘ Asc function evaluates 1st char only:
b(n) = Asc(ReplaceWith)
Else
‘ Not replacing character:
Exit For
End If
Case Else
‘ OK
End Select
Next
Else
IsFileNameValid = False
End If

‘ Optional – convert input string back to Unicode text (passed by ref):
If bReplace Then
strFileSpec = StrConv(b, vbUnicode)
Debug.Print strFileSpec
End If

Exit_Sub:
Exit Function
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “IS FILENAME VALID FUNCTION ERROR”
Resume Exit_Sub
End Function

Sample results:

? IsFileNameValid(“ABC…”,True)
ABC
True

? IsFileNameValid(“ABC?.d*c”,True)
ABC_.d_c
False

? IsFileNameValid(“”,True,”%”)
%htm.doc%
False

Note that AFAIK, brackets in filename are “legal”, while double-quotes (“) are not (at least in XP – see attached pic).

HTH

Reply | Quote