I've been searching for a while and it doesnt appear to be all that easy to load a UTF-8 text file into Excel using VBA. Say for example, a text file containing Chinese Text, saved as UTF-8.
This is the simplest / quickest method that I could come up with - using an ADODB stream to do the work. There were other alternatives available, but all failed to work on some level, or were awfully complex to work with. Hopefully this is a bit easier and will be useful to someone somewhere someday...
- Public Sub Read_UTF_8_Text_File()
- 'ensure reference is set to Microsoft ActiveX DataObjects library (the latest version of it).
- 'under "tools/references"... references travel with the excel file, so once added, no need to worry.
- 'if not you will get a type mismatch / library error on line below.
- Dim adoStream As ADODB.Stream
- Dim var_String As Variant
- Set adoStream = New ADODB.Stream
- adoStream.Charset = "UTF-8"
- adoStream.LoadFromFile "C:\My-UTF8-TextFile.txt" 'change this to point to your text file
- var_String = Split(adoStream.ReadText, vbCrLf) 'split entire file into array - lines delimited by CRLF
- Range("A1").Resize(UBound(var_String) - LBound(var_String)).Value = Application.Transpose(var_String) 'output array to activesheet.
- End Sub