PDA

View Full Version : Create different text files from parts of a range



shayxls
01-30-2016, 02:56 PM
Hi,
I need to create a module that export text files every time a value change from an excel range, example:

AAA 01/01/16 100.52 5001
AAA 01/01/16 536.13 8001
AAA 02/02/16 804.55 4561
BBB 05/01/16 400.11 7120
BBB 06/07/16 913.40 8124

the output needs to be 2 files:
1St file: AAA.txt
AAA 01/01/16 100.52 5001
AAA 01/01/16 536.13 8001
AAA 02/02/16 804.55 4561

2nd file: BBB.txt
BBB 05/01/16 400.11 7120
BBB 06/07/16 913.40 8124

i have try loops .. but still couldn't get it..

Thnx

snb
02-01-2016, 01:08 AM
What is the purpose of duplicating data ?

shayxls
02-01-2016, 02:35 AM
Hi, it isn't, The purpose is to take an excel range and split the data into text files whenever the change of value in the first column.
in the example the first column AAA repeats 3 time > 1st file, then 2 rows with BBB > 2nd file.

akuini
02-01-2016, 02:49 AM
Hi, shayxls
You may try this code:

Sub saveToTextFile()
Dim i As Long
Dim j As Long
Dim rr As Long
Dim sFile As String

rr = ActiveSheet.Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 1 To rr
j = i
If Range("A" & i) = Range("A" & i + 1) Then

Do
i = i + 1
Loop While Range("A" & i) = Range("A" & i + 1)

End If

Range("A" & j).Resize(i + 1 - j, 4).Copy

sFile = "D:\try\" & Range("A" & j)
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs sFile & ".txt", xlTextWindows
ActiveWorkbook.Close True

Next i
End Sub

shayxls
02-01-2016, 02:56 AM
works just fine ! Thnx alot, good day

Shay

Tom Jones
02-01-2016, 03:39 AM
Why you post same question in many forums?

Cross post Create different text files from parts of a range (http://www.mrexcel.com/forum/excel-questions/917821-create-different-text-files-parts-range.html)

shayxls
02-01-2016, 04:11 AM
I have got different answers and it was urgent,
hope it's not of a problem