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!
 

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.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,935
Messages
6,175,494
Members
452,649
Latest member
mr_bhavesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top