Read / Load UTF-8 text file with VBA

  • 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... :)




    Cheers
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Read / Load UTF-8 text file with VBA


    Hi Ger.


    I'm testing your code to import several .XML files coded in UTF-8 into excel 2007 spreadsheet with VBA, but the last XML line (</unit>) is not imported because there is not LF/CR in there. I mean, if the .XML file have 100 lines, only 99 cells are filled.


    Any idea how to bring that line too?


    Thanks!!!

  • Re: Read / Load UTF-8 text file with VBA


    I've got it


    Just change last line of code to:


    Range("A1").Resize(UBound(var_String) - LBound(var_String) + 1).Value = Application.Transpose(var_String) 'output array to activesheet.

  • Re: Read / Load UTF-8 text file with VBA


    Awesome, thanks for posting back your solution :)

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Read / Load UTF-8 text file with VBA




    just spent my whole day trying to solve this problem and luckily stumbled on to your post after checking scores of other articles. Thanks.

  • Re: Read / Load UTF-8 text file with VBA


    Glad it was useful for you.
    :cheers:

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________