MACRO EXCEL - create multiple txt files from excel with a condition

[antonio masia]

hi i need to find a way to create multiple txt files from an excel, as shown in the picture. In detail i need to create a file txt with the name "sez-A1" and another "sez-A2" and so on. inside the txt i need to have the numbers of the 2 columns x and y from the excel

 

[Joe4]

Welcome to the Board!

There are different types of text files. What kind of text file are you saving it to?
It looks like it might either be a tab-delimited text file, or a fixed width text file.
If fixed-width, what are the widths of the two columns?

Also, will the header row for each section always be labelled with "X" and "Y" in columns B and C, respectively?

 

[Joe4]

If you are creating tab-delimited text files (which is my best guess), this VBA should do that, based on the structure of your examples:

Sub CreateTextFiles()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim fpath As String
    Dim fname As String
    Dim fr As Long, lr As Long
    Dim endOfFile As Boolean
    
    Application.ScreenUpdating = False
    
'   Set file path to save text files to
    fpath = "C:\Temp\Joe"
    If Right(fpath, 1) <> "\" Then fpath = fpath & "\"

'   Capture current worksheet with data
    Set ws1 = ActiveSheet

'   Create temp sheet to create text files from
    Sheets.Add.Name = "Temp"
'   Capture temp worksheet
    Set ws2 = ActiveSheet
    
'   Go back to data sheet
    ws1.Activate
    
'   Set first row of data to start on second row
    fr = 2
    
'   Loop through data
    Do
'       Exit loop if endOfFile is TRUE
        If endOfFile = True Then Exit Do
'       Look for next instance of "X" in column B
        ws1.Activate
        lr = Cells.Find(What:="X", After:=Cells(fr, "B"), LookIn:=xlFormulas2, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Row - 1
'       If no more "X"s found, set lr = last row of data and set endOfFile to TRUE
        If lr = 0 Then
            lr = Cells(Rows.Count, "B").End(xlUp).Row
            endOfFile = True
        End If
'       Build name of file
        fname = ws1.Cells(fr - 1, "A") & ".txt"
'       Copy data to temp sheet
        Range(Cells(fr, "B"), Cells(lr, "C")).Copy ws2.Range("A1")
'       Create tab-delimited text file
        ws2.Activate
        ActiveWorkbook.SaveAs Filename:=fpath & fname, FileFormat:=xlText, CreateBackup:=False
'       Clear data on temp sheet
        ws2.Range("A1").CurrentRegion.EntireRow.Delete
'       Reset next first row value
        fr = lr + 2
    Loop
    
'   Delete temp sheet
    Application.DisplayAlerts = False
    ws2.Delete

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
'   Close workbook
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

End Sub

Note that you will just need to update this line of code to specify where you want these text files created:

    fpath = "C:\Temp\Joe"

 

[antonio masia]

thanks for your help, tomorrow i will try it and will let you know