Copy every two rows to text file

R

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
I have an excel file that has 400 rows of data. I need to copy every 2 rows of data into a pipe delimited text file. I also need to copy row 1 into every file. So, every file with have the first row from the excel file and the next two rows of data. There will be 200 files.

One more note: there is only data through column X, but need the pipes to go through columns AB

Location: c:\user\

Filenames: Loader1.txt, Loader2.txt, etc.








Thanks for your help
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hi..

Try this.. hey.. no doubt this could be done better.. but i think this does what you want..

Code: 
Private Sub CommandButton1_Click()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, Frow As String
    Dim LastRow As Long, r As Long, Num As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    C = 1: s = "": x = 0: Num = 1
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\user\Loader" & Num & ".txt", True)


    For r = 1 To LastRow
        If x = 3 Then
            Num = Num + 1
            Set a = fs.CreateTextFile("c:\Loader" & Num & ".txt", True)
            x = 0: r = r - 1
        End If


        While C <= 28
            If Cells(r, C).Value <> "" Then
                s = s & Cells(r, C) & "|"
                C = C + 1
            Else
                s = s & " " & "|"
                C = C + 1
            End If
        Wend


        If x = 0 And r = 1 Then
            Frow = s
        End If


        If x = 0 Then
            a.writeline Frow
        Else
            a.writeline s
        End If
        C = 1: s = "": x = x + 1
    Next r
End Sub

So.. if your sheet was like this for example...

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

You would get..

Loader1.txt
a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|
a2|b2|c2|d2|e2|f2|g2|a2|b2|c2|d2|e2|f2|g2|a2|b2|c2|d2|e2|f2|g2|a2|b2|c2|d2|e2|f2|g2|
a3|b3|c3|d3|e3|f3|g3|a3|b3|c3|d3|e3|f3|g3|a3|b3|c3|d3|e3|f3|g3|a3|b3|c3|d3|e3|f3|g3|

Loader2.txt
a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|
a4|b4|c4|d4|e4|f4|g4|a4|b4|c4|d4|e4|f4|g4|a4|b4|c4|d4|e4|f4|g4|a4|b4|c4|d4|e4|f4|g4|
a5|b5|c5|d5|e5|f5|g5|a5|b5|c5|d5|e5|f5|g5|a5|b5|c5|d5|e5|f5|g5|a5|b5|c5|d5|e5|f5|g5|

Loader3.txt
a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|
a6|b6|c6|d6|e6|f6|g6|a6|b6|c6|d6|e6|f6|g6|a6|b6|c6|d6|e6|f6|g6|a6|b6|c6|d6|e6|f6|g6|
a7|b7|c7|d7|e7|f7|g7|a7|b7|c7|d7|e7|f7|g7|a7|b7|c7|d7|e7|f7|g7|a7|b7|c7|d7|e7|f7|g7|

and so..

Is that what you want?
 
Upvote 0
Note:

You can remove the "i as Integer' and 't as String' parts.. remnants from another script i had..
 
Upvote 0
Thank you.

1. It only create one text file
2. Can you change the logic so the first row in each text file does not have the pipe. It is just data in cell A1. I could also just add the text to the VBA so it would add the text to each text file.
3. Can you remove the space between each pipe. I am not sure if my loader file will work with blanks?
4. I do have cells that are blank between each column so every cell is not populated, but column A is always populated.

Thank you for your help. This is very helpful.






apo said:
Hi..

Try this.. hey.. no doubt this could be done better.. but i think this does what you want..

Code: 
Private Sub CommandButton1_Click()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, Frow As String
    Dim LastRow As Long, r As Long, Num As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    C = 1: s = "": x = 0: Num = 1
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\user\Loader" & Num & ".txt", True)


    For r = 1 To LastRow
        If x = 3 Then
            Num = Num + 1
            Set a = fs.CreateTextFile("c:\Loader" & Num & ".txt", True)
            x = 0: r = r - 1
        End If


        While C <= 28
            If Cells(r, C).Value <> "" Then
                s = s & Cells(r, C) & "|"
                C = C + 1
            Else
                s = s & " " & "|"
                C = C + 1
            End If
        Wend


        If x = 0 And r = 1 Then
            Frow = s
        End If


        If x = 0 Then
            a.writeline Frow
        Else
            a.writeline s
        End If
        C = 1: s = "": x = x + 1
    Next r
