CODEWITHSUNDEEP
excelvba
Guy Hodges
Guy Hodges

Reputation: 157

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

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?

Upvotes: 4

Views: 4165

Answers (2)

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

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.

enter image description here

enter image description here Sample test

enter image description here

Upvotes: 3

Rajnikant Sharma
Rajnikant Sharma

Reputation: 606

Here is a function i used to keep/ force to stop auto formating

Sub spcChr
Dim i As Long
Dim num As Integer
Dim x() As Byte
x = StrConv([A1], vbFromUnicode)
For i = 0 To UBound(x)
Select Case x(i)
    Case 211: num = 79
    Case 63: num = 83
    Case Else: num = x(i)
End Select
[B1] = [B1] & Chr(num)
Next
End Sub

Here A1 cells containing the names with special characters. B1 is for processing.

Upvotes: 0

Related Questions