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.

Function IsLegalFileName(ByVal str As String) As Boolean
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):

Dim strFileN As String
strFileN = Application.GetSaveAsFilename
If (IsLegalFileName(strFileN)) And (strFileN <> False) Then
ActiveWorkbook.SaveAs strFileN, xlNormal
End If

Usage (Outlook):

Dim strFileN As String
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 class ValidateFileNames
{
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)

Function ScrubFileName(stringToScrub As String) As String
' 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.

Function ScrubFileName(stringToScrub As String) As String
' 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

Code For Excel And Outlook