Import text file as UTF-8

[tabrizbalasi]

I am using the following vba code to import all txt files in the active directory into excel.

Sub ImportTXTfiles()
Dim Path As String

Path = Application.ActiveWorkbook.Path & "\"
Filename = Dir(Path & "*.txt")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy after:=ThisWorkbook.Sheets("Sheet1")

Next Sheet
Workbooks(Filename).Close
Filename = Dir()

Loop

End Sub

However my txt files are in UTF-8 character encoding. How can I import them using the above code but in UTF-8 code?

I tried to use this:

With CreateObject("ADODB.Stream")
      .Charset = "utf-8" 'Specify charset For the source text data.
End With

but without success. Your help is appreciated.

Could you please help me to modify this formula to import txt files in utf-8? thanks

 

[pgc01]

Hi
Welcome to the board

This is an example. I read the first 10 characters of a file with UTF-8 format.

In the <acronym title="visual basic for applications">vba</acronym> editor set the reference to "Microsoft Active Data Objects 6.1 Library"

Sub TestR_utf_8()
Dim st As ADODB.Stream
Dim sPathname As String, sText As String

sPathname = "c:\tmp\test_utf-8.txt"

' create a stream object
Set st = New ADODB.Stream

' set properties
st.Charset = "utf-8"
st.Type = adTypeText

' open the stream object and load the text
st.Open
st.LoadFromFile (sPathname)

' read 10 characters
sText = st.ReadText(10)

' display the characters read
MsgBox sText

st.Close
Set st = Nothing
End Sub

 

[Rocko262c]

Dear Tabrizbalasi,

Were you ever able to accomplish this task? I have another post on MrExcel titled "Help with .txt files saved as UTI-8, I need to import them into excel with Origin:=65001 (UTF-8)".

Pgc01 was kind to share his same code, but I was trying to accomplish something more along of the lines that you were trying to do.

Any help is greatly appreciated.

CHeers,
Rocko262C