Save As: change all control characters to an underscore

[ARbitOUR]

Hi all!

Once again I turn to this wonderful forum for help...

I'm busy working on a 'Save As:' macro (using a button) that selects
values from certain cells and then use these vlaues as the file name in
which the file will be saved. This macro will be displaying a userform
with option bottons. The option buttons will display the various
recommended file names of which the end-user will have to select one.

The problem is that from time to time the end-user will need to use
control characters in the applicable cells which determine the file
name. I need a macro that will automatically change all control
characters to an underscore ("_") within the file name (when hitting the
"save" button / control I have created). However, the macro I need must
NOT replace the control characters in the actual cells themselves.

As I understand control characters are the following:
/ \ ? % * : | " < > .

So obviously the macro I need must change all these characters to
underscores.

SOS...SOS...SOS...SOS...SOS

 

[OssieMac]

I think that the following will do what you want. Test it carefully with your
actual data.

Sub Test()

Dim strSource As String
Dim strChrsToReplace As String
Dim intLgthReplace As Integer
Dim i As Integer

strSource = Range("A1")

'Note:Chr(34) concatenated in string for double quote.
strChrsToReplace = "/ \ ? % * : |" & Chr(34) & "< > ."

'Remove any spaces in the string of characters to replace.
strChrsToReplace = Replace(strChrsToReplace, Chr(32), "")

intLgthReplace = Len(strChrsToReplace)

For i = 1 To intLgthReplace
If InStr(strSource, _
Mid(strChrsToReplace, i, 1)) > 0 Then
strSource = Replace(strSource, _
Mid(strChrsToReplace, i, 1), "_")
End If
Next i

Range("A2") = strSource 'Used to test result

 

[ARbitOUR]

Thx!

That helped!