Saving Row as Text file - Concatenate & Line Breaks Not Working

[dj44]

Hello folks,

I am needing some assistance from the pros.:grinhalo:

I am trying to adapt this excel macro.

found here. (http://www.mrexcel.com/forum/excel-questions/593439-convert-each-row-spreadsheet-into-txt-xml-file.html)





Sub ExportTextFiles()

'
Dim i As Long
Dim LastDataRow As Long
Dim MyFile As String
Dim fnum


LastDataRow = ActiveSheet.Range("A" & "B" & Rows.Count).End(xlUp).Row
For i = 1 To LastDataRow

MyFile = "C:\Users\DJ-PC\Documents\" & ActiveSheet.Range("C" & i).Value & ".txt"


fnum = FreeFile()
Open MyFile For Output As fnum
Print #fnum, Format(Range("A"&"B" & i))
Close fnum
Next i

End Sub





This is my excel file

15414


Now I tried all sorts of things to gear up this macro such as to concatenate the cells but that didn't work.:work:

Also the file is saved like text soup, the contents are all in a long line.

How can I have my names and addresses coming down vertically including the line breaks, so it looks civilized example

Bruce
Wayne Manor

Gotham City

Planet Earth

The Universe"
XCGHJJD

No:039844




do advise and many cheers for your help on this tricky task:thumb

and thank you very much for helping me

DJ

[jonh]

Sub ExportTextFiles()


Dim i As Long
Dim LastDataRow As Long
Dim MyFile As String
Dim fnum


LastDataRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastDataRow

MyFile = "C:\Users\DJ-PC\Documents\" & ActiveSheet.Range("C" & i).Value & ".txt"

s = NewLn(Cells(i, 3).Text) & vbNewLine
s = s & NewLn(Cells(i, 2).Text) & vbNewLine
s = s & NewLn(Cells(i, 1).Text) & vbNewLine

fnum = FreeFile
Open MyFile For Output As fnum
Print #fnum, s
Close fnum
Next i

End Sub


Private Function NewLn(s As String) As String
NewLn = Replace(s, vbLf, vbNewLine)
End Function

[dj44]

Hi Jonh,

Many thanks for helping on this macro :biggrin:

I can't believe it - this fiddly exasperating problem solved :checkmark

I wish I was as experienced in the VBA - I hope to get there


but in the mean time you have made me very happy

I salute you for your kind help.:thumb

Now I can export all those names and addresses - sweet as a nut

thank you dear fellow

DJ

solved