Validate Filenames
Validating File Names for use in VBA and Java applications
In Validating file names, blog author Ross Mclean shows a method for validating Windows file names. In Windows, certain characters are not allowed in file names. Here is a function version of his code, which you can drop into a standard code module and call whenever you need to validate a user-typed or system-generated filename before saving a file.
I've modified the original code ever so slightly; the asterisks just inside the quotation marks in Ross' code are unnecessary, if the Visual Basic Help System is to be believed.
If (str Like "[/\:*?""<>]") Then
IsLegalFileName = True
End If
End Function
Interestingly, the code is exactly the same in VBA and VB.NET, although in VBA you can leave off "ByVal" in the parameter list.
Usage (Excel):
strFileN = Application.GetSaveAsFilename
If (IsLegalFileName(strFileN)) And (strFileN <> False) Then
ActiveWorkbook.SaveAs strFileN, xlNormal
End If
Usage (Outlook):
Dim Msg As Outlook.MailItem
' set reference to selected mailitem
Set Msg = ActiveExplorer.Selection.Item(1)
' save attachment with chosen filename
strFileN = Inputbox("What filename do you want?")
If (IsLegalFileName(strFileN)) And (strFileN <> False) Then
Msg.Attachments.item(i).SaveAsFile strFileN
End If
Here is a version of the code written for Java, in case that's what you need. I included it in a class, but you can take the isLegalFileName function out and put it wherever you need.
{
public static void main(String[] args)
{
System.out.println(IsLegalFileName("Doc*"));
}
static boolean IsLegalFileName(String str)
{
// assume true
boolean isValid = true;
int i;
String[] badChars = {"/","\\",":","*","?","\"" ,"<",">"};
for (i = 0; i < badChars.length; i++)
{
if (str.indexOf(badChars[i]) >= 0)
{
isValid = false;
break;
}
}
return isValid;
}
}
Scrub filenames
In addition to validating a filename, you may also want to actively "fix" filenames that aren't usable. Here's a function that will do so.
Version 1 (Recommended)
' remove illegal characters from filenames
Dim newString As String
newString = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(stringToScrub, "|", ""), ">", ""), "<", ""), Chr(34), ""), "?", ""), "*", ""), ":", ""), "/", ""), "\", "")
ScrubFileName = newString
End Function
Version 2 (Not Recommended)
This version of the scrubbing function includes a loop, and is about three to five times slower than the nested Replace function above. It is included for demonstration purposes only, to show you how you can look for a string inside an array.
' remove illegal characters from filenames
Dim newString As String
Dim i As Long
Dim tempString()
tempString = Array("\", "/", ":", "*", "?", Chr(34), "<", ">", "|")
For i = 1 To Len(stringToScrub)
If UBound(Filter(tempString, Mid$(stringToScrub, i, 1))) = -1 Then
newString = newString & Mid$(stringToScrub, i, 1)
End If
Next i
ScrubFileName = newString
End Function
