Solved: VBA uses wrong Alt codes !!

[Aquinax]

I've created a macro which replaces signs \|:/*?<> (which cannot be used in file names) with other characters. VBA is to use alt codes for that task and most characters are being replaced ok, yet some characters VBA puts are, as I've noticed, alien to the standard Alt codes table, Thus the character '¿' with the alt code 0191 is being typed by the VBA as ' ї ' and '►' as '+'.

The issue here, as I've figured out, lies in the cyrillic keyboard layout which the VBA for some reasons utilizes for alt codes (^0191, ^0192, ^0193, ^0194 are referenced by " ї, A, Б, В, Г " in the Cyrillic alt code table instead of " ¿, └, Á, Â, Ã " in the Western type). I've had a Russian keyboard layout installed, yet VBA still types the characters incorrectly even after I've removed it, Word itself (I'm using the 2003 version) fetches the correct characters (in compliance with the Western keyboard type).

Can the VBA be somehow 'told' to use the Western type layout ?? How else can it possibly be made to use right alt codes ?

[SamT]

That might be a windows Control Panel setting. I don't have international installed, So I can't be sure.I do know that Excel uses the OS differently than any other MS Office program.

[Aquinax]

Thanks, SamT,

The reason for the mistyping on the part of VBA was "language for non-Unicode programs" in "Regional and Language options" which was set for Russian language. Having switched it to English, the characters produced by the VBA engine reverted back to the Latin characters.

I also found out that the command 'ChrW(####)' used in VBA to produce alt code characters is more consistent and reliable than '^####', though unfortunately I have not figured out how to type more than one character with the help of it. So if one knows how to do that, and is kind enough to share the knowledge, please do so.

[SamT]

How about
Dim X As String
X = ChrW(1234)
X = X & ChrW(2334)

[Aquinax]

How about
Dim X As String
X = ChrW(1234)
X = X & ChrW(2334)


It's by no means straightforward, but it works.

PS: This works as well: .Replacement.Text = ChrW(39) & ChrW(139) & ChrW(1139)

[SamT]

Sub Test()
Dim BadFName As String
Dim GoodFname As String

BadFName = "Chuck>Baker.xls"
GoodFname = ConvertFileNameCharacters(BadFName)

End Sub

Function ConvertFileNameCharacters(FName As String) As String
Dim i As Long
Dim ch As String
Dim result As String

For i = 1 To Len(FName)
ch = Mid(FName, i, 1)
SelectCase ch
Case Chr("\") 'Replace with correct code
ch = ChrW("\") 'Replace with correct code
Case Chr("|")
ch = ChrW("|")
'Etc for all forbidden characters
Case Else
ch = ch
End Select
result = result & ch
Next i

ConvertFileNameCharacters = result
End Function