Illegal File Names

17 thoughts on “Illegal File Names

  1. Function IsValidFileName(sFileName As String) As Boolean
    IsValidFileName = Not (sFileName Like “[/:*?”“<>|]”)
    End Function
  2. @JP –

    That’s not quite right. This will do it:

    Function IsValidFileName(sFileName As String) As Boolean
        IsValidFileName = Len(Trim(sFileName)) > 0 * Not (sFileName Like “*[/:*?<>|”“]*”)
    End Function

    It won’t work without the asterisks. This version also rejects the case where sFileName is null, as well as the case where it contains nothing but space characters.

    Respectfully,

    Daniel Ferry
    excelhero.com

  3. @JP,

    You forgot the wildcards (asterisks)…

    Function IsValidFileName(sFileName As String) As Boolean
      IsValidFileName = Not sFileName Like “*[/:*?”“<>|]*”
    End Function
  4. @Daniel,

    Good point about the empty string; however, I think you accidentally wrote an asterisk instead of the operator “And”…

    Function IsValidFileName(sFileName As String) As Boolean
      IsValidFileName = Len(Trim(sFileName)) > 0 And Not (sFileName Like “*[/:*?<>|”“]*”)
    End Function
  5. Hi, Rick.

    Quite right. I often use the Multiplication operator instead of AND, but I forgot to enclose the first term in parentheses. It meant this:

    Function IsValidFileName4(sFileName As String) As Boolean
        IsValidFileName4 = (Len(Trim(sFileName)) > 0) * Not (sFileName Like “*[/:*?<>|”“]*”)
    End Function

    Daniel Ferry
    excelhero.com

  6. There are other names that are illegal, but not because of any single character in the name:

    PRN, CON, LPT#, COM#, AUX, NUL

    I’ve found that the best thing to do is to check for errors after the Save/SaveAs to see if it worked.

  7. @Dave,

    Maybe this function will handle all the possibilities…

    Function IsValidFileName(sFileName As String) As Boolean
      IsValidFileName = Len(Trim(sFileName)) > 0 And _
                        Not (sFileName Like “*[/:*?<>|”“]*”) And _
                        Not (UCase(sFileName) Like “COM[1-9]”) And _
                        Not (UCase(sFileName) Like “LPT[1-9]”) And _
                        InStr(1, “PRN*CON*AUX*NUL*”, “*” & sFileName & “*”, vbTextCompare) = 0
    End Function
  8. Maybe. But as for me, I’ve decided that I’m not going to try to check to see if it’s a legal file name. I’ll just do the save/SaveAs and check to see if it was successful.

    Even if the name is legal, the save can fail for lots of reasons, so I’d have to check anyway.

    Well, in real production code — never for myself .

  9. ps. There was a vbg in brackets that was in that last response.

    I don’t know nothing about birthin’ no babies, er, HTML.

  10. Why not just let the OS decide this for you? After all, on Macs, which still do run Excel, filenames MAY contain almost any character. So something like

    Function foo(cfn As String) As Boolean
      Dim tpn As String
     
      tpn = Environ(“TEMP”)
      If tpn = “” Then tpn = Environ(“TMP”)
      If tpn = “” Then tpn = “.”
      tpn = tpn & “” & Format(Now – Int(Now), “.00000000”)
      MkDir tpn
     
      On Error GoTo AllDone
      MkDir tpn & “” & cfn
      ‘only when the previous line succeeds would the next 3 lines run
     RmDir tpn & “” & cfn
      RmDir tpn
      foo = True

    AllDone:
    End Function

  11. Ugh! I mention Macs, then use backslash as directory separator! Change the “” to “/” since Windows’ API is perfectly happy using / and interchangeably.

  12. I thought this little exercise was instructive, and so I worked on it a little more.

    – I shortened the COM and LPT strings.
    – The max filename length that Windows Explorer allows is 241 characters. Maybe this can be increased to 255 characters from code. I wasn’t sure so I limited max length to 241.
    – ASCII characters from 0 to 31 are illegal, just like the symbols we’ve previously been excluding, so I added them to the string of illegal characters:

    Function IsValidFileName(sFileName As String) As Boolean
      Dim sBadChars As String
     
      sBadChars = Chr$(0) & “-“ & Chr$(31) & “/:*?<>|”“”
      IsValidFileName = Len(Trim(sFileName)) > 0 And Len(sFileName) < 242 And _
                         Not (sFileName Like “*[“ & sBadChars & “]*”) And _
                         Not (UCase(sFileName) Like “COM#”) And _
                         Not (UCase(sFileName) Like “LPT#”) And _
                         InStr(1, “PRN*CON*AUX*NUL*”, “*” & sFileName & “*”, vbTextCompare) = 0
    End Function

    In its current state the function is testing for a good number of illegal conditions. I’m sure there’s more…

    Regards,

    Daniel Ferry
    excelhero.com

  13. You omitted an asterisk in front of “PRN” in the InStr function call. The code should be this…

    Function IsValidFileName(sFileName As String) As Boolean
      Dim sBadChars As String
     
      sBadChars = Chr$(0) & “-“ & Chr$(31) & “/:*?<>|”“”
      IsValidFileName = Len(Trim(sFileName)) > 0 And Len(sFileName) < 242 And _
                         Not (sFileName Like “*[“ & sBadChars & “]*”) And _
                         Not (UCase(sFileName) Like “COM#”) And _
                         Not (UCase(sFileName) Like “LPT#”) And _
                         InStr(1, “*PRN*CON*AUX*NUL*”, “*” & sFileName & “*”, vbTextCompare) = 0
    End Function
  14. Dear All,

    I have another question (sorry, this is not relative with this topic) that:

    _ I have opened two workbooks with difference sessions (Ctrl + Alt + Delete to check, and see two Excel.exe sessions).

    How can I check this workbook was openned. It’s something like the following function:

    Function bWorkbookIsOpen(rsWbkName As String) As Boolean
    On Error Resume Next
      bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0)
    End Function

    Just pass the workbook name to this function.

    Thanks,

    Le Van Duyet


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.