Save/Export Worksheet as unicode txt file

[huson]

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!

 

[Leith Ross]

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.

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]

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]

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]

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.