Remove special characters from file name (when saving)

[totalnovice2]

Hello.

I hope someone can help. I use the below code as part of a database which creates documents and then saves them using data from the form fields.

Every now and again one of the form fields contains a "special" character such as /,@,& and this prevents the file from saving.

Is there a simple addition to the below code to remove special characters from the file name if there is one present? It causes all sorts of issues as many people don't realise that there has been a problem until it is too late.

Fingers crossed someone can help

        objWord.ActiveDocument.SaveAs2 FileName:="C:\Users\Public\" & Forms![Front Page]![Site 2 Name] _
         & " " & Forms![Front Page]![Combo79] & " " & "O2" & ".doc"
 
         objWord.ActiveDocument.Close

 

[namliam]

Use the instr function to detect the special characters to prevent the save from happening.
When they are detected "prompt" a message saying these cant be used.

Alternatively, you can use the Replace function to replace any special chars by a default _ or some other character you choose.

Good luck

 

[totalnovice2]

Thank you for the reply. Do you have an example of the coding which I could use please?

I'm not sure of the correct format to use.

 

[gemma-the-husky]

use replace multiple times

dim filename as string
filename ="C:\Users\Public\" & Forms![Front Page]![Site 2 Name] _
& " " & Forms![Front Page]![Combo79] & " " & "O2" & ".doc"

replace(filename,"/","")
replace(filename,"?","")
replace(filename,"\","")

objWord.ActiveDocument.SaveAs2 FileName:= filename


if the character exists, all examples of it will be removed.

alternatively, you could remove any character that isn't an expected character, but this coding would be a bit more advanced.

 

[namliam]

What Dave said, or try typing Instr or Replace into the help and see how far you get.
Come back with any specific questions you have

 

[Mile-O]

Rather than use Replace() multiple times, how about this?

Public Function RemoveIllegalCharacters(ByVal strText As String) As String

    Const cstrIllegals As String = "\,/,:,*,?,"",<,>,|"
    
    Dim lngCounter As Long
    Dim astrChars() As String
    
    astrChars() = Split(cstrIllegals, ",")
    
    For lngCounter = LBound(astrChars()) To UBound(astrChars())
        strText = Replace(strText, astrChars(lngCounter), vbNullString)
    Next lngCounter
    
    RemoveIllegalCharacters = strText

End Function ' RemoveIllegalCharacters

 

[namliam]

Pft, showoff

 

[totalnovice2]

Thank you for these replies.

I will give it a go!

 

[Mile-O]

use replace multiple times

dim filename as string
filename ="C:\Users\Public\" & Forms![Front Page]![Site 2 Name] _
& " " & Forms![Front Page]![Combo79] & " " & "O2" & ".doc"

replace(filename,"/","")
replace(filename,"?","")
replace(filename,"\","")

objWord.ActiveDocument.SaveAs2 FileName:= filename

One problem with this is that if you replace the backslashes, the file path will no longer be valid. Just needs to be the actual file name.

 

[gemma-the-husky]

true - I was just thinking of slashes being an illegal special character he might want to take out. (or not thinking, as it turns out)