CODEWITHSUNDEEP
excelexcel-2007excel-formulaexcel-2010vba
Henry The Least
Henry The Least

Reputation: 649

Create text files from excel

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.

Upvotes: 1

Views: 2192

Answers (1)

Peter L.
Peter L.

Reputation: 7304

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
  1. Read more about ADO Stream Object: http://www.w3schools.com/ado/ado_ref_stream.asp
  2. 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

Upvotes: 2

Related Questions