Strip a filename string of any disallowed characters

[lennyBaby]

I am writing some search results to an excelsheet for reports using the time and date functions to build a filename. I downloaded a StripSpecialCharacters() module, but it doesn't take them all out (only takes ascii above 127).

Does anybody know of another function to do it. searched the archive to no avail.

Thanks in advance

 

[WayneRyan]

Lenny,

Haven't given it much thought, but the function should be pretty easy.
Haven't run this code, but it should give you a start.

Public Function strGoodFileName(strInFileName As String) As String
Dim strTemp As String
Dim strChar As String
Dim i As Long

strTemp = ""
For i = 1 To Len(strInFIleName)
   strChar = Mid(strInFIleName, i, 1)
   If (strChar >= "A" And _
       strChar <= "Z")      Or _
      (strChar >= "a" And _
       strChar <= "z")      Or _
      InStr(1, " _-", strChar) > 0 Then  ' <-- Just add to the list " _-(){}"
    strTemp = strTemp & strChar         '     any other characters to keep.
   Else                                 '     Personnally I hate special characters.
    strTemp = strTemp & "_"  ' <-- Remove the else clause to discard special chars
   End If                          Here, they're just replaced with underscores.
   Next i

strGoodChars = strTemp

End Function

Wayne

 

[lennyBaby]

Thanks alot Wayne - Will give it a try

 

[MarkK]

Remove Special Characters

Here's another crack at the problem that allows you to specify what characters you want removed. The VBA.Strings.Split() function is not available in earlier versions of Access.

Public Function ReplaceChars(ByVal inString As String, Optional replaceWith As String = "") As String
  Dim vDisallowed As Variant
  Dim i As Integer

[COLOR="SeaGreen"]  'construct variant array of disallowed chars, which are space delimited[/COLOR]
  vDisallowed = Split("! @ # $ % ^ & * """, " ")
   
[COLOR="SeaGreen"]  'traverse this array, removing any of it's members from the string[/COLOR]
  For i = 0 To UBound(vDisallowed)
    inString = Replace(inString, vDisallowed(i), replaceWith)
  Next i
  
  ReplaceChars = inString
  
End Function

 

[WayneRyan]

Lag,

Nice function!

Wayne

 

[MarkK]

Thanks Wayne
Cheers,
Mark