Solved

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

Posted on 2010-11-22
1
1,041 Views
Last Modified: 2012-05-10
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
Comment
Question by:gkarydis
1 Comment
 
LVL 14

Accepted Solution

by:
canali earned 125 total points
ID: 34192850

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now