Illegal windows characters

[colmac]

As I understand it, windows does not permit any of these chars in a file name (taken from the error message when saving a file with any one of these chars included)

\/:*?"<>|

I'm using vb Filecopy to rename files, and I need to ensure that none of these characters appear in the string sipplied. The code is therefore checking and replacing each (or any) illegal chr with a space (I want the length to remain as is.)

I'm using this code to do it

tmpstr1 = WorksheetFunction.Substitute(tmpstr1, "*", " ")

this works perfectly for all the characters except the quote. I entered it as

tmpstr1 = WorksheetFunction.Substitute(tmpstr1, """"", " ") *******5 " symbols **************

I get a Compile error saying Expected list separator or)

I've tried all combinations possible (I think), but failed. Do I need to enter a string code? Any help welcome.

Thanks

[HansV]

In VBA, it's more efficient to use the "native" VBA Replace function than WorksheetFunction.Substitute.
You need 4 double-quotes instead of 5:

tmpstr1 = Replace(tmpstr1, """", " ")

or you can use Chr(34) to specify the double-quote. I generally find that easier to read:

tmpstr1 = Replace(tmpstr1, Chr(34), " ")

[colmac]

Thanks

I switched to Replace as suggested (I don't think I'll ever learn this!!!)

Used the Chr(34) as suggested. Works perfectly. Thanks

Never tried 4 quotes. I assumed that I needed the outer two, then the literal quote had to be surrounded by two more. Four doesn't make sense to me. Again, I think I'll never learn this.

Thanks again


Colin

[HansV]

If you insert a double-quote in a quoted string, it confuses VBA:

tmpstr1 = "I use a " character in this string"

When VBA parses this, it considers "I use a " to be a complete string, then doesn't know what to do with the rest. The convention (which is admittedly arbitrary, you have to know it) is to double the double-quote:

tmpstr1 = "I use a "" character in this string"

This tells VBA that you didn't intend to end the string, but to insert a literal double-quote. So a string consisting only of a double-quote becomes

tmpstr1 = """"

But this is hard to read and easily leads to errors. So it's more convenient to use

tmpstr1 = Chr(34)

[colmac]

Ah!!! That makes sense when explained.

Thanks again

Colin