CODEWITHSUNDEEP
vbaexcelnotepad
stillLearning
stillLearning

Reputation: 84

Generate Excel file to .txt file and do some looping

I want to generate excel file to .txt file and do some looping. For example like this:

In Excel:

A | B | C | D
--+---+---+---
1 | 5 | 9 | 1
A | H | U | I

Will be generate to .txt like this:

"A", "1"
"B", "5"
"C", "9"
"D", "1"
"A", "A"
"B", "H"
"C", "U"
"D", "I"

Edited: This is my coding

Private Sub CommandButton1_Click()
Dim sFirst As String
Dim sSecond As String
Dim sThird As String
Dim sFourth As String
Dim sFName As String
Dim intFNumber As Integer
Dim lHeader As Long
Dim lLastRow As Long
Sheet1.Activate
Range("A1").Select
With Sheet1
    lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

sFName = ThisWorkbook.Path & "\Date" & Format(Now(), "yyyymmddhhmmss") & ".xls"

'Get an unused file number
intFNumber = FreeFile

'Create a new file (or overwrite an existing one)
Open sFName For Output As #intFNumber

For lHeader = 1 To lLastRow
    With Sheet1
        'For header
        sFirst = .Cells(lHeader, 1)
        sSecond = .Cells(lHeader, 2)
        sThird = .Cells(lHeader, 3)
        sFourth = .Cells(lHeader, 4)
    End With

    'Write selected data to text file
    Write #intFNumber, sFirst, sSecond , sThird, sFourth
Next lHeader
Close #intFNumber

MsgBox "Values from sheet '" & Sheet1.Name & "' were written to '" & sFName & "' file!", vbInformation

End Sub

But it only generate like this:

"A", "1"

"B", "5"

"C", "9"

"D", "1"

Upvotes: 0

Views: 280

Answers (2)

shA.t
shA.t

Reputation: 16968

Try this:

'Write selected data to text file
Write #intFNumber, "A", sFirst
Write #intFNumber, "B", sSecond
Write #intFNumber, "C", sThird
Write #intFNumber, "D", sFourth

instead of this:

Write #intFNumber, sFirst, sSecond , sThird, sFourth

Upvotes: 1

AnalystCave.com
AnalystCave.com

Reputation: 4974

Replace 

 Write #intFNumber, sFirst, sSecond , sThird, sFourth

with 

Print #intFNumber, sFirst , sSecond , sThird, sFourth & Chr(10)

To add new lines add & Chr(10) or & vbNewLine

Upvotes: 0

Related Questions