Create 1 text file per every 100 Rows Of Data

Question

Let's say:

1. I have 1,000 Rows of separate information in an Excel spreadsheet. (1 Column, 1,000 Rows)

2. I want to save each 100 rows as its own file name. (workbook1.txt, workbook2.txt, workbook3.txt, etc.)

Currently, I do this manually:

• I will highlight 100 Rows.
• I use the Control+C (Copy) then Control+N (New) to create a new file, then Control+V (Paste) then Control+S To Save the File, naming the file with the default name assigned when using Control+N, So it would be saved as workbook1, workbook2, workbook3...

I am saving them as text files (.txt).

Is is possible to automate this process, maybe a bat file or something?

Answer 1

Sometimes it is simply easier to copy everything into a new worksheet and remove what you do not wish to retain. A Worksheet.Copy method to no location will open a new, blank workbook with a single worksheet; that being a copy of the original.

Sub split_100()
    Dim i As Long, lr As Long, fn As String

    'Application.ScreenUpdating = false 'uncomment when it works right for you

    With Worksheets("Sheet1")
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To lr Step 100
            .Copy
            With ActiveWorkbook
                With .Worksheets(1)
                    If i > 1 Then _
                        .Cells(1, 1).Resize(i - 1, 1).EntireRow.Delete
                    .Cells(101, 1).Resize(lr, 1).EntireRow.Delete
                End With
                fn = Environ("TMP") & "\Workbook" & Format(Int(i / 100) + 1, "00") 'no extension; leave that for xlTextWindows
                'Application.DisplayAlerts = False  'uncomment to avoid overwrite warnings
                .SaveAs Filename:=fn, FileFormat:=xlTextWindows
                .Close SaveChanges:=False
            End With
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

After removing what is not wanted, a Workbook.SaveAs method brings the cycle to a Workbook.Close. There are a number of TXT formats to choose from, the above uses the xlTextWindows xlFileFormat.

Answer 2

You can use simple VBA script like:

Dim counter As Integer
counter = 0
Do While counter < 10
Range("A1:A100").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="C:\test" & CStr(counter) & ".txt", _
    FileFormat:=xlTextMSDOS, CreateBackup:=False
Sheets("Sheet1").Select
Range("A101:A1000").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
counter = counter + 1
Loop

Saving as xlTextMSDOS saves only active sheet so You do not have to create new workbook.

Answer 3

If you have access to a Linux system, there is a command line utility called "split" that does exactly this: split a text file in N file of N (lines, bytes, words).

cat original_file.csv | split -l 1000