Changing a macro to save as Unicode text

[Dario de Judicibus]

I need some help. I am not an Excel macro programmer. I wished to use the
following macro I found in Internet to save my sheet as text without quotes.
However, I need to save it as Unicode text. How should the macro be changed?
Can you help me please? Please, consider that I am not able to program
Excel.

---
Public Sub TextNoModification()
Const DELIMITER As String = "CHR(9)" 'or "|", ",", etc.
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), Cells(.Row,
Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord

Close #1
End Sub

 

[Jim Rech]

You might try this:

Public Sub TextNoModification()
Dim Delimiter As String
Dim myRecord As Range
Dim myField As Range
Dim sOut As String
Delimiter = Chr(9)
Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), Cells(.Row,
Columns.Count).End(xlToLeft))
sOut = sOut & Delimiter & StrConv(myField.Text, vbUnicode)
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1
End Sub

I changed how you were doing the delimiter too since it made no sense as the
literal text "CHR(9)"

 

[Wei-Dong Xu [MSFT]]

Hi Dario,

The code from Jim can work very well.

I'd also suggest you can change this line to decide where the txt is saved
to.
'code begin
'specify the path to decide the location of output txt
'Open "c:\test.txt" For Output as #1
Open "Test.txt" For Output As #1
'code end

Please feel free to let me know if you have any further questions.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.