|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Function IsValidFileName(ByVal sFileName As String) As Boolean Dim vaIllegal As Variant Dim i As Long vaIllegal = Array("/", ":", "*", "?", "< ", ">", "|", """") IsValidFileName = True For i = LBound(vaIllegal) To UBound(vaIllegal) If InStr(1, sFileName, vaIllegal(i)) > 0 Then IsValidFileName = False Exit Function End If Next i End Function |
17 thoughts on “Illegal File Names”
Posting code? Use <pre> tags for VBA and <code> tags for inline.
IsValidFileName = Not (sFileName Like “[/:*?”“<>|]”)
End Function
@JP –
That’s not quite right. This will do it:
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
@JP,
You forgot the wildcards (asterisks)…
IsValidFileName = Not sFileName Like “*[/:*?”“<>|]*”
End Function
@Daniel,
Good point about the empty string; however, I think you accidentally wrote an asterisk instead of the operator “And”…
IsValidFileName = Len(Trim(sFileName)) > 0 And Not (sFileName Like “*[/:*?<>|”“]*”)
End Function
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:
IsValidFileName4 = (Len(Trim(sFileName)) > 0) * Not (sFileName Like “*[/:*?<>|”“]*”)
End Function
Daniel Ferry
excelhero.com
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.
@Daniel * Rick
Thanks, knew I forgot something.
@Dave,
Maybe this function will handle all the possibilities…
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
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 .
ps. There was a vbg in brackets that was in that last response.
I don’t know nothing about birthin’ no babies, er, HTML.
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
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
Ugh! I mention Macs, then use backslash as directory separator! Change the “” to “/” since Windows’ API is perfectly happy using / and interchangeably.
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:
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
You omitted an asterisk in front of “PRN” in the InStr function call. The code should be this…
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
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:
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
[…] found the obvious solution for this in the form of a simple function, but the discussion about it raises many questions regarding the […]
Ugh! I mention Macs, then use backslash as directory separator! Change the “” to “/” since Windows’ API is perfectly happy using / and interchangeably.