Save/Export Worksheet as unicode txt file
Results 1 to 5 of 5

Thread: Save/Export Worksheet as unicode txt file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Save/Export Worksheet as unicode txt file

    I've got an excel file contains several worksheets and want to save one of the worksheet as a unicode txt file to facilitate the import to another system. I have to save the worksheet twice with same content but different file name to facilitate the users and want to return to another worksheet of the excel file after the saving. Can it be done by using excel macro? I am new to macro and only know how to modify a "recorded macro" to fit some needs only.

    Can anyone help me with this?

    Thanks very much!

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,775
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save/Export Worksheet as unicode txt file

    Hello huson,

    This VBA macro will create a Unicode CSV file from a worksheet. You will need to change the Filepath, Filename, and Worksheet name to what you will be using. The text is delimited by a comma. You change the separator to some other character if you want.
    Code:
    Sub SaveAsUnicode()
    
      Dim arr1() As Variant
      Dim Delimiter As String
      Dim Filename As String
      Dim Filepath As String
      Dim R As Long
      Dim Rng As Range
      Dim Text As String
      Dim TextFile As String
      Dim Wks As Worksheet
      
        Filepath = "C:\Documents and Settings\Admin.ADMINS\My Documents\"
        Filename = "Unicode Test"
        Delimiter = StrConv(",", vbUnicode)
        
        Set Wks = Worksheets("Sheet1")
        Set Rng = Wks.UsedRange
        
          ReDim arr1(1 To Rng.Rows.Count)
        
          TextFile = Filepath & Filename & ".txt"
          
          Open TextFile For Output As #1
            For R = 1 To Rng.Rows.Count
              arr1 = WorksheetFunction.Index(Rng.Rows(R).Cells.Value, 1, 0)
              Text = Join(arr1, Delimiter)
              Print #1, StrConv(Text, vbUnicode)
            Next R
          Close #1
          
    End Sub
    Adding the Macro

    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time.
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel.



    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
    Sincerely,
    Leith Ross

  3. #3
    New Member
    Join Date
    Sep 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save/Export Worksheet as unicode txt file

    Thanks very much! This is useful for me.

    If I need to save the txt file delimited by Tab, what should I input as the separator?

    If I want to use the value in a particular cell of the worksheet as part of the file name, what should I set in the macro?

    And if I want to ask the user to select the location to store the file, can it be set in the macro or it must be set up before writing the macro?

    And lastly, if I need to save the same worksheet twice with different file name, can I repeat the same code in the macro with different file name set?

    Thanks a lot!

  4. #4
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,775
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save/Export Worksheet as unicode txt file

    Hello huson,

    I was out till late yesterday evening and saw your reply this morning. I can easily modify the macro for you. I have a few questions for you before I make the changes.

    1. Can you give me an example of the new file name using the cell contents?
    2. Will all the new files be saved to the same directory?
    3. Are the new files Excel worksheets saved in CSV format?


    Sincerely,
    Leith Ross

  5. #5
    New Member
    Join Date
    Sep 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save/Export Worksheet as unicode txt file

    Sorry for a late reply coz busy this week. Thanks very much for your help.

    Regarding the questions,

    1. Part of the file name is related to a date value set in the cell and a text value of another cell. The cell position is fixed.
    2. The new files can be saved to the same directory.
    3. The new files are required to be saved in unicode *.txt file. The files are needed for importing data in another system.

    Thanks against for your help.

Some videos you may like

User Tag List

Tags for this Thread

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
  •