I try to read mutiple txt files with Excel VBA and store their file name and content in an array. However I have a hard time with character sets, since the txt file can contain a variety of languages. Is there a charset that supports all languages or how can I solve the below described problem?
Here is the code I have
Function create_Txt_Content_Array(file_count As Integer, path As String, Optional strType As String) As String()
Dim createArray() As String
Dim file As Variant
Dim read_file As Integer
Dim absolut_path As String
Dim i, j As Integer
Dim text_content As String
Dim objStream
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
ReDim createArray(file_count - 1, 1)
If Right(path, 1) <> "\" Then path = path & "\"
file = Dir(path & strType)
absolut_path = path & file
j = 0
While (file <> "")
objStream.Open
objStream.LoadFromFile (absolut_path)
text_content = objStream.ReadText()
objStream.Close
createArray(j, 0) = file
createArray(j, 1) = text_content
Debug.Print (text_content)
i = i + 1
j = j + 1
file = Dir
absolut_path = path & file
Wend
Set objStream = Nothing
End Function
- The first file contains Portuguese: no problem
- The second file contains English: no problem
- The third file contains Hindi: not working
There are other languages like Korean, Japanese and others following.
