CODEWITHSUNDEEP
excelline-breaksvba
KEK79
KEK79

Reputation: 221

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

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

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.

Upvotes: 3

Views: 1524

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149305

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

Upvotes: 3

sp33dlink
sp33dlink

Reputation: 31

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.

Upvotes: 0

Related Questions