Need to create text files from excel rows. Column 1 should include the file names and column 2 the content of the text files. Each row will have either new file name or new content for that new text file. Also, the content of the text should be split into several lines. How to accomplish this? Thank you.
-
2So - what you have you tried so far?brettdj– brettdj2013-01-18 09:05:47 +00:00Commented Jan 18, 2013 at 9:05
Add a comment
|
1 Answer
Edited solution with text separation to lines.
For the sample the following chars are used:
;:,/|
Add new separators to RegEx pattern as required. Full code is below:
Sub Text2Files()
Dim FileStream As Object
Dim FileContent As String
Dim i As Long
Dim SavePath As String
Dim RegX_Split As Object
Set RegX_Split = CreateObject("VBScript.RegExp")
RegX_Split.Pattern = "[\;\:\,\\\/\|]" 'List of used line seperators as \X
RegX_Split.IgnoreCase = True
RegX_Split.Global = True
Set FileStream = CreateObject("ADODB.Stream")
SavePath = "D:\DOCUMENTS\" 'Set existing folder with trailing "\"
For i = 1 To ThisWorkbook.ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
FileContent = RegX_Split.Replace(ThisWorkbook.ActiveSheet.Cells(i, 2).Text, vbNewLine)
FileStream.Open
FileStream.Type = 2 'Text
FileStream.Charset = "UTF-8" 'Change encoding as required
FileStream.WriteText FileContent
FileStream.SaveToFile SavePath & ThisWorkbook.ActiveSheet.Cells(i, 1).Text, 2 'Will overwrite the existing file
FileStream.Close
Next i
End Sub
- Read more about ADO Stream Object: http://www.w3schools.com/ado/ado_ref_stream.asp
- RegEx for beginners: http://www.jose.it-berater.org/scripting/regexp/regular_expression_syntax.htm
Sample file with the above code is here: https://www.dropbox.com/s/kh9cq1gqmg07j20/Text2Files.xlsm
7 Comments
Henry The Least
About the line separator, i mean the Text Contents 1 may be split in the actual output file into several lines, e.g.:
Text Contents 1 How would I add the line breaks here in between the text? Thank you so much!
Peter L.
@HenryTheLeast which character is used as a new line separator? Space? Comma? or a list of chars?
Henry The Least
It can be any character. I am willing to punch in any character to separate the lines, except comma, period or space. Thanks.
Peter L.
@HenryTheLeast with your request RegEx expression is the only flexible solution))) But it will cost a bit - not an immediate solution for me. Will get back to that during the weekend. In case some more requests are coming - feel free to post here!
Peter L.
@HenryTheLeast I'm not sure whether you're notified about answer edits, but the updated solution is above.
|