Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Save as Text with Unicode and Tab Delimited

This is a discussion on Save as Text with Unicode and Tab Delimited within the Excel Questions forums, part of the Question Forums category; Hello, In my excel file I have chinese and some other special characters and many cells have text with commas. ...

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Save as Text with Unicode and Tab Delimited

    Hello,

    In my excel file I have chinese and some other special characters and many cells have text with commas.

    My problem starts when I try to save my file as a Text.
    When I Save As with the Unicode option I can see the chinese characters in my text file but also I see a lot of quotes """ because I have commas in my xls file.
    When I Save As with Tab Delimited option to solve the comma problem, the chinese characters become ????

    It seems that cannot be possible to do it manually cause there is no option to Save As with Unicode and Tab option together.

    So I would deeply appreciate someone could give me a solution with VBA code to save my file as Text with Unicode and Tab Delimited option.

    thanks in advance for your kind help.
    regards,

    PS. I searched the forum but I haven't found a situation similar to mine.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Save as Text with Unicode and Tab Delimited

    If you want to eliminate """ then can't you just replace """ with nothing via Text editor ?

    Am I missing something ?

  3. #3
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Re: Save as Text with Unicode and Tab Delimited

    thank you jindon for your reply.

    if I replace the quotations with nothing then I will have more fields (columns) in my text file (this because of the information with commas in the cells of my excel file)

    for example my excel file is like this
    in row 1 are my headers
    a1:char b1:pinyin c1:description
    -------------------------------------------------
    a2:我们 b2:wǒmen c2:we
    a3:没有 b3:méiyǒu c3:not have, there is not, be without
    a4:家子 b4:jiāzi c4:household, family

    in this example I have 3 columns.
    when saving as a text file with unicode option the registers of column C are displayed with quotations because those cells have commas.
    if I eliminate the quotations my text file will show as it would have more columns because of the commas.

    I tried to solve this problem not taking the commas as a delimiter but tabulation, so when i save my text file with tab delimited option i have the registers in the 3 coulmns that i need but the chinese characters becomes ????.

    as far a i know, in my case saving as text with tab delimited option works better, but my big problem is that chinese characters become all in questions marks ????.

    it would be great to have an option to save with unicode and tab delimited option together.

    i will appreciate your help.
    thanks again,

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Save as Text with Unicode and Tab Delimited

    Then try
    Code:
    Sub test()
    Dim i As Long, txt As String
    With ActiveSheet.UsedRange
        For i = 1 To .Rows.Count
            txt = txt & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
        Next
    End With
     
    Open Replace(ThisWorkbook.Name, ".xls", ".txt") For Output As #1
        Print #1, Mid$(txt, Len(vbCrLf) + 1)
    Close #1
    End Sub

  5. #5
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Re: Save as Text with Unicode and Tab Delimited

    thank you jindon,

    the code runs but i can't see where my new text file is saved.

    i checked in my folder file and also in unit C:\ but i can't find the file created.
    may i know where is it saved?

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default Re: Save as Text with Unicode and Tab Delimited

    Hi

    It should have saved the file down in the same folder as the workbook is in (with a .txt extension instead of .xls). If you are not finding it here, then use this small modification of Jindon's code:

    Code:
    Sub test()
    Dim i As Long, txt As String
    With ActiveSheet.UsedRange
        For i = 1 To .Rows.Count
            txt = txt & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
        Next
    End With
     
    Open Replace(ThisWorkbook.FullName, ".xls", ".txt") For Output As #1
        Print #1, Mid$(txt, Len(vbCrLf) + 1)
    Close #1
    End Sub
    Richard Schollar

    Using xl2013

  7. #7
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Re: Save as Text with Unicode and Tab Delimited

    Richard thank you,

    yes, now i can find it.

    but the new txt file still shows me the chinese characters as questions marks besides some special characters become just as normal letters. like this:

    char pinyin description
    -------------------------------------------------
    ?? women we
    ?? méiyou not have, there is not, be without
    ?? jiazi household, family


    it should be like this:

    char pinyin description
    -------------------------------------------------
    我们 wǒ men we
    没有 méi yǒu not have, there is not, be without
    家子 jiā zi household, family



    how to solve that?

    appreciate your help,
    Last edited by Alejandro Rodriguez; Oct 13th, 2008 at 09:47 AM.

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default Re: Save as Text with Unicode and Tab Delimited

    I think you need to use the FileSystemObject if you want to manipulate text files in unicode (I'm not totally certain about this). I will have a dig around in one of my old posts that deals with this. It may take me a couple of hours to come back to you.
    Richard Schollar

    Using xl2013

  9. #9
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Re: Save as Text with Unicode and Tab Delimited

    i will wait, thank you

  10. #10
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default Re: Save as Text with Unicode and Tab Delimited

    See if this post helps - it shows how to save as a unicode file. It will probably need to be fleshed out a bit to ensure your data is passed to the unicode file as a string variable. i can help with that if required.

    How to create unicode HTM files using VBA?
    Richard Schollar

    Using xl2013

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com