Replacing Reserved Characters in a Varible

I have a table in word where one of the cells in each row holds a name of a file to be created.

Currently my VB code goes through each row looking in this cell, gets the contents and stores this in a varible, opens a word template and then saves the template as whatever has been written in the cell.

No matter how many times i tell the users, they always forget they cannot use characters like ?\/<>: in this cell because they are not allowed in file names.  This obvisouly causes my code to error.

What i want it to do is check each character in the string for these reserved characters and if it finds one replace it with a hyphen so the code can carry on and save the template.

Im been messing around with InStr and Replace methods but im not getting very far.

Anyone able to help me?
LVL 2
jdc1944Asked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
Or as a slightly more compact version:

strSpecial = "?/\<>:"
For i = 1 to Len(strSpecial)
    strFile = Replace(strFile, Mid(strSpecial, i, 1), "-")
Next

Open in new window

~bp
0
 
Bill PrewCommented:
Given that you have a manageable set of characters to exclude, a simple REPLACE approach might work the best, for example:

strFile = Replace(strFile, "?", "-")
strFile = Replace(strFile, "\", "-")
strFile = Replace(strFile, "/", "-")
strFile = Replace(strFile, "<", "-")
strFile = Replace(strFile, ">", "-")
strFile = Replace(strFile, ":", "-")

~bp
0
 
jdc1944Author Commented:
Thank you, both of them do just the job
0
 
Bill PrewCommented:
Welcome.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.