End Sub

So.. if your sheet was like this for example...

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td><td >a1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td><td >f1</td><td >g1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td><td >a2</td><td >b2</td><td >c2</td><td >d2</td><td >e2</td><td >f2</td><td >g2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td><td >a3</td><td >b3</td><td >c3</td><td >d3</td><td >e3</td><td >f3</td><td >g3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td><td >a4</td><td >b4</td><td >c4</td><td >d4</td><td >e4</td><td >f4</td><td >g4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td><td >a5</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td><td >f5</td><td >g5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td><td >a6</td><td >b6</td><td >c6</td><td >d6</td><td >e6</td><td >f6</td><td >g6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td><td >a7</td><td >b7</td><td >c7</td><td >d7</td><td >e7</td><td >f7</td><td >g7</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

You would get..

Loader1.txt
a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|
a2|b2|c2|d2|e2|f2|g2|a2|b2|c2|d2|e2|f2|g2|a2|b2|c2|d2|e2|f2|g2|a2|b2|c2|d2|e2|f2|g2|
a3|b3|c3|d3|e3|f3|g3|a3|b3|c3|d3|e3|f3|g3|a3|b3|c3|d3|e3|f3|g3|a3|b3|c3|d3|e3|f3|g3|

Loader2.txt
a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|
a4|b4|c4|d4|e4|f4|g4|a4|b4|c4|d4|e4|f4|g4|a4|b4|c4|d4|e4|f4|g4|a4|b4|c4|d4|e4|f4|g4|
a5|b5|c5|d5|e5|f5|g5|a5|b5|c5|d5|e5|f5|g5|a5|b5|c5|d5|e5|f5|g5|a5|b5|c5|d5|e5|f5|g5|

Loader3.txt
a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|a1|b1|c1|d1|e1|f1|g1|
a6|b6|c6|d6|e6|f6|g6|a6|b6|c6|d6|e6|f6|g6|a6|b6|c6|d6|e6|f6|g6|a6|b6|c6|d6|e6|f6|g6|
a7|b7|c7|d7|e7|f7|g7|a7|b7|c7|d7|e7|f7|g7|a7|b7|c7|d7|e7|f7|g7|a7|b7|c7|d7|e7|f7|g7|

and so..

Is that what you want?
Click to expand...
 
Upvote 0
Hi..

Just change "Add you text here" to whatever you like in the 6th line..

It was only creating 1 txt file for you because i hadn't set the filepath to your desired path in both places ( I was testing with it outputting straight to C:\ )... fixed now..

See how this works for you...

Code: 
Private Sub CommandButton1_Click()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, Frow As String
    Dim LastRow As Long, r As Long, Num As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    C = 1: s = "": x = 0: Num = 1
    Frow = "Add your text here"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\user\Loader" & Num & ".txt", True)
    For r = 1 To LastRow
        If x = 3 Then
            Num = Num + 1
            Set a = fs.CreateTextFile("c:\user\Loader" & Num & ".txt", True)
            x = 0
            r = r - 1
        End If


        While C <= 28
            If Cells(r, C).Value <> "" Then
                s = s & Cells(r, C) & "|"
                C = C + 1
            Else
                s = s & "|"
                C = C + 1
            End If
        Wend
        If x = 0 Then
            a.writeline Frow
        Else
            a.writeline s
        End If
        C = 1: s = "": x = x + 1
    Next r
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Help with automation
Replies
2
Views
254
Anonymous1378
A
D
  • Solved
Export and name sheets within a workbook to a text file in same directory as workbook
Replies
3
Views
533
rlv01
R
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
566
ahaye
A
G
  • Question
Master excel that is updated by; and updates other excel files in folder.
Replies
1
Views
391
AFPathfinder
A
D
  • Question
Excel macro to copy data from same column of different files within a folder to a single Excel
Replies
6
Views
416
mumps
mumps

Forum statistics

Threads
1,215,134
Messages
6,123,237
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top