PDA

View Full Version : [SOLVED] Generate multiple TXTs files



Romulo Avila
04-11-2019, 01:31 PM
Good afternoon,
I need some help,
I have a file as attached and need a macro that I can generate txt files breaking every change in column "Company" B, and that each TXT is saved in the folder in use with the name of the break. Ex. 1.txt / 602.txt
Thank you for your attention.

Note: The attached file is just an example, the file I'm working with has over 100,000 lines


Thank you

RomuloRDM

p45cal
04-11-2019, 02:37 PM
try a variation of:
Sub blah()
Sheets("Planilha1").Copy After:=Sheets(1)
With ActiveSheet
Set FSO = CreateObject("scripting.filesystemobject")
Set myrng = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
With myrng
.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(2), Replace:=True, PageBreaks:=False, SummaryBelowData:=False
.AutoFilter Field:=1, Criteria1:="=*Count*"
.ClearContents
For Each are In .Columns(1).SpecialCells(xlCellTypeConstants, 23).Areas
FSO.createtextfile("C:\Users\Public\Documents\" & are.Cells(1).Offset(, 1).Value & ".txt").write Join(Application.Transpose(are.Value), vbCrLf)
Next are
End With
Application.DisplayAlerts = False: .Delete: Application.DisplayAlerts = True
End With
End Sub

Romulo Avila
04-11-2019, 03:51 PM
Good night,


P45cal, maybe I was not clear on my placement, I need the macro to generate each file with the data of each company, and that the files are in the format as attached image.

Note: In the example below would be the TXT file with company data 1


Thank you for your help
Hugs24052

p45cal
04-12-2019, 04:55 AM
P45cal, maybe I was not clear on my placementWell, I thought that it was clear enough to write code. However, your most recent message takes clarity to a whole new level, downwards.
You say:"Note: In the example below would be the TXT file with company data 1".
Does this mean that (a) the file should be named 1.txt instead of 34.txt? or
(b) that the file name is correct in your picture, but you have derived the name of the file from the first line of data somehow (which is not showing in your picture)?
Below is a picture of my results after running the macro blah in my earlier message:
Here, you can see your sheet on the left, the file names created on the right, and the file 1.txt opened in the middle.

I am at a complete loss as to what you are asking me to do to put it right.
24056

Romulo Avila
04-12-2019, 06:28 AM
P45Cal, Good morning, I need it to work according to the image that you have attached, that's what I need, except that running the macro you have previously posted "blah", it only generates a file according to the attached image.
** Only generated a file with the name 35.txt24058

p45cal
04-12-2019, 06:57 AM
Ahh. Try changing:
.AutoFilter Field:=1, Criteria1:="=*Count*"
to:
.AutoFilter Field:=1, Criteria1:="=*Conta*"

Romulo Avila
04-12-2019, 08:26 AM
P45cal,


Good afternoon, thank you, it worked correctly.


Hugs