Parse Functions‎ > ‎

Rows To .TXT

"I have a sheet varying columns of information.  Each row needs to become it's own text file. I'd like to set the delimiter on the fly each time, I might use a space or comma or a dash, something different each time."

  1. Macro uses a set path to save the files
  2. Macro creates a file for each row of data
  3. Macro prompts the user to enter the delimiter, defaults to " - " if none is entered
  4. Each text file is named for the string in the first cell


           Dog - Needs - Walking
   Cat.txt            Cat - Nip
   Lucky.txt        Lucky - Rabbit's - Foot = Keychain


Option Explicit

Sub RowsToTextFiles()
'Author:    Jerry Beaucaire
'Date:      7/30/2011
'Summary:   Write each row of values to a delimited text file
'           works with any number of rows or columns
Dim RW As Long, LastCol As Long, Col As Long
Dim MyPath As String, Delim As String, MyStr As String

MyPath = "C:\2011\Text\"    'remember the final \ in this path string
Delim = Application.InputBox("What delimiter to use?", "Delimiter", " - ", Type:=2)
If Delim = False Then Delim = " - "

    For RW = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Open MyPath & Range("A" & RW).Text & ".txt" For Append As #1
        For Col = 1 To Cells(RW, Columns.Count).End(xlToLeft).Column
            MyStr = MyStr & Delim & Cells(RW, Col)
        Next Col
        Print #1, Mid(MyStr, Len(Delim) + 1, Len(MyStr))
        Close #1
        MyStr = ""
    Next RW

End Sub

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!