Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid Charaters (VBA)

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Invalid Charaters (VBA)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Invalid Charaters (VBA)

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

    <pre>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</pre>

    Hope this helps.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Invalid Charaters (VBA)

    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

  5. #5
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid Charaters (VBA)

    Hi Randall

    Yet another way:
    <pre>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
    </pre>


  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Invalid Charaters (VBA)

    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 < 60 > 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([img]/forums/images/smilies/cool.gif[/img]
    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("<htm.doc>",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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •