I recently came across a forum question in which the user needed a way to break a huge CSV file into multiple smaller files while retaining the header row in each of the smaller files. In this instance the question was revolving around JavaScript, but it got me thinking as to how it could be approached in VB/VBA/VBScript.

Well, below is a first run at one possible approach to this question.

VBA Function To Split A CSV File

Function CSV_Split(ByVal sSrcCSVFile As String, _
                 ByVal sOutputFolder As String, _
                 ByVal lSplitCSVMaxRowCount As Long, _
                 Optional ByVal sOpenDestFolder As Boolean = True) As Boolean
    Dim iFileNumber           As Integer
    Dim sLine                 As String
    Dim sHeader               As String
    Dim lLineCounter          As Long
    Dim iFileCounter          As Integer
    Dim sOutputFile           As String
    Dim iOutputFileNumber           As Integer

    If Right(sOutputFolder, 1) <> "\" Then sOutputFolder = sOutputFolder & "\" ' Ensure the proper formatting of the folder path
    iFileCounter = 1    ' Initialize the split file counter

    ' Open the source CSV for reading its content
    iFileNumber = FreeFile
    Open sSrcCSVFile For Input As #iFileNumber

    Line Input #iFileNumber, sHeader    'Grab the 1st line as the Header to be used in each file we create

    ' Loop through each line of the source CSV file
    Do While Not EOF(iFileNumber)
        ' If line count is zero, create a new output file and write the sHeader line to it
        If lLineCounter = 0 Then
            sOutputFile = sOutputFolder & iFileCounter & ".csv"
            iOutputFileNumber = FreeFile
            Open sOutputFile For Output As #iOutputFileNumber
            Print #iOutputFileNumber, sHeader
        End If

        Line Input #iFileNumber, sLine    ' Read a single line from the source file
        Print #iOutputFileNumber, sLine    ' Write the current line to the output file

        lLineCounter = lLineCounter + 1    ' Increment line counter

        ' If the current line count reaches the max allowable row count, 
        ' close the current output file and reset the line count and index the file counter
        If lLineCounter >= lSplitCSVMaxRowCount Then
            Close #iOutputFileNumber
            lLineCounter = 0
            iFileCounter = iFileCounter + 1
        End If
    Loop

    ' Close any open files
    If lLineCounter > 0 Then Close #iOutputFileNumber
    Close #iFileNumber

    CSV_Split = True 'If we made it here, everything worked and we should have the split files in the output folder
    If sOpenDestFolder Then Application.FollowHyperlink sOutputFolder 'Open the destination folder if requested
End Function

Then to use the function you simply do:

'Take the C:\Temp\testing.csv and break it into files containing a maximum of 15000 rows and save the resulting split CSVs in the C:\Temp\SplitCSV\ folder and don't open the folder once the operation is complete.
If CSV_Split("C:\Temp\testing.csv", "C:\Temp\SplitCSV\", 15000, False) Then
    'We're here because everything worked
End If

Per the usual, don’t forget to add proper error handling throughout.

Something To Consider

An alternative approach would be to read the csv into memory (into a vba variable) in one shot rather than line by line, split the variable into an array and process the array. I suspect this would actually be slightly faster.

Resources

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/open-statement
https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/line-inputstatement
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/printstatement
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/close-statement