|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Difficulties with replacing certain language-specific characters using VBA.
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: Code:
Cells.Replace What:="e", Replacement:="č", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False Code:
Cells.Replace What:="?", Replacement:="č", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False |
#2
|
||||
|
||||
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: Code:
Cells.Replace What:=&H00E8, Replacement:=&H010D, LookAt:=xlPart, SearchOrder:=xlByRows _ , MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False Code:
Cells.Replace What:=sWhat, Replacement:=sRepl, LookAt:=xlPart, SearchOrder:=xlByRows _ , MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 Quote:
Bob's suggestion Code:
Cells.Replace What:=&H00E8, Replacement:=&H010D, LookAt:=xlPart, SearchOrder:=xlByRows _ , MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False Code:
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. |
Tags |
characters, replacing, vba |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
A Macro to break lines automatically after specific number of characters | Annie Wordy | Word VBA | 1 | 11-03-2017 08:36 PM |
Deleting Characters in a specific location in Word 2010 | ppayaw | Word VBA | 8 | 12-13-2016 08:11 AM |
Customize grammar settings for specific language? | jpgauvin | Word | 0 | 12-05-2016 03:06 PM |
How do I Remove A Specific Amount of Characters from Lines in a Document? | tatihulot | Word | 5 | 01-22-2016 05:55 AM |
Extract Line of Text w/ specific characters up to the paragraph character, send to Excel | dmarie123 | Word VBA | 10 | 07-20-2015 12:16 AM |