Save/Export Worksheet as unicode txt file

huson

New Member
Joined
Sep 11, 2010
Messages
6
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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
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

  • Copy the macro above pressing the keys CTRL+C
  • Open your workbook
  • Press the keys ALT+F11 to open the Visual Basic Editor
  • Press the keys ALT+I to activate the Insert menu
  • Press M to insert a Standard Module
  • Paste the code by pressing the keys CTRL+V

    [*] Make any custom changes to the macro if needed at this time.
  • Save the Macro by pressing the keys CTRL+S
  • 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
 

huson

New Member
Joined
Sep 11, 2010
Messages
6
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!
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
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
 

huson

New Member
Joined
Sep 11, 2010
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,657
Messages
5,488,123
Members
407,625
Latest member
Alanacran

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top