Save as Text with Unicode and Tab Delimited

[Alejandro Rodriguez]

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.

 

[Alejandro Rodriguez]

thank you Richard,

i checked the post and it certainly seems what i want.

unicode text file with tab delimitation is what i need.

my original xls file has 5 columns,
and other xls files will have more than 5 columns
(so i hope the code would be flexible about this) .

your help is much appreciated
thank you

 

[Alejandro Rodriguez]

thank you Richard,

i checked the post and it certainly seems what i want.

unicode text file with tab delimitation is what i need.

my original xls file has 5 columns,
and other xls files will have more than 5 columns
(so i hope the code would be flexible about this) .

your help is much appreciated
thank you

how to make the code work properly for my case?

 

[Richard Schollar]

Hi

Try the following - you just need to run the Test() sub and it should save down the activesheet as a unicode file. You will need to change the location and filename to suit:

Sub test()
'set reference to Microsoft Scripting Runtime lib
Dim strFile As String, strText As String
Dim fso As FileSystemObject
Dim txtStrm As TextStream
strFile = "C:\Users\Richard\myFile.txt"  'amend as appropriate
strText = MakeText(ActiveSheet.UsedRange, Chr$(9))
Set fso = New FileSystemObject
Set txtStrm = fso.CreateTextFile(strFile, Overwrite:=True, Unicode:=True)
With txtStrm
    .Write (strText)
    .Close
End With
End Sub

Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = ",", Optional ByVal strNewLine As String = vbCrLf) As String
Dim varArray As Variant
Dim i As Long, j As Long
Dim strTemp As String

If rng.Count = 1 Then
    MakeText = rng.Value
    Exit Function
Else
    varArray = rng.Value
    For i = 1 To UBound(varArray, 1)
        For j = 1 To UBound(varArray, 2)
            strTemp = strTemp & varArray(i, j) & strDelim
        Next j
        strTemp = Left(strTemp, Len(strTemp) - 1) & strNewLine
    Next i
    strTemp = Left(strTemp, Len(strTemp) - 1)
    MakeText = strTemp
End If
End Function

 

[Alejandro Rodriguez]

Richard, thank you so much.

Your code does everything what I need.

Just 2 minor details when I was testing it.
I am working with Adobe Photoshop Variables, and the moment I try to vinculate the txt file is not accepted.
I found 2 reasons:

1) the txt file created has more empty tabs than my xls file which Photoshop takes as more columns.
My original xls file has 5 columns and the txt file created has 8 columns (three empty columns).
I realized that when another xls file has more than 8 columns the code doesn't have problems and creates the txt with the columns required but when I tested it with less than 8 columns it creates the new txt with eight columns (if xls has 3 columns, the txt has 3 columns with data and 5 empty columns).

2) The txt is saved as Unicode (which I requested) but for some reason Photoshop doesnt accept it.
I saved again the file as UTF-8 and this time was accepted it. So Photoshop only accepts me UTF-8 and UTF-16.

Could it be a way to solve this 2 problems?.

thank you so much in advance for your cooperation.


PS. could it be also a way the code shows a dialog box to point the path where I want to save the file?. if not possible it is ok.

 

[Richard Schollar]

The code as it currently stands using the UsedRange of the activesheet and I think it is this that is giving you problems with the additional columns - the amended version below will work on the range you have selected before you run the macro (so if you only want A1:C5 being written to the text file, select A1:C5 then run the macro). The modified code also lets you specify where to save the file. I'm not sure about the UTF-8 format issue - I will do a bit of investigation, but I may be unable to dtermine the cause.

Sub test()
'set reference to Microsoft Scripting Runtime lib
Dim strFile As String, strText As String, strPath As String
Dim fso As FileSystemObject
Dim txtStrm As TextStream
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    .Title = "Choose a folder to save file to"
    If .Show = -1 Then
        strPath = .SelectedItems(1)
    Else
        MsgBox "No folder selected! Exiting Sub", vbCritical + vbOKOnly, "Warning!"
    End If
End With
   
strFile = strPath & "\myFile.txt"  'amend as appropriate
strText = MakeText(Selection, Chr$(9))
Set fso = New FileSystemObject
Set txtStrm = fso.CreateTextFile(strFile, Overwrite:=True, Unicode:=True)
With txtStrm
    .Write (strText)
    .Close
End With
End Sub

Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = ",", Optional ByVal strNewLine As String = vbCrLf) As String
Dim varArray As Variant
Dim i As Long, j As Long
Dim strTemp As String

If rng.Count = 1 Then
    MakeText = rng.Value
    Exit Function
Else
    varArray = rng.Value
    For i = 1 To UBound(varArray, 1)
        For j = 1 To UBound(varArray, 2)
            strTemp = strTemp & varArray(i, j) & strDelim
        Next j
        strTemp = Left(strTemp, Len(strTemp) - 1) & strNewLine
    Next i
    strTemp = Left(strTemp, Len(strTemp) - 1)
    MakeText = strTemp
End If
End Function

 

[Alejandro Rodriguez]

Problem Solved

Richard, now the code works amazing!.

No problem about the UTF-8 issue. I am saving the file again with this format and can work with Photoshop.
Problem solved.

I deeply appreciate your assistance and time. Thank you and Have a good day!.

 

[uvsdl]

Hi,

i was looking for a code that would save excel file in to txt file that would correctly display some of the IPA characters (such as ã, ɑ, ɔ ). The above code does this for some characters but not for ɑ, ɔ, can someone help.

Thanks