How To Replace Accented Characters With Regular Latin Characters

How To Replace Accented Characters With Regular latin Characters ?

We got a request from a client, to convert the accented characters of multiple Excel files to plain Latin. Excel VBA is great and really handy but there is no method to perform such a task using VB code. On the other hand C# inherits all beautiful .Net libraries, and it’s so easy to solve the above problem. Why not bring the .Net world close to the “vintage” world of VBA?

So here is what we did. We prepared a method in C# that fixes the problem and then made it public to the VBA IDE:

				
					            string accentedStr = "Staré Město";
            byte[] tempBytes;
            tempBytes = Encoding.GetEncoding("ISO-8859-8").GetBytes(accentedStr);
            string asciiStr = Encoding.UTF8.GetString(tempBytes);
				
			

And from the Excel VBA IDE:
We registered the C# Library from Tools | References, and we added the following method:

				
					Public Sub Convert()
                    
    Application.Cursor = xlWait
    
    'Create Object from C#
    Dim obj As CSharpTools.CSharpTools
    Set obj = New CSharpTools.CSharpTools
        
    Dim CountRows As Long
    Dim ActiveCol
    ActiveCol = ActiveCell.Column
    Cells(1, ActiveCol).Select
    
    'Get the last row in the current region
    CountRows = Cells(1, ActiveCol).End(xlDown).Row
    
    Dim i As Long
    For i = 1 To CountRows
    
        'Convert each cell to Latin characters
        Cells(i, ActiveCol).Value = obj.ConvertFromUTF8ToLatin(Cells(i, ActiveCol).Value)
    
    Next i
    
    Application.Cursor = xlDefault
    
End Sub
				
			

With the above code we can convert a whole column at no time at all !

If you need assistance on converting your documents please use our Contacts Form, we would love to help ! 

 

latest posts

Automate log off process to improve the security of your computer

Hackers are criminals who gain unauthorized access to a network and devices, usually with the intent to steal sensitive data, such as financial information or company secrets.You can protect your...

Automate Creation of Serial Numbers In An Existing PDF Template

We received a request from a client, to automate the creation of Serial Numbers inside a PDF file.The PDF was a A3+ format and was designed in such a way...

How To Replace Accented Characters With Regular latin Characters ?

We got a request from a client, to convert the accented characters of multiple Excel files to plain Latin. Excel VBA is great and really handy but there is no...

Hafnium Hack – How to run the patches, with simple detailed instructions.

Why make such a “How to”? Because all articles we found so far, talk about what to do, but none shows how to do it.So let’s start. 1. HOW TO KNOW IF YOU...