Convert to text using VB-macro (including line breaks within cells)

Question

I would like to convert an Excel sheet into several text files. The sheet contains two columns:

Column A gives the names for the generated text files
Column B provides content - contains line breaks within cells

The conversion works using the following code, but line breaks are not imported to the text file - all text is in one line:

Private Sub CommandButton1_Click()
    Dim oTxt As Object

    For Each cell In Ark1.Range("A1:A" & Ark1.UsedRange.Rows.Count)
        ' you can change the sheet1 to your own choice    
        saveText = cell.Text
        Open "H:\Webshop_Zpider\S-solutions" & saveText & ".txt" For Output As #1
        Print #1, cell.Offset(0, 1).Text
        Close #1
    Next cell
End Sub

Can anybody help me with that?

Hi, the result of using Siddharths suggestions: line breaks are included! But all generated files are not named and saved as I wanted. All files get the name S-solutions plus content of colum A (e.g. S-solutionsS-4001-K-T.txt) and are saved in folder H:\Webshop_Zpider. However, what I want is: Filename: content of colum A (e.g.S-4001-K-T.txt) and folder H:\Webshop_Zpider\S-solutions. How can I change that? Thanks.

score 3 · Answer 1 · answered Aug 07 '13 at 12:04

3

Replace this

Print #1, cell.Offset(0, 1).Text

with

Print #1, Replace(cell.Offset(0, 1).Value, vbLf, vbCrLf)

Also one Tip! Do not use .Text. Use .Value

answered Aug 07 '13 at 12:04

Siddharth Rout

147,039
17
206
250

Explaining the tip: See [This](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) – Siddharth Rout Aug 07 '13 at 12:16
One more thing: All files get the name S-solutions plus content of colum A and are saved in folder H:\Webshop_Zpider. However, what I want is: Filename: content of colum A and folder H:\Webshop_Zpider\S-solutions. How can I change that? Thanks for your help. – KEK79 Aug 08 '13 at 07:17
Please update your question with the result that you are getting and the result expected – Siddharth Rout Aug 08 '13 at 07:20
Hi, the result of using Siddharths suggestions: line breaks are included, but files are not not named and saved as I wanted. All files get the name S-solutions plus content of colum A (e.g. S-solutionsS-4001-K-T.txt) and are saved in folder H:\Webshop_Zpider. However, what I want is: Filename: content of colum A (e.g.S-4001-K-T.txt) and folder H:\Webshop_Zpider\S-solutions. How can I change that? – KEK79 Aug 08 '13 at 07:32
Try this `Open "H:\Webshop_Zpider\S-solutions\" & saveText & ".txt" For Output As #1` notice the "\" after "S-solutions" – Siddharth Rout Aug 08 '13 at 07:35
Thanks, Siddharth! Works again :-) – KEK79 Aug 08 '13 at 07:43

score 0 · Answer 2 · answered Aug 07 '13 at 12:03

0

First you should Open the Textfile only ONCE and not for every Cell Then you can do

Print #1, cell.Offset(0, 1).Text & vbnewline

For the celltext and a new line.

answered Aug 07 '13 at 12:03

sp33dlink

31
5

Convert to text using VB-macro (including line breaks within cells)

2 Answers2