Parse Functions‎ > ‎

### Rows To .TXT

PROBLEM:
"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."

SPECIFICATIONS:
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
EXAMPLE

 A B C D E 1 Dog Needs Walking 2 Cat Nip 3 Lucky Rabbit's Foot Keychain 4

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

#### CODE

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!