VBA: extract systematiically 5 rows and save them in a new excell file as .txt

[SES]

Hi All,

My excel file looks like this:

chr	1	1	A
chr	2	2	A
chr	3	3	G
chr	4	4	A
chr	5	5	G
chr	6	6	A
chr	7	7	A
chr	8	8	G
chr	9	9	A
chr	10	10	G

I need to systematically extract 5 rows :

1- line 0->5
2-line 6-10
3-....
till the end of the file

and then save each 5 rows in a separate excel file in a .txt extension

May you help me solve this issue plz .

Tx in advance,

Best !

[sijpie]

Option Explicit


'-------------------------------------------
Sub MakeTextFiles()
'
' Macro to export first 4 columns 5 rows at _
  a time to text files
'-------------------------------------------
    Dim X As Long, Z As Long, FF As Long, TextOut As String
    Const OutputPath As String = "c:\temp\"   '<==Note the trailing backslash
    Const BaseFileName As String = "Rows_"    ' adjust base file name as you wish
    Const StartColumn As Long = 1   'Assumed Column A
    Dim StartRow As Long
  
    StartRow = 1     'Assumed Row 1
  
    Do While Cells(StartRow, StartColumn).Value <> vbNullString ' while not empty
            TextOut = ""
        For Z = StartRow To StartRow + 4    ' for 5 rows at a time
            For X = StartColumn To StartColumn + 3  ' for 4 columns
                ' add the text of each cell separated by a space
                TextOut = TextOut & Cells(Z, X).Value & " "
            Next
            TextOut = TextOut & vbCrLf  ' add new line
        Next
        ' Write this text string to a text file
        FF = FreeFile
        Open OutputPath & BaseFileName & StartRow & "-" & (Z - 1) & ".txt" For Output As #FF
        Print #FF, TextOut
        Close #FF
        ' increment start row
        StartRow = Z
    Loop
End Sub

this code is adjusted from code supplied by Rick Rothstein in thread
mrexcel.com/forum/excel-questions/646985-visual-basic-applications-export-range-text-files.html

[SES]

Many tx sijpie,

However the following script is not working, and that's why i did not get any result

Open OutputPath & BaseFileName & StartRow & "-" & (Z - 1) & ".txt" For Output As #FF

[sijpie]

Have you checked the path given higher up

Const OutputPath As String = "c:\temp\"

Does this directory exist? Else you need to change the path to suit your output.

[SES]

Thank you a million !!

I had forget to make a temp folder in C:\

it is working great