Create text Files from every row in an Excel spreadsheet

Question

I need help creating separate text files from each row in an excel spread sheet called "worksheet". I want the text files to be named with content of Column A, with columns B-G being the content, preferably with a double hard return between each column in the text file, so each column will have a blank line in between them.

Is this possible? How would I go about it. thanks!

Answer 1

The attached VBA macro will do it, saving the txt files in C:Temp

Sub WriteTotxt()

Const forReading = 1, forAppending = 3, fsoForWriting = 2
Dim fs, objTextStream, sText As String
Dim lLastRow As Long, lRowLoop As Long, lLastCol As Long, lColLoop As Long

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

For lRowLoop = 1 To lLastRow

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set objTextStream = fs.opentextfile("c:emp" & Cells(lRowLoop, 1) & ".txt", fsoForWriting, True)

    sText = ""

    For lColLoop = 1 To 7
        sText = sText & Cells(lRowLoop, lColLoop) & Chr(10) & Chr(10)
    Next lColLoop

    objTextStream.writeline (Left(sText, Len(sText) - 1))


    objTextStream.Close
    Set objTextStream = Nothing
    Set fs = Nothing

Next lRowLoop

End Sub