VBA to Change File Encoding ANSI to UTF8 – Text to Unicode

How to change Text encoding from ANSI to UTF8?

If there are multiple txt files or programatically you need to change the encoding of a file from ANSI to UTF8 or any other encoding, use the code in this page.

If it is just a one time task, use these manual steps.

  1. Open the txt in notepad or any text editor
  2. Click Menu -> File
  3. Choose SaveAs
  4. Browse path
  5. In dropdown near Save button, select UTF8
  6. Click Save

This will convert the opened file format from plain text to a unicode format.

VBA code to convert Text to UTF8 (.Txt to Unicode)

The unicode format is used in files that has non English characters in the data. To store non English or special characters in a text file, the encoding has to be changed from text to UTF.

Here is the code to change the encoding of a ANSI file.

Sub convertTxttoUTF(sInFilePath As String, sOutFilePath As String)
    Dim objFS  As Object
    Dim iFile       As Double
    Dim sFileData   As String
    iFile = FreeFile
    Open sInFilePath For Input As #iFile
        sFileData = Input$(LOF(iFile), iFile)
        sFileData = sFileData & vbCrLf
    Close iFile
    'Open & Write
    Set objFS = CreateObject("ADODB.Stream")
    objFS.Charset = "utf-8"
    objFS.WriteText sFileData
    'Save & Close
    objFS.SaveToFile sOutFilePath, 2   '2: Create Or Update
    Application.StatusBar = "Completed"
End Sub

This enables the system to store data in enhanced unicode format to hold the extra characters.

Where this UTF8 conversion can be useful?

When you download a xml data from any server that contains unicode characters, it is possible that the data is saved in ANSI format, if it is manually saved.

When you try to load a ANSI encoded xml file with special characters into Excel, it will give error. The process will stop with message “not able to load special characters”.

In such cases, the file can be converted into proper unicode format and then loaded into Excel.

External Refernce: 

  1. Here is another similar topic about ANSI text to Unicode conversion using VBA code.