Difficulties with replacing certain language-specific characters using VBA.

[Franci]

In excel, I need to process data that is prepared and exported from another application in the .xls file format.
Because certain language-specific characters in this .xls file are not displayed properly (eg "è" instead of "č"), I wanted to replace them with the help of a macro. So I recorded a macro, which should replace, for example, all "è" characters with the "č" character. There was no problem while recording the macro and all the characters were replaced correctly. But running the same macro on another .xls file caused a problem because instead of all "è" characters, all "e" characters were replaced with "č" character.
This is part of a code I recorded:

Cells.Replace What:="e", Replacement:="č", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

The recorded VBA code shows that the character to be replaced is not correctly displayed and is actually "e" and not "è". Therefore, I tried to paste the character "è" into the code instead. Unfortunately, this was not successful because after pasting, a question mark ("?") appeared.

Cells.Replace What:="?", Replacement:="č", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

How can I make a macro that will replace characters correctly?

[BobBridges]

Franci, just to be clear, have you tried running the program since you pasted "è" into the program and saw it recorded as "?"? I ask because it's just possible the VBA Editor doesn't know how to display such characters on the screen but nevertheless stores them correctly. You could try running it and see what happens.


But that's just a thought; worth trying but I don't really believe it. If I were attempting this, the next thing I'd try is to use hex constants in your code, like this:

Cells.Replace What:=&H00E8, Replacement:=&H010D, LookAt:=xlPart, SearchOrder:=xlByRows _
  , MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

If that doesn't work, you might have to set two string variables to the What and Replacement values and put them in the code instead:

Cells.Replace What:=sWhat, Replacement:=sRepl, LookAt:=xlPart, SearchOrder:=xlByRows _
  , MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

One problem is that the "č" character requires an extra byte (is that what they call "Unicode"?); I'm not sure how you represent that in VBA. You might have to examine the value where it appears in a worksheet and see what hex code is being used under the covers.

[ArviLaanemets]

The function CHRW(UnicodeValue) in VBA returns Unicode character;
The function UNICODE(TextString) in VBA returns Unicode value of 1st character in TextString.

You can use REPLACE() in VBA to replace one Unicode/ASCII character in call with another one;
Or you can give an ReplaceAll command to replace one Unicode/ASCII character on worksheet with another one.

So general approach for VBA procedure will be:
Create 2-dimensional array with Unicodes for characters to be replaced and to replace to;
a) Cycle through all cells in used area in worksheet. For every cell cycle through array to replace the character;
b) Cycle through array. For every character to be replaced invoke ReplaceAll for worksheet.

[Franci]

Hi Bob and Arvil!
I apologize for the late reply, but this weekend I was absent and I was not at my computer.

Thank you for your for your suggestions that have been a shift in the right direction to solve my problem.


Bob, the answer to your question

Franci, just to be clear, have you tried running the program since you pasted "è" into the program and saw it recorded as "?"? I ask because it's just possible the VBA Editor doesn't know how to display such characters on the screen but nevertheless stores them correctly. You could try running it and see what happens.

I did not mention it, but I tried this before. This replaces all the characters with the "č" sign.


Bob's suggestion

Cells.Replace What:=&H00E8, Replacement:=&H010D, LookAt:=xlPart, SearchOrder:=xlByRows _   , MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

didn't work for me, but after I read Arvil post I came up with

Cells.Replace What:=ChrW(&HE8), Replacement:=ChrW(&H10D), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

This does the job, so I can continue from here.



Thank you both.