Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1156
  • Last Modified:

Export with VB/Macro from excel, unicode characters to text files

Hi all,

I need to export the rows from the attached excel file in to multiple txt files. Each row will be exported to a different txt file with filename the last columns data.

I have written a VB code but it doesn't work with unicode data. I need to export Greek text in the file. Can anyone help by writing a new code or by tweaking mine?
Book1.xls

Sub TextFile()
Dim fs As Object, a As Object, i As Long, s As String, lngLastRow As Long
Set fs = CreateObject("Scripting.FileSystemObject")
lngLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = 2 To lngLastRow Step 1
  Set a = fs.CreateTextFile("C:\Temp\" & Range("G" & i).Value & ".txt", True)

  s = s & Cells(i, 1) & vbCrLf & Cells(i, 2) & vbCrLf & Cells(i, 3) & vbCrLf & Cells(i, 4) & vbCrLf & Cells(i, 5) & vbCrLf & Cells(i, 6) & vbCrLf & Cells(i, 7) & vbCrLf & Cells(i, 8) & vbCrLf & Cells(i, 9)


  a.WriteLine s
  s = ""
  a.Close
Next i
End Sub

Open in new window

0
gkarydis
Asked:
gkarydis
1 Solution
 
canaliCommented:

object.CreateTextFile(filename[, overwrite[, unicode]])
You need to tell that your file is unicode if the third param is omited the file is ASCII.

http://msdn.microsoft.com/en-us/library/5t9b5c0c(VS.85).aspx

Sub TextFile()
Const Unicode = -1
...
...
Set a = fs.CreateTextFile("C:\Temp\" & Range("G" & i).Value & ".txt", True, Unicode)
...
...
end sub
Sub TextFile()
Const Unicode = -1


Dim fs As Object, a As Object, i As Long, s As String, lngLastRow As Long
Set fs = CreateObject("Scripting.FileSystemObject")
lngLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = 2 To lngLastRow Step 1
  Set a = fs.CreateTextFile("C:\Temp\" & Range("G" & i).Value & ".txt", True, Unicode)

  s = s & Cells(i, 1) & vbCrLf & Cells(i, 2) & vbCrLf & Cells(i, 3) & vbCrLf & Cells(i, 4) & vbCrLf & Cells(i, 5) & vbCrLf & Cells(i, 6) & vbCrLf & Cells(i, 7) & vbCrLf & Cells(i, 8) & vbCrLf & Cells(i, 9)


  a.WriteLine s
  s = ""
  a.Close
Next i
End Sub

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now