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 (http://www.vbaexpress.com/forum/member.php?51399-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 :)