Change invalid characters in file names VBA

Question

There is code below that I used to change ".." to ".". For example I have a name of file like "file..pdf" and I would like to have "file.pdf", but It removes all dots. I don't know how to change it:

Function strLegalFileName2(ByVal FName) As String
Dim i As Integer

Const strIllegals = "*&..&*"
strLegalFileName2 = FName
For i = 1 To Len(strIllegals)
    strLegalFileName2 = Replace(strLegalFileName2, Mid$(strIllegals, i, 1), ".")
Next i
End Function

Sub LoopThroughFiles2()
Dim FName As Variant
Dim strNew As String
Dim strDir As String

strDir = "path"
FName = Dir(strDir & "*..*")
Do While Len(FName) > 0
    strNew = strLegalFileName2(FName)
        If StrComp(strNew, FName) <> 0 Then Name (strDir & FName) As (strDir & strNew)
    FName = Dir
Loop
End Sub

It doesn't work yet. No result. – user3114375 May 19 '15 at 08:48 — user3114375, May 19 '15 at 08:48
Why don't you use `Replace(strLegalFileName2, "..", ".")` – PaulFrancis May 19 '15 at 08:50 — PaulFrancis, May 19 '15 at 08:50

score 0 · Answer 1 · answered May 19 '15 at 09:23

Because you go through all character in your file name:

For i = 1 To Len(strIllegals)

Therefore it will remove all "." in your file name.

Just simple use:

Function strLegalFileName2(ByVal FName) As String
Dim i As Integer

Const strIllegals = "*&..&*"
strLegalFileName2 = FName

strLegalFileName2 = Replace(strLegalFileName2, "..", ".")

End Function

score 0 · Answer 2 · answered May 19 '15 at 10:46

If you want to change some special characters to some other characters I suggest you to use this function:

Function strLegalFileName2(ByVal FName) As String
Dim strIllegal() As String
Dim i As Integer

Const strIllegals = "..@=>@."

strIllegal = Split(strIllegals, "@|@")

For i = LBound(strIllegal) To UBound(strIllegal)
    FName = Replace(FName, Mid(strIllegal(i), 1, InStr(1, strIllegal(i), "@=>@") - 1), Mid(strIllegal(i), InStr(1, strIllegal(i), "@=>@") + 4))
Next i

strLegalFileName2 = FName
End Function

For more illegal you can change strIllegals = "..@=>@." to something like strIllegals = "..@=>@.@|@&@=>@ AND " that will change & to AND.

Change invalid characters in file names VBA

2 Answers2