How to split large excel sheet into two separate text file (.txt)?

[JackkG]

Hi guys,

I have an excel file with first sheet having large quantity of data, say around 200,000 rows of data. I'm able to save the sheet data to a text file, but due to the amount of data i'm unable to upload this text file into server. Is there any VBA macro to split this sheet data into two text files with headers intact in both files? For example, first sheets with total of 200,000 records split up with 100,000 records in one text file and another 100,000 in another text file, headers will be same in both. Note: headers and rows may change overtime.

Any help with this will be great.

Thanks
Jackal

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...el-sheet-into-two-separate-text-file-txt.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[JackkG]

The same has been posted here:
https://www.excelforum.com/excel-pr...o-two-separate-text-file-txt.html#post5073326

 

[JackkG]

Hi guys,


I've found a code which does what i need, but it doesn't save it in a text file as required. It saves as .txtm file. Any help tweaking the SaveAs statement will be of great help. Thanks! Here is the code.

Public Sub Split()


    Dim inputFile As String, inputWb As Workbook
    Dim lastRow As Long, row As Long, n As Long
    Dim newCSV As Workbook
    Dim mainFolder As String, saveSubfolder As String
    
    inputFile = "C:\Desktop\Personal\DataTest-1.xlsm"
    
    mainFolder = "C:\Desktop\Personal\"
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"
    
    Set inputWb = Workbooks.Open(inputFile)
    
    Application.DisplayAlerts = False
    
    With inputWb.Worksheets(1)
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
        
        Set newCSV = Workbooks.Add
        
        n = 0
        For row = 2 To lastRow Step 55500
            n = n + 1
            .Rows(1).EntireRow.Copy newCSV.Worksheets(1).Range("A1")
            .Rows(row & ":" & row + 55500 - 1).EntireRow.Copy newCSV.Worksheets(1).Range("A2")
            
            saveSubfolder = mainFolder
            On Error Resume Next
            On Error GoTo 0
            newCSV.SaveAs FileName:=saveSubfolder & Replace(inputWb.Name, ".xls", "(" & n & ").txt"), FileFormat:=xlTextWindows, CreateBackup:=False
        Next
    End With
    
    newCSV.Close saveChanges:=False
Application.DisplayAlerts = True
End Sub

Same has been posted here:
https://www.excelforum.com/excel-pr...o-two-separate-text-file-txt.html#post5073326