VBA: need to read accented characters from a text file and KEEP them

Question

I want to use VBA to read text files, extract relevant data and transer to an Excel file. This is the type of code I'm using:

Sub ReadText()
Dim myFile As String, textline As String
myFile = Application.GetOpenFilename()
Open myFile For Input As #1
LineNumber = 0
Do Until EOF(1)
    LineNumber = LineNumber + 1
    Line Input #1, textline
    'do stuff to extract the info I want and transfer to Excel
Loop
Close #1
End Sub

I'm living in a Spanish-speaking country so there is a lot of accented characters which I need to keep. The problem is that these accented characters are being converted to other characters. For example, what should be read as INGLÉS is read as INGLÉS

I've seen there are solutions for replacing accented characters, but I don't want to do this because the accents are still needed for what I want to do with the info in Excel.

Am I missing a format option somewhere?

Answer 1

Another way to do it is by using ADO.

Sub ReadUTFFile()
    Dim objADO As ADODB.Stream
    Dim varText As Variant

    Set objADO = New ADODB.Stream
    objADO.Charset = "UTF-8"
    objADO.Open
    objADO.LoadFromFile "C:\Users\pankaj.jaju\Desktop\utftest.txt"

    varText = Split(objADO.ReadText, vbCr)
    Range("A1").Resize(UBound(varText) - LBound(varText)).Value = Application.Transpose(varText)
End Sub

Note - Dont forget to reference Microsoft ActiveX DataObjects.

Sample test