How to import Chinese language data from a text file into excel using VBA?

[Dheeraj]

Hi,

I have a few text files (.txt) which have data in English and Chinese languages. I am required to consolidate these files.

I am able to do that successfully however; while reading Chinese language data and pasted it to Excel, Chinese alphabets becomes some special characters i.e. do not remain in Chinese anymore.

I have also tried to save text files in Unicode/UTF8 format but no success.

When I manually open that file and copy and paste this data into excel, it doesn't change to special characters.

Please help me out what wrong am I doing? Is there any specific technic available to handle such scenario?

Below is the code I am using to read a text file and pasting content to excel:

Sub Copy_Txt_File()

Dim FSO As New FileSystemObject 'Tools->Microsoft Scripting RunTime
Dim Obj As New DataObject 'Microsoft Forms 2.0 object library

Dim TextFile As File 'Object to hold Text File
Dim FileText As String 'Variable to hold Text File's content

Set TextFile = FSO.GetFile("D:\China data.txt")

FileText = TextFile.OpenAsTextStream.ReadAll

Obj.SetText FileText ' Copy text stream in object
Obj.PutInClipboard 'Place text stream in Clipboard
ActiveSheet.Paste 'Access clipboard text

End Sub

Thanks,
Dheeraj

 

[Marc L]

Hi !

When you open this text file under a serious text editor like Notepad++,
what is its characters code format ?

Try with ADOSB.Stream instead of FSO …
(samples on forum and help on MSDN site)

Start also with a web search on Chinese under Excel …
​

 

[Chihiro]

If you don't need to loop through many files. It's simple enough to use "Get External Data"->"From Text".

You will see something like below, telling you about encoding.

 

[Dheeraj]

Hi !

When you open this text file under a serious text editor like Notepad++,
what is its characters code format ?

Try with ADOSB.Stream instead of FSO …
(samples on forum and help on MSDN site)

Start also with a web search on Chinese under Excel …
​

Thanks Marc L for your response however; I could not use it. I added "Microsoft ActiveX Data Objects 2.5 library" and tried but didn't get using it. Also, tried to read about it on Google but felt difficult to understand the examples. (http://www.motobit.com/tips/detpg_read-write-binary-files/)

Could you please give me a simple example. or modify the code I had mentioned in my first post.

Thanks,
Dheeraj