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 ! 



Download your free CONVERSION TOOL

latest posts

Hyper-V How to Fix Virtual machine could not be started because the hypervisor is not running

After a client's power failure where the UPS failed to properly shutdown the Hyper-V Server, VMs were not able to start. See below for the error we got every time...

How to connect/remote Hyper-V Server using Server Manager from another Windows Server and view Task Scheduler

First we have to enable communication between the two servers.Connect to your Hyper-V Server and open powershell. Type the following two commands: Enable-PSRemoting Enable-WSManCredSSP -Role server From your Windows Server...

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...

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...