Replacing strange foreign characters in Excel 2000 VBA 1

[basil3legs]

I'm sure this has been asked many times before but I can't find any previous threads, so here goes...

I have a spreadsheet which imports data from webpages into an Access database. Before writing the data it goes through and replaces all the accented characters with the non-accented version, e.g. ö is replaced with o

However, some of the words imported have strange characters like ? in (capital C with an Acute accent over it in case it doesn't come up properly!!) which cannot be typed in Excel VBA and hence I cannot replace them. Also there are Turkish accented characters as well which I have the same problem with (e.g. ? and ?).

Is there any way of replacing these characters?

Thanks in advance.

 

[fumei]

and replace them with.....?

Gerry

 

[basil3legs]

the non-accented character (as previously stated) - C with an acute accent over it needs replacing with a C etc.

The 2 Turkish characters which are now ?s (they looked right when this was previewed before posting) were an S with a cedilla under it and a G with a curved line above it which need to be replaced with S and G respectively.

If I try a Replace(name,"?","C") where the ? is the C with the accent on, and try cutting and pasting the character into the statement, it just ends up with Replace(name,"C","C") which doesn't do anything.

 

[AMCCoder]

I've never had much luck with strings and the Chr(#) function, but I think it may help you here. I do most, if not all, of my VBA in Excel, but here's the info I have.

I think the best way to do this is to determine the UniCode character number for your special character. I'm sure there are some websites that may be able to provide the entire unicode list, but the following function (used in a blank workbook, please) will list all 65,535 UniCode characters (unless there are more UniCode Characters...) on the Active Worksheet. The following will not work on a Macintosh machine.

Sub WriteChars()
    Dim i As Long               'a counter
    Dim LastLong As Long        'var for setting the largest # to search
    Dim LongToStrStr As String   'a middleman string for helping

    
    LastLong = 65535           'according to help - the last Unicode char
    
    ActiveSheet.Cells(1, 1) = "Chr(#)"
    ActiveSheet.Cells(1, 2) = "Character"
    
    For i = 1 To LastLong
        LongToStrStr = i
        Application.StatusBar = "Out of 65535 - " _
            & LongToStrStr & " Complete..."
        ActiveSheet.Cells(i + 1, 1) = i
        ActiveSheet.Cells(i + 1, 2) = ChrW(i)
    Next
    Application.StatusBar = False
End Sub

Once you know the Unicode character code, you can use it in your replace function. Where I have specified numbers in the CharW(#) function, put your own UniCode character code in its place.

Sub Test()
    Dim TestStr As String
    
    TestStr = "PlayStation" + ChrW(174)
    MsgBox TestStr
    
    MsgBox Replace(TestStr, ChrW(174), ChrW(169))
End Sub

In the above example, the registered trademark symbol after "PlayStation" becomes the copyrighted symbol. In your program, you can, of course, replace the second ChrW(#) (i.e., ChrW(169)) with your "C" string. as in

MsgBox Replace(TestStr, ChrW(174), "C")

I don't know how useful this information may be to you, but I think using ChrW(#) will help you overcome the difficulties in using the World Wide Web.

Hope this helps,
Joseph

 

[MichaelRed]

The strangeness of it all is that these simply represent a different language (ore properly Code Page). Simply replacing the oddities with your concept of whay they might be could well just propagate the strangeness into a new dimension. Many languages use a similar "alphabet", many of which use the various character representations (includingthir accents) to actually change the character. As a (probably bad example) suppose that -in one of these languages- the two dots over the "A" meant that it was a "b". Just removing the two dots would return some (other) gibberish which would simply look like poor spelling or grammer or even just an example of the 100 monkeys ...



MichaelRed

 

[basil3legs]

AMCCoder, that looks like it will work fine, thanks (wasn't aware of the CHRW and ASCW functions).

Temporarily I was writing the text to a cell and then reading it back from another cell with a SUBSTITUTE formula in it which worked fine until Excel kindly loaded one of the webpages over it and every susequent string just got blanked!!

MichaelRed, that is true but once all the characters are useable straight within VBA I can then do a replace on the whole string as necessary.

The code is to do with football teams and the Turkish characters come up in teams like Besiktas (which should have a little curved line above the Ss). If some website did use, say ö to represent the Turkish S then I can just do a later replace of Beoiktao with Besiktas. The huge number of replace statements I am using take no time in comparison to the looking up of the webpages so a few extra makes no difference.