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.).
You can also use the macro for csv files - just replace "txt" with "csv".
Dim sFile As String
Dim sText As String
Dim lStep As Long
Dim vX, vY
Dim iFile As Integer
Dim lCount As Long
Dim lIncr As Long
Dim lMax As Long
Dim lNb As Long
Dim lSoFar As Long
On Error GoTo ErrorHandle
sFile = Application.GetOpenFilename()
If sFile = "False" Then Exit Sub
lStep = Application.InputBox("Max number of lines/rows?", Type:=1)
lStep = lStep - 1
sText = _
vX = Split(sText, vbLf)
sText = ""
Do While lSoFar < UBound(vX)
If UBound(vX) - lSoFar >= lStep Then
lMax = lStep + lSoFar
ReDim vY(UBound(vX) - lSoFar)
lMax = UBound(vX)
lNb = 0
For lCount = lSoFar To lMax
vY(lNb) = vX(lCount)
lNb = lNb + 1
lSoFar = lCount
iFile = FreeFile
lIncr = lIncr + 1
Open sFile & "-" & lIncr & ".txt" For Output As #iFile
Print #iFile, Join$(vY, vbCrLf)
MsgBox Err.Description & " Procedure SplitTextFile"
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.