VBA Read accented characters for Input

[bettlejus]

Hi all,

I have the following code which I use to read TXT file name/file content to excel file from a location.

The problem is that I have some accented characters in TXT files and they end up in excel as "Ä" - "È™" - ""

How can I modify the code to specify the encoding or solve this problem?

Sub TxtCopy()
    
    Dim sPath As String
    Dim iRow As Long
    Dim strString  As String
    Dim strString1  As String
    Dim strString2  As String
    
    Dim fso As FileSystemObject
    Dim xFile As File
    Dim xFolder As Folder
    Dim Location As Variant
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Location = InputBox("Introduceti calea fisierelor TXT")
    
    Set xFolder = fso.GetFolder(Location & "\")
    
    iRow = 1 ' Row to start inserting data
    
    For Each xFile In xFolder.Files
            
        If InStr(1, xFile.Name, ".txt") <> 0 Then
        
            Dim lFile As Long
            Dim szLine As String
            
            lFile = FreeFile()
            
            Open xFile.Path For Input As lFile
            
            strString = ""
            While Not EOF(lFile)
            
                Line Input #lFile, szLine
            
                ' Concatenete lines from text file
                strString = strString & szLine & vbCrLf
                strString1 = xFile.Name
                strString2 = xFolder.Name
                                
            Wend
            
            ' Add to cell
            Cells(iRow, 2).Value = strString
            Cells(iRow, 1).Value = Left(strString1, Len(strString1) - 4)
            Cells(iRow, 3).Value = strString2
            
            
            iRow = iRow + 1
            
            
            ' Close the file
            Close lFile
            
            Application.ScreenUpdating = True
                        
        End If
        
    Next ' End of LOOP
    
    MsgBox "Completed!"
End Sub

 

[bettlejus]

I tried to read the file with ADODB>Stream in UTF-8 enconding:

Dim objStream, strData
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8=bom"
objStream.Open
objStream.LoadFromFile ("C:\ACC_VBA\descriere.html")
strData = objStream.ReadText()
MsgBox strData

But till my characters are replaced with question marks ??

I have romania accented characters in LATIN10 format or ISO 8859-16....I don;t see any wah to define the 8859 charset in ADODB.Stream.

Please help!