Split a text file into smaller files with Excel VBA

On this page I show how VBA can split a text file into smaller files with a user defined number of max lines/rows.

The macro exemplifies, how the user can select a file, how the text is read into an array with one line per row with the VBA function "Split()". It also shows how to write an array to a string with linefeeds and write a file to the disk.

You can highlight the code with the mouse, copy it by pressing CTRL+c and paste into a VBA module with CTRL+v. If your screen is small, some code lines may look broken, but they will be okay when pasted into a VBA module.

It started, because I wanted to analyse log files for my webpage with Excel. These log files can be very big, and I use Excel 2003 with room for "only" 65536 rows.

So I looked for a solution that could split the log file into smaller files. I found some answers, but couldn't get them to work. Maybe I was too impatient, but anyhow I wrote my own macro, which you can see below.

There are probably smarter ways, but this works for me and reasonably fast. The new files are saved in the same folder as the original file and get the same name + a number (name1.txt, name2.txt etc.).

Sub SplitTextFile()
'Splits a text or csv file into smaller files
'with a user defined number (max) of lines or
'rows. The new files get the original file
'name + a number (1, 2, 3 etc.).

Dim sFile As String  'Name of the original file
Dim sText As String  'The file text
Dim lStep As Long    'Max number of lines in the new files
Dim vX, vY           'Variant arrays. vX = input, vY = output
Dim iFile As Integer 'File number from Windows
Dim lCount As Long   'Counter
Dim lIncr As Long    'Number for file name
Dim lMax As Long     'Upper limit for loop
Dim lNb As Long      'Counter
Dim lSoFar As Long   'How far did we get?

On Error GoTo ErrorHandle

'Select a file
sFile = Application.GetOpenFilename()

'If the user cancelled
If sFile = "False" Then Exit Sub

'Ask the user for max number of lines per file. E.g. 65536
lStep = Application.InputBox("Max number of lines/rows?", Type:=1)

'Our arrays have zero as LBound, so we subtract 1
lStep = lStep - 1

'Read the file text to sText
sText = _
CreateObject("Scripting.FileSystemObject").OpenTextFile(sFile).ReadAll

'Put the text into the array vX. Linefeed chars (new
'line) will add a new row to the array.
'If linefeed doesn't work try:
''vX = Split(sText, vbCrLf)
'which is Carriage return–linefeed kombination
vX = Split(sText, vbLf)

'Free memory
sText = ""

'Now we start a loop that will run until all
'rows in the array have been read and saved
'into new files. The variable lSoFar keeps
'track of how far we are in vX.
Do While lSoFar < UBound(vX)
   'If the number of rows minus lSoFar is
   'bigger than max number of rows, the
   'array vY is dimensioned to max number
   'of rows.
   If UBound(vX) - lSoFar >= lStep Then
      ReDim vY(lStep)
      'lMax is set = last rownumber to be
      'copied to vY.
      lMax = lStep + lSoFar
   Else
      'Else we dimension vY to the number of
      'rows left.
      ReDim vY(UBound(vX) - lSoFar)
      'Last row to copy is last row in vX
      lMax = UBound(vX)
   End If
   
   lNb = 0
   'Now we copy the rows from vX to vY
   For lCount = lSoFar To lMax
      vY(lNb) = vX(lCount)
      lNb = lNb + 1
   Next
   
   'lSoFar keeps track of how far we got in vX
   lSoFar = lCount
   
   'Get a free file number
   iFile = FreeFile
   
   'Increment the number for the new file name
   lIncr = lIncr + 1
   
   'Save vY as a text file (.txt). It could also be a csv-file,
   'but then you need to replace txt with csv.
   Open sFile & "-" & lIncr & ".txt" For Output As #iFile
      'The Join function makes a text
      'string from the array elements.
      Print #iFile, Join$(vY, vbCrLf)
   Close #iFile
Loop

Erase vX
Erase vY

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure SplitTextFile"
End Sub

That was it. The original text file has now been split into smaller files with a user defined number of lines/rows. The last file may of course be shorter.